Set operations

Working with sets of rows

So far each query produced one result. Set operators combine the results of two SELECT statements as if they were mathematical sets. They are written between the two queries:

SELECT name FROM products
UNION
SELECT name FROM clearance;

Rules common to all of them

To combine two queries, both must have:

• the same number of columns, and
• compatible types in each position (they are matched by position, not by name).

The result's column names come from the first query.

UNION vs UNION ALL

• UNION combines the rows from both and removes duplicates. To do that it must compare and sort internally, so it has an extra cost.
• UNION ALL combines and keeps all rows, including duplicates. It is faster and the one you should use when you know there are no duplicates (or when you want to count them).

SELECT category FROM products
UNION ALL
SELECT category FROM clearance;

INTERSECT and EXCEPT

• INTERSECT returns the rows that appear in both queries (the intersection).
• EXCEPT returns the rows from the first query that are not in the second (set subtraction). Order matters: A EXCEPT B is not B EXCEPT A.

SELECT name FROM products
INTERSECT
SELECT name FROM clearance;   -- products that are also on clearance

Like UNION, the INTERSECT and EXCEPT operators also remove duplicates by default.

ORDER BY in combinations

An ORDER BY applies to the whole result of the combination, so it goes at the end, after the last query (not inside each SELECT):

SELECT name FROM products
UNION
SELECT name FROM clearance
ORDER BY name;        -- sorts the ENTIRE combined result

Examples

UNION removes duplicates; UNION ALL keeps them

SELECT category FROM products
UNION
SELECT category FROM clearance
ORDER BY category;