UNION

UNION stacks the rows of two SELECTs into one result and then removes duplicate rows by sorting or hash-aggregating the combined output — that dedup pass is the whole story, and it is exactly the work UNION ALL skips.

Both inputs must be union-compatible: the same number of columns, in the same left-to-right order, with compatible types per column. The engine pairs columns by position, not by name — the second SELECT's first column lines up with the first SELECT's first column regardless of what either is called. The result column names come from the first SELECT.

The two inputs

We have a Customers table and a Suppliers table. Both happen to have a city column, and we want every city either group touches.

Note Customers already contains London twice, and London + Madrid also appear in Suppliers — so duplicates arise both within one input and across the two.

UNION — deduplicated

SELECT city FROM Customers
UNION
SELECT city FROM Suppliers
ORDER BY city;

Trace it step by step:

1. Concatenate all rows from both SELECTs into one bag of 7 rows: Berlin, London, London, Madrid, London, Madrid, Paris.
2. Dedup. The engine sorts the bag (or builds a hash table) so it can detect equal rows. Sorted: Berlin, London, London, London, Madrid, Madrid, Paris.
3. Collapse adjacent equal rows to one each: Berlin, London, Madrid, Paris.
4. The trailing ORDER BY city applies to the final combined result, not to either SELECT.

Seven input rows became four. Every duplicate — the second London in Customers, and the London/Madrid shared across tables — was folded into a single row.

UNION ALL — keep everything

SELECT city FROM Customers
UNION ALL
SELECT city FROM Suppliers
ORDER BY city;

Same inputs, but no dedup step. The engine simply appends the second SELECT's rows after the first and returns all seven. With ORDER BY city for readability:

London appears three times (two from Customers, one from Suppliers); Madrid twice. The duplicates survive because nothing collapsed them.

The real engineering cost

The single most important practical fact: UNION pays for a sort or hash to find duplicates; UNION ALL does not. Look at the query plan and you will see the difference is one node — a Sort / HashAggregate (Postgres) or Sort / Hash Match (Aggregate) (SQL Server) sitting on top of an Append / Concatenation.

That dedup node is O(n log n) for the sort variant, or O(n) time and O(distinct rows) memory for the hash variant. On millions of rows it can spill to disk, dominate the query's runtime, and add latency for a result you may not even need deduplicated. If you know the inputs are disjoint — or you simply don't care about duplicates — UNION ALL is the correct default precisely because it removes that node entirely.

Rule of thumb: write UNION ALL first. Upgrade to UNION only when you have a concrete reason to dedup, and prefer deduping with a narrower mechanism (a WHERE filter, EXISTS, or a GROUP BY on just the key) when only part of the row needs to be unique.

Pitfalls

• Reaching for UNION when you meant UNION ALL. The default everyone copies is UNION, which silently adds a sort/hash on every run. On large result sets this is the most common avoidable performance bug in set queries.
• Mismatched column count. SELECT city FROM Customers UNION SELECT city, country FROM Suppliers fails outright — each UNION query must have the same number of columns. The engine cannot pair columns it can't line up.
• Columns pair by position, not name. SELECT name, city UNION SELECT city, name compiles but quietly mixes names into the city column and vice versa. Always list columns in the same order in every branch.
• Incompatible types. Pairing a numeric column against a date, or an INT against free-text, errors or forces an implicit cast you didn't intend. Cast explicitly so the intent is visible.
• ORDER BY belongs to the whole result, only once, at the end. Putting ORDER BY inside a branch is usually a syntax error or ignored; the final ORDER BY sorts the combined output.
• NULLs count as equal for dedup. Under UNION, two rows of all-NULL collapse to one — even though NULL = NULL is normally unknown. Set-operation duplicate matching treats NULLs as the same value, which can surprise you.

Takeaways

• UNION = concatenate then dedup (sort/hash); UNION ALL = concatenate only. The dedup pass is the entire difference, in both result and cost.
• Default to UNION ALL; choose UNION only when you specifically need distinct rows, because dedup is the expensive part.
• Inputs must be union-compatible: same column count, same order, compatible types — columns pair by position, result names come from the first SELECT.
• A single trailing ORDER BY sorts the combined output; under dedup, NULLs are treated as equal.

Sources: ISO/IEC 9075 (SQL standard, set operators); PostgreSQL documentation, "Combining Queries (UNION, INTERSECT, EXCEPT)" and EXPLAIN of Append vs HashAggregate; Microsoft SQL Server docs on the UNION operator and Concatenation vs Hash Match query-plan operators; the W3Schools Customers/Suppliers cities example. Re-authored and deepened for this guide — converted image-only result tables to real text tables, added the union-compatibility rule, a hand-authored mechanism diagram, the sort/hash dedup cost that makes UNION ALL the cheaper default, and engineer-facing pitfalls.

🎯 STANDOUT elevation: Why / example / when-not / failure / panel / drills — UNION

Why this exists / the decision it encodes

UNION stacks two union-compatible SELECTs then deduplicates (sort or hash). UNION ALL is the same stack without dedup. The entire engineering story is that dedup node: CPU, memory, possible disk spill. Default to UNION ALL unless distinctness is a business requirement.

Worked example with numbers or traced SQL/FD

Customers.city: Berlin, London, London, Madrid
Suppliers.city: London, Madrid, Paris
UNION: concat 7 → sort/hash → Berlin, London, Madrid, Paris (4)
UNION ALL: 7 rows keep London×3, Madrid×2
Plan: Append + HashAggregate/Sort for UNION; Append only for UNION ALL
Compatibility: same column count, position pairing (not by name), compatible types
NULL=NULL under set dedup: two all-NULL rows collapse to one

When NOT / named alternative

Write UNION ALL first; upgrade to UNION only when you need distinct rows. Prefer earlier DISTINCT on a narrow key or EXISTS filters when only part of the row must be unique. Do not UNION when you meant JOIN (different relationships).

Failure mode / ops fingerprint / interview trap

Trap: SELECT name, city UNION SELECT city, name — compiles, swaps meaning by position. Ops: nightly UNION of multi-million logs with accidental dedup OOMs. Interview: "UNION is free" — false.

Domain judgment (K11 theory-bridge / K12 concurrency / K13 query-judgment)

K13: set ops are plan nodes — choose ALL vs distinct with EXPLAIN in mind. K11: UNION is relational union; multiset vs set semantics matter.

Hostile-panel drills (with model answers)

Q1. What is the asymptotic cost difference?
Model answer: UNION ALL is linear append of inputs. UNION adds O(n log n) sort or O(n) hash with memory proportional to distinct rows, with spill risk.

Q2. Why do column names come from the first SELECT?
Model answer: Union-compatibility pairs by ordinal position; names are metadata from the first branch only.

Q3. When is UNION (dedup) correct and necessary?
Model answer: When the business result is a set of entities from multiple sources and duplicates would double-count (e.g. unique cities across customers and suppliers for a filter list).