UNION, INTERSECT, and EXCEPT

A join combines tables sideways — more columns. A set operator combines them vertically — more rows.

SQL▶ Run
SELECT name AS label, 'customer' AS kind FROM customers
UNION ALL
SELECT name, 'product' FROM products
ORDER BY kind, label;

Both queries produce two columns, so their rows can stack.

The rules for stacking

1. Both queries need the same number of columns.
2. Corresponding columns need compatible types.
3. The first query's column names are used; names in the second are ignored.
4. ORDER BY goes at the very end and applies to the combined result.

Mismatched column counts are an error. Mismatched meanings are not — swap two columns of the same type in the second query and you get a result set with names in the country column and nobody notices.

UNION removes duplicates, UNION ALL does not

This is the distinction that matters, in correctness and in speed.

SQL▶ Run
SELECT COUNT(*) AS with_duplicates FROM (
  SELECT country FROM customers
  UNION ALL
  SELECT location FROM departments
);

SQL▶ Run
SELECT country AS place FROM customers
UNION
SELECT location FROM departments
ORDER BY place;

UNION ALL returns ten rows — everything from both sides. UNION deduplicates and returns six: the repeated countries collapse, and both NULLs collapse into one, because deduplication treats NULLs as the same value in exactly the way GROUP BY does and = does not.

Use UNION ALL unless you actually want deduplication. UNION has to sort or hash the entire combined result to find duplicates, which on large result sets is expensive — and if you know the two halves cannot overlap, you are paying for nothing. UNION ALL is the default choice; UNION is the deliberate one.

INTERSECT and EXCEPT

INTERSECT returns rows present in both; EXCEPT returns rows in the first that are not in the second (MINUS on Oracle — the same thing with a different name).

SQL▶ Run
SELECT location AS place FROM departments
EXCEPT
SELECT country FROM customers
ORDER BY place;

Both deduplicate by default, like UNION. PostgreSQL and SQL Server support INTERSECT ALL and EXCEPT ALL; support elsewhere is patchy. MySQL added INTERSECT and EXCEPT only in 8.0.31 — on older versions you need a join or a subquery instead.

EXCEPT is a neat way to ask "what is in A that is missing from B" without a join, and the two are worth comparing: EXCEPT compares whole rows and deduplicates, LEFT JOIN … IS NULL compares only the join key and preserves duplicates. For reconciling two extracts, EXCEPT in both directions is often the shortest correct answer.

Combining different tables into one report

The common real use: pulling similarly-shaped rows from several tables into one list.

SQL▶ Run
SELECT 'order'   AS source, order_id   AS id, amount FROM orders
UNION ALL
SELECT 'payment' AS source, payment_id AS id, amount FROM payments
ORDER BY source, id;

The literal 'order' / 'payment' column marks where each row came from — a constant in the SELECT list, which is a small trick worth having: it survives the union and tells you afterwards which half a row belongs to.

Set operators versus joins

They answer different questions and are not interchangeable:

Precedence: INTERSECT binds tighter than UNION and EXCEPT. When you mix them, bracket — for the same reason AND and OR need brackets.

Common mistakes

• UNION where UNION ALL was meant — silently drops legitimate duplicate rows, and costs a sort.
• Columns in a different order in the second query — types match, meanings do not, no error.
• ORDER BY in the middle — it belongs at the end, applying to the whole.
• Assuming INTERSECT/EXCEPT exist — not on MySQL before 8.0.31.

Interview question

What is the difference between UNION and UNION ALL, and which should you reach for?

UNION removes duplicate rows, UNION ALL keeps them. Prefer UNION ALL: deduplication requires sorting or hashing the whole result, so UNION is slower, and when the inputs cannot overlap it is pure cost. Use UNION only when duplicates are genuinely possible and genuinely unwanted.

Module complete

You can now combine tables sideways and stack them vertically, and you know the ways both go wrong quietly. Next: subqueries and CTEs — queries inside queries, which is how the fan-out fix from lesson 22 is actually written.