UNION vs UNION ALL in SQL
Learn the difference between UNION and UNION ALL in SQL — how each handles duplicates, their performance trade-offs, and when to use each with clear examples.
Expected Interview Answer
UNION combines the result sets of two queries and removes duplicate rows, while UNION ALL combines them and keeps every row including duplicates.
Both operators stack the rows of two SELECT statements that have the same number and compatible types of columns. UNION performs an extra distinct/sort pass to eliminate duplicate rows, which costs time and memory, whereas UNION ALL simply concatenates the results with no deduplication. Because of that sort/hash step, UNION is slower; UNION ALL is the faster default when you know rows are already unique or duplicates are acceptable.
- UNION guarantees a distinct result set
- UNION ALL is faster with no dedup overhead
- Both merge results from multiple sources
- UNION ALL preserves true row counts
- Choice lets you trade correctness for speed intentionally
AI Mentor Explanation
Two selectors each hand you a list of players they scouted this season. UNION merges the lists but strikes out anyone who appears on both so each name is written once. UNION ALL simply staples the two lists together, so a player scouted by both selectors shows up twice in the combined squad sheet.
Step-by-Step Explanation
Step 1
Match the columns
Ensure both SELECT statements return the same number of columns with compatible data types in the same order.
Step 2
Pick the operator
Use UNION when you need distinct rows; use UNION ALL when duplicates are fine or impossible.
Step 3
Understand the cost
UNION adds a sort or hash step to remove duplicates; UNION ALL skips it and just concatenates.
Step 4
Order the final set
Apply a single ORDER BY after the last SELECT — it sorts the combined result, not each query separately.
Step 5
Prefer UNION ALL when safe
If you already know rows are unique, choose UNION ALL to avoid the unnecessary dedup overhead.
What Interviewer Expects
- Knows UNION removes duplicates and UNION ALL keeps them
- Explains the performance cost of the dedup step
- Mentions the column count and type compatibility rule
- Knows ORDER BY applies to the whole combined result
- Can pick the right operator for a scenario
Common Mistakes
- Thinking UNION ALL removes duplicates
- Assuming UNION is always the safer default without noting its cost
- Forgetting the two queries need matching column counts and types
- Adding ORDER BY to each SELECT instead of once at the end
- Using UNION on already-unique data and paying for needless sorting
Best Answer (HR Friendly)
“UNION glues two query results together and throws away any repeated rows, while UNION ALL keeps everything including repeats. UNION ALL is faster because it skips the duplicate-removal step, so you pick UNION only when you actually need a duplicate-free list.”
Code Example
-- Removes duplicates: a city in both tables appears once
SELECT city FROM customers
UNION
SELECT city FROM suppliers;
-- Keeps every row: a shared city appears twice
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;
-- One ORDER BY sorts the whole combined result
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers
ORDER BY city;Follow-up Questions
- Why is UNION generally slower than UNION ALL?
- How does the database remove duplicates in a UNION?
- Can you use ORDER BY inside each SELECT of a UNION?
- What happens if the two queries have different column counts?
- How would you combine three or more result sets?
MCQ Practice
1. Which operator removes duplicate rows from the combined result?
UNION performs a distinct pass and removes duplicate rows; UNION ALL keeps them.
2. Why is UNION ALL usually faster than UNION?
UNION ALL simply concatenates rows, avoiding the sort/hash step UNION needs to deduplicate.
3. A single ORDER BY at the end of a UNION statement sorts what?
ORDER BY placed after the final SELECT applies to the whole merged result, not the individual queries.
Flash Cards
What does UNION do? — Combines two query results and removes duplicate rows.
What does UNION ALL do? — Combines two query results and keeps all rows, including duplicates.
Which is faster and why? — UNION ALL — it skips the deduplication sort/hash step that UNION performs.
Column rule for UNION? — Both queries must return the same number of columns with compatible data types in the same order.