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:
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.