UNION vs UNION ALL: Key Differences & When to Use
UNION merges two SELECTs and silently removes duplicate rows; UNION ALL returns every row, duplicates included, exactly as they appear in each result set.
Developers often reach for UNION first because “no duplicates” feels safer, but the hidden sorting step that kills duplicates also eats CPU and memory—so the “safe” choice can throttle performance without anyone noticing.
Key Differences
UNION performs an implicit DISTINCT, adding an extra pass over the data; UNION ALL streams rows straight through. UNION may re-order output; UNION ALL keeps original sequence. In most engines, UNION ALL is markedly faster.
Which One Should You Choose?
Need uniqueness guaranteed by data model? Use UNION. Just concatenating logs, staging tables, or daily exports? Choose UNION ALL for speed. When in doubt, benchmark—UNION ALL is almost always the cheaper default.
Examples and Daily Life
Stacking yesterday’s and today’s order tables? UNION ALL keeps every sale row. Merging two mailing lists for a campaign? UNION removes duplicate e-mails so nobody gets spammed twice.
Does UNION ALL ever need ORDER BY?
Yes. Without ORDER BY the final sequence is undefined, even if each SELECT is already sorted.
Can UNION lose rows?
Only duplicates—so if two rows are truly identical, only one survives. Unique rows remain untouched.
Is UNION ALL always faster?
Almost always, unless the data is already deduplicated or indexes make the DISTINCT step trivial.