SQL EXCEPT

What is the SQL EXCEPT set operation?

The SQL EXCEPT set operation is another set operation like the UNION or INTERSECT operations. This operation, however, returns all rows from the first query except those rows that appears in the second query. Think of this as subtraction but for sets. Visually, the SQL EXCEPT set operation looks like:

SQL EXCEPT

Order matters with the EXCEPT set operation just like when subtracting numbers. Let’s look at an example. Suppose we have two queries: the ProductID and Name columns for all rows from Production.Product and another query that returns the ProductID and Name columns for all rows with a ProductID greater than 100. Applying the EXCEPT operation to the two queries, we get:

SELECT ProductID, Name
FROM Production.Product

EXCEPT

SELECT ProductID, Name
FROM Production.Product
WHERE ProductID > 100

The first query returns all rows from Production.Product. Then the EXCEPT operator tells SQL to exclude any row from the first query that appears in the second query; in this case, any row with a ProductID greater than 100. This is why the results are limited to just those few rows from the table with a ProductID less than or equal to 100.

Let’s look at another example. Simply switch the order of the queries from the previous example and look at the results:

SELECT ProductID, Name
FROM Production.Product
WHERE ProductID > 100

EXCEPT

SELECT ProductID, Name
FROM Production.Product

No rows are returned. This is the expected behavior for this query. Since the SQL EXCEPT operation excludes all rows present in the second query, an empty result field is the proper outcome. Since the first query only includes rows with a ProductID greater than 100, and the second query includes ALL rows from this table, then it follows that every row in the first query would appear in the second query. Since the EXCEPT operation excludes all rows that appear in the second query, we arrive at the result where we return no rows.

The SQL EXCEPT set operation can be a very useful operation. It can, at times, replace a lower performing query that may use the NOT IN OR NOT EXISTS operators in the WHERE clause.

Practice Problems

Using the examples in this lesson as a resource, try to complete the following practice problems.

  1. Use the EXCEPT set operation on the following two queries. The first query will be all columns in the HumanResources.Employee table for rows where the BusinessEntityID is less than 100. The second query will be all columns in the HumanResources.Employee table where the JobTitle equals “Chief Executive Officer”.
  2. Use the EXCEPT set operation on the following two queries. The first query will be all columns in the Production.Product table for rows with a ListPrice greater than $2,000. The second query will be all columns in the Production.Product table for rows with a Color value equal to “Red”.

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *