SQL UNION vs UNION ALL: Duplicate Rules and Performance — sqlfmt

sqlfmt▍

SQL UNION vs UNION ALL: Duplicate Rules and Performance — sqlfmt

Key takeaways

UNION removes duplicate output rows; UNION ALL keeps them.
Inputs need the same number of columns with compatible types.
Use UNION ALL unless deduplication is intentional.

What is the real difference?

Both operators combine two compatible result sets. UNION applies distinctness to the combined result; UNION ALL preserves all occurrences. Make that semantic choice deliberately.

One repeated customer row

SELECT customer_id FROM january_customers
UNION
SELECT customer_id FROM february_customers;

SELECT customer_id FROM january_customers
UNION ALL
SELECT customer_id FROM february_customers;

PostgreSQL states that UNION eliminates duplicates unless ALL is specified and requires union-compatible inputs.

What makes inputs compatible?

Corresponding SELECT positions need compatible types and every input needs the same output-column count. Alias the first SELECT when a stable final column name matters.

Where does ORDER BY apply?

An ORDER BY at the end orders the combined result. Parenthesize an input query when its own LIMIT or ordering must apply before the set operation.

Keep branches readable with this UNION formatting guide.

Frequently asked questions

Is UNION ALL always faster?

UNION requests duplicate removal and UNION ALL does not. Actual plans vary, but a query should not pay for deduplication when duplicate rows are meaningful.

Can I combine different data types?

Only when the database can resolve corresponding expressions to compatible types. Cast deliberately and make column position explicit.