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