UNION vs UNION ALL Performance in SQL

What each keyword actually does

SELECT customer_id FROM newsletter_signups
UNION
SELECT customer_id FROM loyalty_members;
-- Each customer_id appears once, even if present in both tables

SELECT customer_id FROM newsletter_signups
UNION ALL
SELECT customer_id FROM loyalty_members;
-- A customer_id in both tables appears twice

Why UNION is slower: the hidden dedup step

UNION is functionally equivalent to UNION ALL followed by a SELECT DISTINCT over the combined result. That dedup step is implemented one of two ways internally:

• Sort-based: every row in the combined result is sorted, which makes duplicates adjacent and therefore trivial to collapse — but sorting the whole result costs time proportional to n log n.
• Hash-based: a hash table is built of rows seen so far, and each new row is checked against it — cheaper than a full sort in many cases, but still work that UNION ALL skips entirely.

Either way, UNION has to examine every row in the combined result before it can return anything — UNION ALL can start streaming rows back immediately as each query produces them.

Seeing it in EXPLAIN

Running EXPLAIN on both versions of the same query makes the difference concrete — UNION's plan includes an extra step (commonly labeled HashAggregate, Sort, or Unique, depending on the database) that simply doesn't appear in the UNION ALL plan:

EXPLAIN
SELECT customer_id FROM newsletter_signups
UNION
SELECT customer_id FROM loyalty_members;
-- Plan includes an extra HashAggregate / Sort+Unique step

EXPLAIN
SELECT customer_id FROM newsletter_signups
UNION ALL
SELECT customer_id FROM loyalty_members;
-- Plan is just Append / Concatenate — no dedup step at all

For a deeper walkthrough of reading these plans, see EXPLAIN Query Plan – Beginner to Advanced.

When UNION is genuinely necessary

UNION is the right choice specifically when the two queries being combined can produce overlapping rows, and that overlap needs to collapse to one row in the final result:

Column requirements for both

This part is identical for both keywords and unrelated to the performance difference: each SELECT must return the same number of columns, and corresponding columns must be of compatible types.

SELECT customer_id, signup_date FROM newsletter_signups
UNION ALL
SELECT customer_id, join_date   FROM loyalty_members;
-- Column names in the final result come from the FIRST SELECT

Common mistakes

• Defaulting to UNION out of habit when the two queries can never actually overlap — paying for a dedup pass that finds nothing every single time.
• Using UNION ALL when duplicates genuinely need removing, silently double-counting rows in downstream aggregates like SUM() or COUNT().
• Assuming column names must match across both SELECTs. They don't — only the count and type compatibility matter; the result takes its column names from the first query.
• Not verifying the assumption with EXPLAIN before deciding UNION ALL is safe — "I don't think there are duplicates" is a guess, not a guarantee.