UNION vs UNION ALL
The deduplication and performance difference, and why UNION ALL is usually intended.
UNION vs UNION ALL is a free SQL Interview Prep lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
Why Interviewers Ask About UNION
Set operations stack one result set on top of another, vertically. UNION and UNION ALL are the first set operators interviewers reach for because the difference between them is a one-line answer that reveals whether you understand cost.
The question is almost always phrased as: "What is the difference between UNION and UNION ALL, and which should you use?" A strong answer mentions deduplication, ordering, and performance in one breath.
What UNION Does
UNION combines the rows of two queries into a single result set and then removes duplicate rows. Two rows are duplicates only if every column matches.
To remove duplicates the engine must sort or hash all combined rows, which is real work. UNION returns a set in the mathematical sense: no repeats.
SELECT city FROM customers
UNION
SELECT city FROM suppliers;What UNION ALL Does
UNION ALL concatenates the two result sets and keeps every row, including duplicates. It does no deduplication, so it does no sorting or hashing for that purpose.
Because it skips the dedup step, UNION ALL is almost always faster and is the correct choice when you know rows cannot overlap or when duplicates are actually wanted.
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;The Performance Difference
The interview headline: UNION ALL is cheaper because it never deduplicates. UNION must compare every combined row against every other to drop repeats.
UNION= UNION ALL + an implicit DISTINCT step.- On large result sets that DISTINCT can dominate the query cost.
If you do not need duplicate removal, reaching for UNION wastes CPU and memory.
A Worked Example With Duplicates
Suppose both queries can return the row 'Paris'. With UNION you get a single Paris. With UNION ALL you get Paris twice.
Interviewers love asking you to predict the row count. Always picture the raw concatenation first, then ask: does the operator strip repeats?
-- customers.city: Paris, Lyon
-- suppliers.city: Paris, Nice
-- UNION -> Paris, Lyon, Nice (3 rows)
-- UNION ALL -> Paris, Lyon, Paris, Nice (4 rows)Duplicates Are Defined Across All Columns
A common trap: two rows are duplicates only if every selected column is equal. Add one differing column and the rows are no longer duplicates, so UNION will keep both.
This is why SELECT id, city often returns more rows than SELECT city under UNION, even on the same tables.
SELECT id, city FROM customers
UNION
SELECT id, city FROM suppliers;
-- ids differ -> few or no duplicates removedOrdering the Combined Result
You cannot put ORDER BY on the individual branches; it applies to the whole combined result and must appear once at the very end.
An interviewer may ask where ORDER BY goes. The answer: a single ORDER BY after the last query, referring to the output columns by name or by position.
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers
ORDER BY city;Column Names Come From the First Query
When you reference columns in the final ORDER BY, use the names (or aliases) from the first SELECT. The second query's column names are ignored for output labeling.
This matters when the branches name their columns differently. Alias the first branch to control the output header.
SELECT city AS location FROM customers
UNION ALL
SELECT town FROM suppliers
ORDER BY location;When UNION Is the Right Choice
Use UNION only when overlapping rows are genuinely possible and you want each distinct row once. Examples: merging two contact lists where the same person may appear in both, or building a deduplicated list of distinct values from several sources.
If you can guarantee disjoint sources, UNION ALL gives the same result faster.
SELECT email FROM web_signups
UNION
SELECT email FROM store_signups;
-- one row per distinct email across bothWhen UNION ALL Is the Right Choice
Use UNION ALL when duplicates cannot occur, when duplicates are meaningful, or when you will aggregate afterward. A classic pattern is stacking monthly partition tables that share no rows.
Tip for interviews: state that UNION ALL is the default and you only escalate to UNION when dedup is actually required.
SELECT * FROM sales_2023
UNION ALL
SELECT * FROM sales_2024;Putting It Together
A polished interview answer sounds like: "UNION removes duplicate rows, which forces a sort or hash; UNION ALL keeps everything and is faster. Both require matching column counts and compatible types, and any ORDER BY goes once at the end. I default to UNION ALL unless deduplication is required."
That single sentence shows correctness and performance awareness together.
Quick Check
Test your grasp of the UNION versus UNION ALL distinction.
Recap
Key takeaways:
UNION= combine + remove duplicate rows (implicit DISTINCT, extra cost).UNION ALL= combine and keep everything, faster, the sensible default.- Duplicates require all columns to match.
- A single
ORDER BYgoes at the very end and uses the first query's column names.
Default to UNION ALL; escalate to UNION only when deduplication is truly needed.
Frequently asked questions
Is the “UNION vs UNION ALL” lesson free?
Yes — the full text of “UNION vs UNION ALL” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.
What will I learn in “UNION vs UNION ALL”?
The deduplication and performance difference, and why UNION ALL is usually intended. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Interview Prep?
No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “UNION vs UNION ALL” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Interview Prep lesson?
Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.