Performing Semi-Joins with EXCEPT and INTERSECT
A semi-join returns rows from the first input if there is as least one matching row in the second input. An anti-join returns rows from the first input if there are no matching rows in the second input. You use the EXCEPT and INTERSECT operators to perform semi-joins and anti-joins. These operators enable 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 is the result set.
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 is the result set.
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 is the result set.
Col1
-----------
NULL
1
2
3
(4 row(s) affected)