UNION and UNION ALL

Stacking result sets, and why UNION ALL is usually the right choice.

What you'll be able to do

Combine result sets vertically
Choose between UNION and UNION ALL
Use INTERSECT and EXCEPT

Stacking rows

Joins combine tables side by side; unions stack them on top of each other.

SELECT name, city FROM customers_us
UNION ALL
SELECT name, city FROM customers_eu;

Requirements: the same number of columns, in the same order, with compatible types. Column names come from the first query.

UNION versus UNION ALL

UNION removes duplicate rows. UNION ALL keeps everything.

Deduplication requires a sort or hash across the whole result, so UNION ALL is meaningfully faster. Use UNION ALL unless you specifically need duplicates removed — and if the sources cannot overlap, you never do.

ORDER BY with a union

A single ORDER BY applies to the combined result and goes at the very end:

SELECT name FROM a
UNION ALL
SELECT name FROM b
ORDER BY name;

INTERSECT and EXCEPT

SELECT id FROM a INTERSECT SELECT id FROM b;   -- in both
SELECT id FROM a EXCEPT    SELECT id FROM b;   -- in a, not in b

EXCEPT is MINUS in Oracle. Both are quick ways to diff two sets — useful when validating a migration.

Key points

UNION removes duplicates; UNION ALL keeps them and is faster
Column count, order and types must be compatible
ORDER BY applies once, at the end of the whole union

Check yourself