Set operations

The idea

Most SQL compares columns. UNION, INTERSECT and EXCEPT compare whole rows between two queries that already have the same shape: same number of columns, in the same order, with compatible types.

You get four moves: stack the two streams and keep every copy (UNION ALL), stack them and collapse repeats (UNION), keep only what appears in both (INTERSECT), or take the second list away from the first (EXCEPT). Three of those four quietly deduplicate. Only UNION ALL leaves your rows alone.

Watch the junction

How it works

Both queries are evaluated, then the rows are combined positionally: the first column of the left query lines up with the first column of the right query, whatever they are called. The result takes its column names from the first query only. UNION, INTERSECT and EXCEPT then apply DISTINCT to the answer; adding ALL keeps every copy (widely supported for UNION ALL, and in PostgreSQL also for INTERSECT ALL / EXCEPT ALL).

-- warehouses.city : lisbon, oslo, porto, oslo   (4 rows)
-- stores.town     : oslo, porto, quito, reykjavik (4 rows)

SELECT city AS location FROM warehouses
UNION ALL                     -- stack, compare nothing        -> 8 rows
SELECT town FROM stores;

SELECT city AS location FROM warehouses
UNION                         -- stack, then DISTINCT          -> 5 rows
SELECT town FROM stores;

SELECT city AS location FROM warehouses
INTERSECT                     -- distinct rows in both         -> 2 rows
SELECT town FROM stores;

SELECT city AS location FROM warehouses
EXCEPT                        -- distinct left rows not in right -> 1 row (lisbon)
SELECT town FROM stores;

SELECT town FROM stores
EXCEPT                        -- the other direction           -> 2 rows (quito, reykjavik)
SELECT city FROM warehouses;

-- ORDER BY belongs to the whole statement, at the very end,
-- and can only see the FIRST query's column names.
SELECT city AS location FROM warehouses
UNION
SELECT town FROM stores
ORDER BY location;

A useful mental model: UNION ALL is concatenation, UNION is concatenation plus a hash table, and INTERSECT / EXCEPT build a hash of one side and then probe it with the other.

Cost

With m rows on the left and n on the right, and d distinct result rows.

Trade-off: deduplication is never free. It needs a sort or a hash table sized to the distinct rows, and it blocks streaming. If you already know the inputs cannot overlap (monthly partitions, disjoint regions), UNION ALL gives the same answer for less.

Watch out for

• Writing UNION when you meant UNION ALL. Two genuinely different refunds of £40 on the same day are one row after UNION. You lose data and pay for a dedup pass. Stacking partitions? Almost always UNION ALL.
• Column names come from the first query. SELECT town AS place on the second branch is ignored: the result column is still whatever the first branch called it. So ORDER BY place at the end fails; use the first query's name, or an ordinal like ORDER BY 1.
• Columns line up by position, not by name. SELECT id, city against SELECT city, id either errors on type mismatch or, if both columns are text, silently returns nonsense. Always list columns explicitly, never SELECT * on both sides.
• EXCEPT has a direction and it deduplicates. A EXCEPT B is not B EXCEPT A, and the count is not "rows of A minus matched rows": 400 rows in can become 12 out, because the answer is the distinct values of A that appear nowhere in B.
• NULL plays by different rules here. For set operations two NULLs count as the same value: they collapse under UNION and they match under INTERSECT/EXCEPT, unlike =. That makes EXCEPT a safe anti-join, where NOT IN (subquery containing NULL) returns nothing at all.

Worked example

You hold two extracts: warehouses(city) with lisbon, oslo, porto, oslo (two sites in oslo) and stores(town) with oslo, porto, quito, reykjavik. Ops asks: where do we have a store but no warehouse?

That is SELECT town FROM stores EXCEPT SELECT city FROM warehouses → quito, reykjavik. Put the queries the other way round and you have answered a different question, warehouse cities with no store, and get lisbon. Same two tables, same operator, different direction, different answer: that is the whole point of the swap control above.

Now a second ask: how many sites do we have per city? Reaching for UNION would quietly delete the second oslo warehouse. You want SELECT city AS location FROM warehouses UNION ALL SELECT town FROM stores wrapped in a GROUP BY location, which returns oslo = 3, porto = 2, and the rest 1: eight rows in, five groups out.

Check yourself