Performing Semi-Joins with EXCEPT and INTERSECT
The EXCEPT and INTERSECT operators allow you to compare the results of two or more SELECT statements and return distinct values. The EXCEPT operator returns any distinct values from the query on the left side of the EXCEPT operator that are not also returned by the query on the right side. INTERSECT returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operator. Result sets that are compared using EXCEPT or INTERSECT must all have the same structure. They must have the same number of columns, and the corresponding result set columns must have compatible data types. For more information, see EXCEPT and INTERSECT (Transact-SQL).
Consider tables TableA, TableB, and TableC with the following data in their respective int columns col1.
| TableA (col1 int) | TableB (col1 int) | TableC (col1 int) |
|---|---|---|
NULL |
NULL |
2 |
NULL |
1 |
2 |
NULL |
3 |
2 |
1 |
4 |
4 |
2 |
4 |
4 |
2 |
||
2 |
||
3 |
||
4 |
||
4 |
Using EXCEPT, the following query returns any distinct values from the query to the left of the EXCEPT operand that are not also found on the right query.
SELECT * FROM TableA EXCEPT SELECT * FROM TableB
Here are the results:
Col1
-----------
2
(1 row(s) affected)
Using INTERSECT, this query returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operator.
SELECT * FROM TableA INTERSECT SELECT * FROM TableB
Here are the results:
Col1
-----------
NULL
1
3
4
(4 row(s) affected)
The INTERSECT operator takes precedence over EXCEPT. For example, the following query uses both operators:
SELECT * FROM TableA EXCEPT SELECT * FROM TableB INTERSECT SELECT * FROM TableC
Here are the results:
Col1
-----------
NULL
1
2
3
(4 row(s) affected)
See Also
Concepts
Using UNION, EXCEPT and INTERSECT with Other Transact-SQL Statements