Set operators in combined queries

The set operators UNION and UNION ALL can combine the result sets of two queries that specify the same number of columns in the Projection clause, and that have compatible data types in the corresponding columns of both queries.The set operators UNION, UNION ALL, INTERSECT, and MINUS can manipulate the result sets of two queries that specify the same number of columns in the Projection clause, and that have compatible data types in the corresponding columns of both queries.

  • The UNION operator returns the distinct results from both queries, excluding any duplicate rows.
  • The UNION ALL operator returns the all the qualifying rows from both queries, including any duplicate rows.

(The MINUS set operator has EXCEPT as its keyword synonym. Results that the MINUS and EXCEPT operators return from the same operands are always identical.)

These operators perform basic set operations of union, intersection, and difference on the result sets of two queries that are the left and right operands of the set operators:
  • The UNION set operator combines the qualifying rows from two queries into a single result set that consists of the distinct rows that either or both of the queries returned. (If you also include the ALL keyword, the UNION ALL result set can include duplicate rows.)
  • The INTERSECT set operator compares the result sets from two queries, but returns only the distinct rows that are in the result sets of both queries.
  • The MINUS set operator compares the result sets from two queries, but returns only the distinct rows in the result set of the left query that are absent from the result set of the right query.

The set operators are useful in business analytic contexts. They can also be used in SELECT statements to check the integrity of your database after you have performed a DML operation like UPDATE, INSERT, DELETE, or MERGE. The set operators can similarly be used when you transfer data to a history table, for example, when you need to verify that the correct data is in the history table before you delete rows from the original table.

All of the set operators have the same precedence. In complex queries that include more than one set operator, the precedence of operators is from left to right. Use parentheses to group set operators and their operands, if you need to override the default left-to-right precedence of set operators.

Only the UNION set operator supports the ALL keyword. The ALL keyword is not valid with the INTERSECT, MINUS, or EXCEPT set operators, from which only distinct rows are returned.

When comparing rows to calculate a set intersection or difference, two NULL values are considered equal in INTERSECT and MINUS operations.