UNION vs UNION ALL in SQL: What's the Difference?

UNION and UNION ALL both stack the results of two queries on top of each other. The difference is one word and it changes both your row count and your query’s cost: UNION removes duplicate rows; UNION ALL keeps every row exactly as returned.

The fastest way to feel it is to run both. The playground below combines two queries — customers in the USA and customers in the UK — and returns their cities. Run it as written, then hit the UNION ALL chip and watch the row count jump:

UNION returns 4 rows — New York, San Francisco, Chicago, London. UNION ALL returns 8, because three customers live in New York and three live in London, and those repeats are preserved.

The rules both operators share

Whichever you use, the branches must line up:

1. Same number of columns in every branch. Mismatch and the query fails — try it in the playground with SELECT id, name FROM customers UNION SELECT id FROM products and read the error.
2. Compatible data types, column by column, left to right — a real database rejects a text column unioned onto a date column. (The playground here is permissive and won’t stop you; the column-count rule is enforced.)
3. Column names come from the first branch. Alias there if you want a specific output name; aliases in later branches are ignored.
4. ORDER BY goes at the very end and sorts the combined result — it isn’t allowed on the individual branches in standard SQL (some databases permit it with parentheses, but it doesn’t mean what people expect).

SELECT city AS location FROM customers WHERE country = 'USA'
UNION
SELECT city FROM customers WHERE country = 'UK'
ORDER BY location;

Why UNION ALL is usually faster

UNION has to prove every output row is unique, which means the database sorts or hashes the whole combined result before returning it. UNION ALL just concatenates and streams. On large result sets that difference is substantial — and it’s pure waste if the branches can’t overlap in the first place.

So the practical rule: use UNION ALL by default, and reach for UNION only when you actually need duplicates collapsed. In pipeline work — stacking monthly partitions, appending yesterday’s rows to history, combining per-region extracts — the branches are disjoint by construction, so UNION would spend real time removing duplicates that cannot exist.

Two details worth knowing:

• Duplicate means the entire row matches, not just one column. Two rows differing in any column are both kept by UNION.
• The dedupe applies to the whole combined result, not just to rows that cross branches. UNION also collapses duplicates that occur entirely within one branch — it is defined as “concatenate, then remove duplicates.”
• NULLs count as equal for this purpose. Two rows that are NULL in the same column dedupe against each other, even though NULL = NULL is not true in a WHERE clause. Set operators and DISTINCT use “not distinct from” semantics; predicates use = semantics. Run SELECT segment FROM customers UNION SELECT segment FROM customers — the NULL appears once.

Neither guarantees an order

UNION ALL doesn’t promise the first branch’s rows come first, and UNION doesn’t promise sorted output just because it may sort internally. If order matters, say so with ORDER BY at the end. Relying on incidental ordering is one of those bugs that only shows up after the data grows or the plan changes.

The other set operators

UNION has siblings that compare two result sets instead of stacking them:

• INTERSECT — rows present in both branches.
• EXCEPT — rows in the first branch that are not in the second. (Oracle spells this MINUS.)

Both remove duplicates by default, like UNION.

Support is good but not universal: PostgreSQL, SQL Server, Oracle (as MINUS), and SQLite have had these for years; MySQL only added INTERSECT and EXCEPT in 8.0.31, so on older MySQL you emulate them with a join or a NOT EXISTS.

Practice

Frequently asked questions

What is the difference between UNION and UNION ALL? UNION combines the results of two queries and removes duplicate rows. UNION ALL combines them and keeps every row, including duplicates — which also makes it faster because nothing has to be deduplicated.

Which is faster, UNION or UNION ALL? UNION ALL, essentially always. UNION must sort or hash the combined result to detect duplicates; UNION ALL just concatenates.

Do the queries need the same columns? They need the same number of columns with compatible types, in the same order. The names come from the first query.

Does UNION treat NULLs as duplicates? Yes. For deduplication purposes two NULLs are considered the same value, so repeated NULL rows collapse to one.

Can I use ORDER BY with UNION? Put a single ORDER BY at the end; it sorts the whole combined result. Sorting the individual branches isn’t standard and doesn’t do what most people expect.

Stacking data is a pipeline job

Appending sources together — monthly files, per-region extracts, an old system plus a new one — is the bread and butter of data engineering, and UNION ALL is its SQL spelling. ET1 makes it a Concat / Union node on a canvas: wire two sources in, see the combined rows preview live, and keep the “do these branches overlap?” decision explicit instead of buried in a query.

Keep learning: filtering with WHERE, joins (combining sideways instead of stacking), and GROUP BY.