UNION, INTERSECT, and EXCEPT Operators
By using the operators UNION
, INTERSECT
, and EXCEPT
, the output of more than one SELECT
statement can be combined to form a single result set. The <select_statement>
can’t have an ORDER BY
or LIMIT
clause. ORDER BY
and LIMIT
can be attached to a sub-expression if it’s enclosed in parentheses. Without parentheses, these clauses apply to the result of the UNION
.
The result of these operators, by default, doesn’t contain duplicates. To return duplicates in the result, specify the ALL
option.
Operators in the same SELECT
statement are evaluated from left to right. Intersect binds more tightly than UNION
or EXCEPT
. For example, A UNION B INTERSECT C
is read as A UNION (B INTERSECT C)
.
The UNION
operator computes the set union of the rows returned by the SELECT
statements. A row is in the set union if it’s in at least one of the result sets. Provide two SELECT
statements that produce the same number of columns, and have columns with compatible data types.
Syntax
The INTERSECT
operator computes the set intersection of the rows returned by the SELECT
statements. A row is in the intersection if it appears in both result sets. With the ALL
option, a row that has m
duplicates in the left table and n
duplicates in the right table appears min(m, n)
times in the result set.
Syntax
The EXCEPT
operator computes the set of rows that are in the result of the left SELECT
statement but not in the result of the right one. With ALL
, a row that has m
duplicates in the left table and n
duplicates in the right table appears max(m - n, 0)
times in the result set.
Syntax