SQL: UNION removes duplicates, UNION ALL does not

UNION sorts or hashes both sides to deduplicate, which costs real time. If you know the sides cannot overlap, UNION ALL is the same answer for less work.

Code

SELECT id FROM archived_orders
UNION
SELECT id FROM orders;          -- dedupes across both

SELECT id FROM archived_orders
UNION ALL
SELECT id FROM orders;          -- concatenates, no dedupe

Output

UNION     : HashAggregate  ->  482 ms
UNION ALL : Append         ->   31 ms

-- Both sides must have the same number of columns and compatible types.
-- Column names come from the FIRST branch.

Run this yourself in the Online Java Compiler, spin up a live REST API in the API Sandbox, or practise with Java interview questions.

Published 2026-08-25