UNION ALL vs UNION (Deduplication Cost)
Understand the dedup work UNION does and why UNION ALL is faster when you know there are no duplicates.
UNION ALL vs UNION (Deduplication Cost) is a free SQL Academy lesson on CoddyKit — lesson 2 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 Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
The Difference
UNION— combines and removes duplicatesUNION ALL— combines and keeps every row
UNION ALL Is Faster
Dedup is expensive (sort or hash). UNION ALL skips it entirely. When you know there can't be duplicates, always pick UNION ALL.
Example: When Each Side Is Disjoint
Active and archived orders never overlap — use UNION ALL:
SELECT * FROM active_orders
UNION ALL
SELECT * FROM archived_orders;Example: When Duplicates Matter
Combining a list of order events with possible repeated rows: maybe you genuinely want to count each occurrence — UNION ALL.
SELECT order_id FROM email_events
UNION ALL
SELECT order_id FROM push_events;Example: When You Want Dedup
Two newsletter lists where some emails appear in both — you want one row per unique email:
SELECT email FROM list_a
UNION
SELECT email FROM list_b;UNION ALL with Different Sources
Stack metrics tables for an "all reports" feed:
SELECT 'login' AS event, ts, user_id FROM logins
UNION ALL
SELECT 'signup', ts, user_id FROM signups
UNION ALL
SELECT 'purchase', ts, user_id FROM purchases
ORDER BY ts DESC LIMIT 100;Order Inside Each Sub-Select
Most databases ignore ORDER BY inside individual sub-SELECTs of a UNION/UNION ALL (unless wrapped). Apply ORDER BY at the end:
SELECT ... FROM a
UNION ALL
SELECT ... FROM b
ORDER BY ts DESC;LIMIT Per Side
Wrap each side in parens to LIMIT independently:
(SELECT id FROM new_orders ORDER BY created_at DESC LIMIT 10)
UNION ALL
(SELECT id FROM old_orders ORDER BY created_at DESC LIMIT 10);When the Planner Optimises UNION
If the planner can prove the two sides are disjoint (e.g. mutually exclusive WHERE filters), it may run UNION as UNION ALL internally — but don't rely on it. Be explicit.
UNION vs OR
For OR conditions that can't use the same index efficiently, UNION ALL of two separately-indexed queries can be faster:
-- Slow OR (may not use either index):
SELECT * FROM users WHERE city = 'NYC' OR country = 'DE';
-- Sometimes faster:
SELECT * FROM users WHERE city = 'NYC'
UNION
SELECT * FROM users WHERE country = 'DE';Watch the Column Order
Set operators match by position, NOT by name. SELECT a, b FROM t1 UNION ALL SELECT b, a FROM t2 swaps semantically — be careful.
Recap
UNION ALL is the default for performance; reach for UNION only when you genuinely need dedup.
Quick Check
When can you replace UNION with the faster UNION ALL?
Frequently asked questions
Is the “UNION ALL vs UNION (Deduplication Cost)” lesson free?
Yes — the full text of “UNION ALL vs UNION (Deduplication Cost)” is free to read here on the web, and the SQL Academy 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 Academy course, upgrade to CoddyKit PRO.
What will I learn in “UNION ALL vs UNION (Deduplication Cost)”?
Understand the dedup work UNION does and why UNION ALL is faster when you know there are no duplicates. You practise SQL Academy 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 Academy?
No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “UNION ALL vs UNION (Deduplication Cost)” 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 Academy lesson?
Yes. Every SQL Academy 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.
All lessons in this course
- UNION, INTERSECT, EXCEPT
- UNION ALL vs UNION (Deduplication Cost)
- CASE Expressions and Pivot Queries
- Crosstab Patterns (PostgreSQL crosstab())