Curriculum
In this tutorial, you will learn how to use the SQL Server EXCEPT
 operator to subtract a result set of a query from another result set of another query.
EXCEPT
 operatorThe SQL Server EXCEPT
 compares the result sets of two queries and returns the distinct rows from the first query that are not output by the second query. In other words, the EXCEPT
 subtracts the result set of a query from another.
The following shows the syntax of the SQL Server EXCEPT
:
query_1 EXCEPT query_2
The following are the rules for combining the result sets of two queries in the above syntax:
The following picture shows the EXCEPT
 operation of the two result sets T1 and T2:
In this illustration:
The except
 of the T1 and T2 returns 1 which is the distinct row from the T1 result set that does not appear in the T2 result set.
EXCEPT
 operator exampleSee the following products
 and order_items
 tables from the sample database:
EXCEPT
 exampleThe following example uses the EXCEPT
 operator to find the products that have no sales:
SELECT product_id FROM production.products EXCEPT SELECT product_id FROM sales.order_items;
In this example, the first query returns all the products. The second query returns the products that have sales. Therefore, the result set includes only the products that have no sales.
EXCEPT
 with ORDER BY
 exampleTo sort the result set created by the EXCEPT
 operator, you add the ORDER BY clause in the last query. For example, the following example finds the products that had no sales and sorts the products by their id in ascending order:
SELECT product_id FROM production.products EXCEPT SELECT product_id FROM sales.order_items ORDER BY product_id;