UNION ALL does exactly what UNION does, stacks the results of two or more SELECT statements, with one difference: it keeps every row, including duplicates. That one difference makes it both faster and, often, the more correct choice.
Learning Objectives
- Use UNION ALL and predict how it differs from UNION on the same two queries.
- Explain why UNION ALL is faster than UNION.
- Decide which one is actually correct for a given question.
Query 1 Result
5 rows
UNION ALL
No deduplication step
Query 2 Result
3 rows
8 Rows
Every row kept, duplicates included
UNION ALL Keeps Every Row
An employee in Engineering who also earns over 90000 appears twice here, once from each SELECT, where the UNION version from the previous lesson would have shown them only once. Same column-count-and-type rule as UNION, the only behavior that changes is whether duplicates get removed.
SELECT first_name, department_id FROM employees WHERE department_id = 1 UNION ALL SELECT first_name, department_id FROM employees WHERE salary > 90000;
Why UNION ALL Is Faster
UNION has to compare every row against every other row to find and remove duplicates, real extra work, especially on a large result set. UNION ALL skips that step entirely, it just concatenates both result sets as-is. On a genuinely large query, that difference is often significant, not just theoretical.
When Each One Is Actually Correct
The two mutually-exclusive-condition department_id = 1 and department_id = 2 example from the previous lesson's Try It Yourself is a perfect case for UNION ALL instead, a row can never satisfy both WHERE conditions at once, so there's nothing for UNION's deduplication to ever actually remove, making it pure overhead.
| Situation | Use |
|---|---|
| The two queries can't produce overlapping rows anyway (e.g. mutually exclusive WHERE conditions) | UNION ALL, duplicates are impossible, so paying for deduplication is pure waste |
| Overlap is possible and a duplicate genuinely shouldn't be counted twice | UNION |
| Overlap is possible and a duplicate should be counted each time it occurs (e.g. combining two logs or event lists) | UNION ALL, here the "duplicate" rows are meaningfully separate events |
Try It Yourself
Rewrite the mutually-exclusive department_id = 1 / department_id = 2 query from the UNION lesson using UNION ALL instead, since duplicates are impossible either way.
SELECT first_name FROM employees WHERE department_id = 1 UNION ALL SELECT first_name FROM employees WHERE department_id = 2;
Frequently Asked Questions
Does UNION ALL preserve any particular row order?
Not guaranteed on its own, same as any query without ORDER BY. Add one ORDER BY clause at the very end of the whole UNION ALL statement if a specific order across both result sets matters.
Is UNION ALL always safe to use instead of UNION?
Only when duplicates either can't occur or genuinely should be kept. Swapping UNION for UNION ALL purely for speed, without checking which case applies, can silently change what the query actually returns.
Common Beginner Mistakes
Defaulting to UNION everywhere out of habit, even when duplicates are impossible
This pays for a deduplication pass that can never find anything to remove, pure wasted work. If the two queries' conditions are mutually exclusive, UNION ALL is strictly the better choice.
Using UNION ALL when a duplicate genuinely needs to be counted once
If an employee matching both of two overlapping conditions should only be reported once (a headcount, say), UNION ALL would double-count them. That's exactly the case UNION's deduplication exists for.
Best Practices
- Default to UNION ALL when the two queries' conditions can't overlap, it's strictly faster with no behavior difference.
- Reach for UNION specifically when overlapping duplicates are possible and need to be collapsed to one.
- Add a single ORDER BY after the final SELECT when the combined result needs a specific order, not inside each individual SELECT.
Interview Questions
What's the difference between UNION and UNION ALL?
UNION combines two or more SELECT results and removes exact duplicate rows; UNION ALL combines them and keeps every row, duplicates included. UNION ALL is faster because it skips the deduplication comparison entirely.
When would UNION ALL give a wrong answer where UNION would give a correct one?
When the two queries can return overlapping rows that represent the same real-world thing and shouldn't be double-counted, for example two overlapping WHERE conditions both matching the same employee. UNION ALL would list that employee twice; UNION would correctly list them once.
Summary
UNION ALL combines SELECT results exactly like UNION but skips deduplication, making it faster and the correct choice whenever duplicates are impossible or should genuinely be kept. UNION remains correct whenever an overlapping duplicate needs to collapse to a single row.
What's Next?
GROUP BY next, picking back up the aggregate functions from Module 3, now computing a separate total, count, or average per group instead of one number for the whole table.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.