Lesson 34 of 61

5 mins read

SQL UNION ALL vs UNION: Performance & Duplicates

In Plain English

`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.

The Mental Model

"`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

Blazing Fast: Zero sorting or hashing overhead; ideal for high-throughput pipelines and data pipelines.

Preserves Duplicates: All rows from all SELECT statements are preserved in output.

Default Best Practice: Default to UNION ALL unless you explicitly require deduplication across disjoint queries.

Common Beginner Traps & How to Fix Them

Mistake: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.

Pro Tip: In audit loggers, always use `UNION ALL` so that identical consecutive events are never suppressed.

Live Interactive Example

Hit Run Code to see it live

High-Performance Event Streaming with UNION ALL

Python 3.12

Output Console

Click "Run Code" to view the rendered output.

How it works: UNION ALL preserved both separate login events with zero deduplication overhead.

Your Turn: Micro Challenge

No pressure! Edit the starter code below and test your solution with instant feedback.

Micro Exercise

Stack All Rows with UNION ALL

Stack all rows from `tab1` and `tab2` using `UNION ALL`.

Sandbox Output

Click "Run & Check" to test your solution.

Finished reading and practicing?

Mark this lesson as completed to update your course progress.