Updated July 1, 2023
Introduction to SQL Except Select
SQL except select is useful while retrieving data from the query, and you have to mention that certain data should not be added in the final resultset. Manier times while fetching the data from the database, updating the records in the database, or deleting multiple records from the database, we have to mention that certain records from the retrieved resultset of the execution of the query should be excluded from the final resultset on which either of the operations will get performed i.e select, update or delete. In the query, after the SELECT statement, you can use the EXCEPT clause to specify either hardcoded values or another query statement that will determine the values to be excluded from the final result set.
In this article, we will study the general syntax of the except clause inside the select clause and also learn about its implementation using multiple examples. Further, we will learn about the difference in the working of the not in the clause and except. Microsoft SQL Server 2005 introduced except clause and its functionality.
Syntax of SQL Except Select
The syntax of except select clause in the select operation of the SQL query statement is as shown below –
SELECT column name 1 [, column name 2 ] FROM table name 1 [, table name 2 ] [WHERE condition or restriction]
EXCEPT
SELECT column name 1 [, column name 2 ] FROM table name 1 [, table name 2 ] [WHERE condition or restriction]In the above syntax, the first select statement that is on the right side is the main query that retrieves the resultset from the table named table name 1, and the retrieved values contain values of columns with names column name 1, column name 2, and so on. Now what the EXCEPT keyword does is that the rows that are retrieved from the second select query statement on the left side should be removed from the result set obtained after execution of the right side query if present. Note that both the queries on the left and right sides should retrieve the same number of comparative data-typed values from each other similar to union clause usage.
Examples of SQL Except Select
Let us first consider a simple example where we have one existing table named developers, which has the structure and contents of its table that are as shown in the output of the below query statement –
To exclude records with a salary less than 20000 from the result set of a query that retrieves name, position, and technology from the developer’s table, you can use the EXCEPT keyword in the following query statement:
SELECT
NAME,
POSITION,
technology
FROM
`developers`
EXCEPT
SELECT
NAME,
POSITION,
technology
FROM
`developers`
WHERE salary < 20000;The execution of the above query statement gives an output that is as shown below-
We can observe that all the developers having a salary greater than or equal to 2000 are included in the final result set, and the others with less than 20000 are excluded because those many records were retrieved from the second select statement after except. Now, let us consider one more example where we have two tables two existing tables named Articles and UpdatedArticles whose contents and structure are as shown in the output of the following query statement –
SELECT * FROM Articles;The execution of the above query statement gives an output that is as shown below –
SELECT * FROM UpdatedArticles;The execution of the above query statement gives an output that is as shown below –
Now, we have to retrieve the records from the updated articles table such that those records should not be present in the table named articles. Note that both the tables contain the same columns, and we have to retrieve the topic of the article and its rate. For this, we can make use of the EXCEPT clause and build our query statement as follows –
SELECT
`ArticleTopic`,
`Rate`
FROM
`UpdatedArticles`
EXCEPT
SELECT
`ArticleTopic`,
`Rate`
FROM
`Articles` ;The execution of the above query statement gives an output that is as shown below –
We can observe that only a single record is retrieved, a this is the only one that was present in updated articles but not in articles table records.
Difference between EXCEPT and NOT IN Clauses
We can see that the functionality of both clauses is the same. That is, they help specify certain resultsets that should not be included in the final resultset of the query. But there lie many differences in their working.
When using the EXCEPT clause in a query, it is necessary to specify all the column values that the query will retrieve in the query that specifies the records to be excluded. This means that the exclusion constraint applies to all the column values in the result set.
Recommended Articles
We hope that this EDUCBA information on “SQL Except Select” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
