How to except in sql server

The 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:
The following are the rules for combining the result sets of two queries in the above syntax:
  • The number and order of columns must be the same in both queries.
  • The data types of the corresponding columns must be the same or compatible.
The following picture shows the EXCEPT operation of the two result sets T1 and T2:
SQL Server EXCEPT illustration
In this illustration:
  • T1 result set includes 1, 2, and 3.
  • T2 result set includes 2, 3, and 4.
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.

SQL Server EXCEPT example

See the following products and order_items tables from the sample database:

A) Simple EXCEPT example

The following example uses the EXCEPT operator to find the products that have no sales:
SQL Server EXCEPT example
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.

B) EXCEPT with ORDER BY example

To 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:
SQL Server EXCEPT with ORDER BY example
In this tutorial, you have learned how to use the SQL Server EXCEPT to combine result sets of two queries.

Post a Comment