Python SQL UNION ALL vs UNION: Performance & Duplicates
`UNION ALL` combines results from multiple queries without removing duplicate rows. Because it skips the computationally expensive deduplication sorting and hashing passes required by `UNION`, `UNION ALL` is significantly faster and should be the default choice whenever duplicates are acceptable or impossible.
"`UNION` is merging two guest lists and carefully cross-referencing to cross off duplicate names; `UNION ALL` is taking both sheets of paper and stapling them together in 1 second flat."
Deep Dive: How It Works
UNION ALL Performance Advantage: Zero memory buffering, zero sorting, O(N) streaming performance directly from disk to client.
When to Use UNION ALL: When tables are mutually exclusive (e.g. combining `current_year_orders` and `archive_2025_orders`), duplicates are mathematically impossible, making `UNION` a wasteful bottleneck.
Tracking Source Provenance: Add a literal discriminator column (`SELECT id, 'USA' AS region`) to identify which query produced each row.
Syntax Blueprint
SELECT id, amount, 'Current' AS status FROM active_orders UNION ALL SELECT id, amount, 'Historical' AS status FROM archived_orders;
UNION ALL streams rows immediately without paying the performance cost of deduplication.
Core Rules to Remember
Common Beginner Traps & How to Fix Them
Using `UNION` instead of `UNION ALL` on queries returning 10 million rows from mutually exclusive partitions.Why it happens: Habitually writing UNION without realizing it triggers a multi-gigabyte sort spill to disk.
How to fix: Use `UNION ALL` on partitioned datasets.
Live Interactive Example
Hit Run Code to see it liveYour Turn: Micro Challenge
No pressure! Edit the starter code below and test your solution with instant feedback.
Stack All Rows with UNION ALL
- Stack all rows from `tab1` and `tab2` using `UNION ALL`.
Finished reading and practicing?
Mark this lesson as completed to update your course progress.