UNION, INTERSECT, and EXCEPT
UNION, INTERSECT, and EXCEPT are set operators that combine the result sets of two queries into a single result set.
Syntax
query
{ UNION [ ALL ] | INTERSECT | EXCEPT }
queryOperators
| Operator | Behavior |
|---|---|
UNION | Returns all rows from both result sets. Duplicates removed. |
UNION ALL | Returns all rows from both result sets. Duplicates retained. |
INTERSECT | Returns only rows that appear in both result sets. |
EXCEPT | Returns rows that appear in the first result set but not the second. |
Calculation sequence
Default order of evaluation
UNION and EXCEPT are left-associative. Without parentheses, evaluation proceeds left to right.
The following statement first computes the union of t1 and t2, then returns rows in that union that do not appear in t3:
SELECT * FROM t1
UNION
SELECT * FROM t2
EXCEPT
SELECT * FROM t3
ORDER BY c1;INTERSECT takes precedence
INTERSECT has higher precedence than UNION and EXCEPT. When they appear together, INTERSECT is evaluated first, regardless of position.
The following statement first computes the intersection of t2 and t3, then returns the union of that result and t1:
SELECT * FROM t1
UNION
SELECT * FROM t2
INTERSECT
SELECT * FROM t3
ORDER BY c1;Override with parentheses
Use parentheses to explicitly control the order of evaluation.
The following statement first computes the union of t1 and t2, then returns the intersection of that result and t3:
(SELECT * FROM t1
UNION
SELECT * FROM t2)
INTERSECT
(SELECT * FROM t3)
ORDER BY c1;