Command SELECT - SQL UNION Clause Foundation

The UNION operator computes the union of the rows returned by two SQL SELECT statements. A row is contained in the union if it is contained in at least one of the result sets. The two SQL SELECT statements used as the operands of the UNION must produce the same number of columns which must also be of a compatible data type.

The result of the UNION operator does not contain duplicate rows unless the ALL option is specified. ALL prevents the elimination of duplicates. Therefore, using UNION ALL is usually significantly faster and should be used whenever possible.

Multiple UNION operators can be used in the same SQL SELECT statement. The operators are evaluated from left to right, unless a specific order is indicated by using parentheses.

The INTERSECT operator computes the intersection of the rows returned by two SQL SELECT statements. A row is contained in the intersection if it is contained in both result sets.

The result of INTERSECT does not contain duplicate rows unless the ALL option is specified. With ALL, 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.

Multiple INTERSECT operators can be used in the same SELECT statement. The operators are evaluated from left to right, unless a specific order is indicated using parentheses. INTERSECT has a higher precendence than UNION. That is, A UNION B INTERSECT C will be executed as A UNION (B INTERSECT C).

The EXCEPT operator computes the set of rows which are contained in the result of the left SQL SELECT statement but not in the result of the right one.

The result of EXCEPT does not contain duplicate rows unless the ALL option is specified. 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.

Multiple EXCEPT operators can be used in the same SQL SELECT statement. The operators are evaluated from left to right, unless a specific order is indicated using parentheses. EXCEPT has the same precedence as the UNION operator.

<SubSelectExpression> specifies an SQL SELECT statement whose result set defines the second operand for computing the union. The first operand is the SQL SELECT statement containing the UNION clause. Any type of SQL SELECT can be used with this parameter, including statements using joins and filters. However, the INTO and EVAL clauses can not be used in subselects.