What Is the Difference Between UNION and UNION ALL?
Learn the difference between SQL UNION and UNION ALL, why UNION ALL performs faster, and when to choose each with clear query examples for interviews.
Expected Interview Answer
UNION combines the results of two queries and removes duplicate rows, while UNION ALL combines them and keeps every row including duplicates, which makes UNION ALL faster since it skips the de-duplication step.
Both operators require the combined queries to return the same number of columns with compatible data types. UNION performs an implicit sort/hash step to identify and remove duplicate rows across the combined result set, which adds CPU and memory overhead on large datasets. UNION ALL simply concatenates the row sets without checking for duplicates, so it is the better default whenever you already know the sources are disjoint or duplicates are acceptable (or even desirable, such as summing counts from multiple tables).
- UNION ALL avoids the expensive duplicate-removal sort/hash pass
- UNION guarantees a distinct combined result set
- Choosing the right one avoids silently dropping needed duplicate rows
- Both let you merge structurally similar queries from different tables
- Performance difference becomes significant at scale
AI Mentor Explanation
UNION is like merging two clubs' player registration lists into one roster and striking out anyone who appears on both lists, so each player is counted once. UNION ALL is like just stapling both lists together as-is, so a player registered at two clubs shows up twice in the combined stack.
Step-by-Step Explanation
Step 1
Match column count and types
Both queries in a UNION/UNION ALL must return the same number of columns with compatible types.
Step 2
UNION de-duplicates
The database performs a sort or hash pass over the combined rows to remove exact duplicates.
Step 3
UNION ALL just concatenates
Rows from both queries are appended directly with no comparison step.
Step 4
Consider performance
Prefer UNION ALL when you know results are disjoint or duplicates are acceptable, since it skips the de-dup cost.
Step 5
Order the final result
Apply ORDER BY once at the end of the combined query, not inside each individual SELECT.
What Interviewer Expects
- States UNION removes duplicates, UNION ALL keeps them
- Explains the performance cost of the de-duplication pass
- Knows column count/type must match across both queries
- Can give an example where UNION ALL is clearly preferable
- Mentions ORDER BY applies to the whole combined result
Common Mistakes
- Assuming UNION ALL is 'wrong' and always using UNION out of habit
- Forgetting that duplicates from UNION ALL can double-count aggregates
- Mismatching column types between the two SELECTs
- Putting ORDER BY inside each individual SELECT instead of at the end
Best Answer (HR Friendly)
“UNION combines two query results and removes duplicate rows, while UNION ALL combines them and keeps every row, which is faster because it skips the extra step of checking for repeats — like choosing whether to merge two guest lists into a unique list or just stack them together.”
Code Example
-- Removes duplicate rows
SELECT city FROM customers
UNION
SELECT city FROM suppliers;
-- Keeps every row, including duplicates, and is faster
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;Follow-up Questions
- Does UNION ALL preserve the original order of rows from each query?
- How would you use UNION ALL to safely count duplicates across sources?
- What happens if the two SELECT statements have mismatched column types?
- How does INTERSECT differ from UNION?
- When would you need DISTINCT instead of UNION on a single table?
MCQ Practice
1. What does UNION do that UNION ALL does not?
UNION performs an extra de-duplication step that UNION ALL skips entirely.
2. Why is UNION ALL generally faster than UNION?
UNION ALL concatenates rows directly without the sort/hash step needed to find duplicates.
3. What must be true of the SELECT statements combined with UNION?
Both queries must return matching column counts and compatible data types.
Flash Cards
UNION — Combines two result sets and removes duplicate rows.
UNION ALL — Combines two result sets and keeps all rows, including duplicates.
Which is faster? — UNION ALL, since it skips the duplicate-check pass.
Column requirement — Both queries must return the same number of columns with compatible types.