Oracle SQL - Select Set Operators


You can use the SQL set operators UNION, MINUS, and INTERSECT to combine the results of two independent query blocks into a single result.

The meanings of these set operators in SQL are listed in the following table.

Operator Result
Q1 UNION Q2 All rows occurring in Q1 or in Q2 (or in both)
Q1 UNION ALL Q2 As UNION, retaining duplicate rows
Q1 MINUS Q2 The rows from Q1, without the rows from Q2
Q1 INTERSECT Q2 The rows occurring in Q1 and in Q2

By default, all three set operators suppress duplicate rows in the query result.

UNION ALL operator does not eliminate duplicate rows.

The UNION, MINUS, and INTERSECT operators cannot be applied to any arbitrary set of two queries.

The separate results of queries Q1 and Q2 must be "compatible" in order to use them as arguments to a set operator.

Here the compatibility means the following:

  • Q1 and Q2 must select the same number of column expressions.
  • The data types of those column expressions must match.

The result table inherits the column names or aliases from Q1.

Q1 cannot contain an ORDER BY clause.

If you specify an ORDER BY clause at the end of the Q2 query, it doesn't refer to Q2, but to the total result of the set operator.

Related Topics