An INTERSECT operation retrieves the matching set of distinct values (not rows) from two columns. The INTERSECT ALL returns the set of matching individual rows. An INTERSECT and/or EXCEPT operation is done by matching ALL of the columns in the top and bottom result sets. In other words, these are rows, not the column, operations. It is not possible to only match on the keys, yet at the same time, also fetch non-key columns. To do this, one needs to use a sub-query. The UNION operation is a logical OR, INTERSECT is a logical AND. When multiple operations are done in the same SQL statement, there are precedence rules:
- Operations in parenthesis are done first.
- INTERSECT operations are done before either UNION or EXCEPT.
- Operations of equal worth are done from top to bottom.
SQL treats NULLS differently in these operations than it does when handing NULLS in joins and scans. SQL treats set operations NULL values as equal to each other; that is, the evaluation expression (NULL = NULL) produces the result TRUE whereas, in JOIN or SCAN, NULL equality expression evaluates to UNKNOWN
INTERSECT Syntax
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
INTERSECT/ INTERSECT ALL
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
Let’s consider below example
Table R1 Table R2
R1 R2
Abhi Abhi
Abhi Abhi
Abhi Baby
Baby Baby
Baby Baby
Cat Cat
Cat Dan
Cat
Elie
SELECT R1
FROM R1
INTERSECT/ INTERSECT ALL
SELECT R2
FROM R2
ORDER BY 1;
INTERSECT INTERSECT ALL
Abhi Abhi
Baby Abhi
Cat Baby
Baby
Cat
EXCEPT & EXCEPT ALL
An EXCEPT operation retrieves the set of distinct data values (not rows) that exist in the first table but not in the second. The EXCEPT ALL returns the set of individual rows that exist only in the first table.
NOTE: Only the EXCEPT operation is not commutative. Both the UNION and the INTERSECT operations work the same regardless of which table is on top or on the bottom.
Example:
Table R1 Table R2
R1 R2
Abhi Abhi
Abhi Abhi
Abhi Baby
Baby Baby
Baby Baby
Cat Cat
Cat Dan
Cat
Elie
SELECT R1
FROM R1
EXCEPT/ EXCEPT ALL
SELECT R2
FROM R2
ORDER BY 1;
EXCEPT EXCEPT ALL
Elie Abhi
Cat
Cat
Elie
WARNING: Unlike the UNION and INTERSECT operations, the EXCEPT statement is not commutative. This means that “A EXCEPT B” is not the same as “B EXCEPT A”. So if you use the EXCEPT operation in the reverse direction (i.e. R2 to R1 instead of R1 to R2) will give a different result:
SELECT R2
FROM R2
EXCEPT/ EXCEPT ALL
SELECT R1
FROM R1
ORDER BY 1;
EXCEPT EXCEPT ALL
Dan Baby
Dan