UNION vs UNION ALL: What is the Difference?
Learn the difference between SQL's UNION and UNION ALL, why UNION ALL is faster, and when to use each in interviews.
Expected Interview Answer
UNION combines the result sets of two or more SELECT queries and removes duplicate rows, while UNION ALL combines them and keeps every row, including duplicates, making UNION ALL faster because it skips the deduplication step.
Both operators require the combined queries to return the same number of columns with compatible types, and the final result takes its column names from the first SELECT. UNION performs an implicit sort-and-dedupe pass (often via a hash or sort operation) to guarantee distinct rows, which costs extra CPU and memory on large result sets. UNION ALL simply concatenates the row sets in whatever order the engine produces them, so it is the right default whenever you already know the inputs are disjoint or duplicates are acceptable.
- UNION guarantees a distinct combined result
- UNION ALL avoids the cost of deduplication
- Both stack results from structurally compatible queries
- Choosing correctly avoids silent performance regressions
AI Mentor Explanation
Imagine merging the squad lists from two touring sides into one combined roster for a charity match. UNION is like a selector who crosses out any player listed on both sides, leaving one clean list of distinct names. UNION ALL is like simply stapling both squad sheets together without checking for repeats, so a player named on both tours shows up twice. The stapled version is quicker to produce because nobody has to compare every name against every other name.
Step-by-Step Explanation
Step 1
Write compatible SELECT statements
Ensure both queries return the same number of columns with compatible data types in the same order.
Step 2
Choose UNION or UNION ALL
Use UNION to force a distinct combined result, or UNION ALL to keep every row including duplicates.
Step 3
Combine the queries
Place the set operator between the two SELECT statements to stack their result sets.
Step 4
Consider performance
Prefer UNION ALL when duplicates are impossible or acceptable, since it skips the extra sort/dedupe pass UNION performs.
What Interviewer Expects
- Clear statement that UNION deduplicates and UNION ALL does not
- Awareness that UNION ALL is faster due to skipping deduplication
- Mention of the matching-columns requirement for both operators
- A sense of when to prefer each in real query design
Common Mistakes
- Assuming UNION and UNION ALL always return the same rows
- Defaulting to UNION everywhere without considering the performance cost
- Forgetting that combined queries must have matching column counts and types
- Not knowing that column names come from the first SELECT statement
Best Answer (HR Friendly)
“UNION combines two query results and automatically removes duplicate rows, while UNION ALL combines them and keeps every row, duplicates included. Because UNION ALL skips the work of checking for duplicates, it runs faster, so I use it whenever I know the data will not overlap or duplicates are fine.”
Code Example
-- UNION removes duplicate rows across both result sets
SELECT customer_id, email FROM ActiveCustomers
UNION
SELECT customer_id, email FROM NewsletterSubscribers;
-- UNION ALL keeps every row, including duplicates, and is faster
SELECT customer_id, email FROM ActiveCustomers
UNION ALL
SELECT customer_id, email FROM NewsletterSubscribers;Follow-up Questions
- Why is UNION ALL generally faster than UNION?
- What requirements must the combined SELECT statements satisfy?
- How do you sort the final result of a UNION query?
- When would using UNION instead of UNION ALL introduce a subtle bug?
MCQ Practice
1. What does UNION do that UNION ALL does not?
UNION performs deduplication on the combined result set, while UNION ALL keeps every row as-is.
2. Why is UNION ALL typically faster than UNION?
UNION ALL simply concatenates result sets, avoiding the extra work UNION does to identify and remove duplicate rows.
3. What must be true of two SELECT statements combined with UNION?
Set operators require the combined queries to have matching column counts and compatible data types in the same order.
Flash Cards
What does UNION do? — Combines two result sets and removes duplicate rows.
What does UNION ALL do? — Combines two result sets and keeps every row, including duplicates.
Which is faster, UNION or UNION ALL? — UNION ALL, because it skips the deduplication step.
What must match between combined SELECTs? — The number of columns and their compatible data types.