SQL UNION vs UNION ALL: Key Differences, Performance & When to Use

So you're writing SQL queries and suddenly need to combine results from two tables. You google "how to merge SQL results" and bam – dozens of articles about SQL UNION vs UNION ALL. But half of them are so technical they make your eyes glaze over. I remember scratching my head for hours when I first encountered this. Let me save you that headache.

Picture this: last year I was building a report pulling customer data from multiple regions. My initial UNION query took 8 seconds to run because I didn't understand the performance trap. After switching to UNION ALL? Down to 2 seconds. That's the kind of real-world difference we're talking about here.

The Core Difference That Changes Everything

At its heart, the SQL UNION vs UNION ALL debate boils down to one thing: duplicate elimination. UNION acts like that overly cautious friend who removes duplicates "just in case", while UNION ALL is the efficient pragmatist that keeps everything.

Hands-on example: Imagine two lists of employee IDs who attended training sessions:
Session A: 101, 102, 103
Session B: 102, 103, 104
SELECT * FROM SessionA UNION SELECT * FROM SessionB → 101,102,103,104
SELECT * FROM SessionA UNION ALL SELECT * FROM SessionB → 101,102,103,102,103,104

When UNION Wastes Your Time (Literally)

I learned this the hard way during a data migration project. We were combining product tables from legacy systems using UNION. The query crawled because:

It sorted all 500k records internally
Compared every row against others for duplicates
Added 300ms overhead even when tables had unique IDs

Switching to UNION ALL cut execution time by 60%. The kicker? We later discovered duplicates were impossible due to our ID scheme. Ouch.

Performance Benchmarks That Actually Matter

Forget synthetic tests - here's real-world timing from our analytics server (1M rows per table):

Operation	Execution Time	CPU Load	Memory Usage
UNION ALL	1.2 seconds	Medium	Low
UNION	4.8 seconds	High	High
UNION with DISTINCT	5.1 seconds	Very High	Very High

Notice how UNION performs almost identically to SELECT DISTINCT? That's because behind the scenes, SQL UNION basically does this:

Concatenate all rows
Sort the entire dataset
Scan sorted data to remove adjacent duplicates

Meanwhile UNION ALL just does step 1. No sorting, no comparison. That's why it dominates in speed.

When to Actually Use Each Operator

UNION ALL Wins Here

Combining monthly sales reports where dates naturally partition data
Merging logs from different services (web servers, app servers)
Any pre-deduplicated data like unique user IDs
Data warehousing ETL processes (speed is critical)

Last quarter I optimized a client's nightly ETL job by replacing 12 UNION operations with UNION ALL. Their 4-hour window became 90 minutes. Operations team sent pizza.

When UNION is Necessary

Compiling distinct email lists from multiple sources
Finding unique visitors across website sections
Combining survey responses where duplicates invalidate results
Cases where data integrity trumps performance needs

Code example needing UNION:

-- Get unique customers from both stores
SELECT customer_id FROM store1_orders
UNION
SELECT customer_id FROM store2_orders;

Practical Syntax Rules Everyone Messes Up

Requirement	UNION	UNION ALL
Column count match	Strictly enforced	Strictly enforced
Data type compatibility	Required	Required
Column naming	Uses first query's names	Uses first query's names
ORDER BY position	Must be in final SELECT	Must be in final SELECT
LIMIT behavior	Applies after deduplication	Applies to raw results

Gotcha moment: You can't ORDER BY before the final union. This fails:

(SELECT name FROM employees ORDER BY id)
UNION ALL
(SELECT product FROM inventory);

The correct approach:

SELECT * FROM (
  SELECT name AS data, 'employee' AS type FROM employees
  UNION ALL
  SELECT product, 'inventory' FROM inventory
) combined
ORDER BY type;

Advanced Scenarios Where Choice Matters

Indexing Strategies

With UNION ALL, indexing source tables matters most. But with UNION, you're fighting the sort operation. One trick: pre-aggregate with GROUP BY before UNION if possible.

CTEs vs UNIONs

Common Table Expressions can sometimes replace unions. Compare:

-- Traditional UNION approach
SELECT * FROM invoices_2022
UNION ALL
SELECT * FROM invoices_2023;

-- CTE alternative
WITH all_invoices AS (
  SELECT * FROM invoices_2022
  UNION ALL
  SELECT * FROM invoices_2023
)
SELECT * FROM all_invoices;

The CTE version often reads clearer for complex multi-union queries.

Partitioning Workarounds

On one project with terabytes of log data, we couldn't use UNION ALL naively. Solution:

Create partitioned tables by date
Use UNION ALL between partitions
Materialize results incrementally

This avoided the 3-hour UNION timeout while keeping data fresh.

Your Burning SQL UNION vs UNION ALL Questions Answered

Does UNION sort results automatically?

Not guaranteed! UNION removes duplicates through sorting but final output order isn't specified. Always use explicit ORDER BY.

Can I use different column names in each SELECT?

Technically yes, but the final result uses the first query's names. This causes confusion:

SELECT user_id AS id FROM customers
UNION
SELECT product_id FROM orders; -- Column name becomes 'id'

Which is safer for production systems?

UNION ALL generally causes fewer surprises since it doesn't secretly sort gigabytes of data. But test with realistic data volumes first.

How do NULL values behave?

Both treat NULLs identically: two NULLs are considered duplicates in UNION. Controversial but true.

Any gotchas with large result sets?

UNION requires temporary storage equal to your dataset size - I've seen this crash servers. UNION ALL streams results incrementally.

Expert-Level Optimization Tactics

The Pre-Filter Strategy

Instead of:

SELECT * FROM transactions WHERE value > 100
UNION
SELECT * FROM refunds WHERE value > 100

Do this:

SELECT * FROM (
  SELECT * FROM transactions
  UNION ALL
  SELECT * FROM refunds
) combined
WHERE value > 100;

Why? The first version filters before duplicate check (good) but UNION still sorts. The second filters after union but avoids sort - usually faster.

Partial UNION ALL Optimization

Mix both strategically:

-- Stage 1: Fast UNION ALL for known-unique datasets
SELECT * FROM (
  SELECT * FROM fresh_data -- No duplicates
  UNION ALL
  SELECT * FROM legacy_data -- Has duplicates
) raw_combined

-- Stage 2: Deduplicate only where needed
SELECT DISTINCT * FROM raw_combined
WHERE source = 'legacy_data'
UNION ALL
SELECT * FROM raw_combined
WHERE source = 'fresh_data';

Saw this cut processing time by 75% on a healthcare dataset.

Final Thoughts From the Trenches

After fifteen years of SQL work, my default choice is always UNION ALL unless proven duplicates exist AND matter. The performance difference isn't academic - it's the difference between interactive analytics and watching progress bars.

That said, UNION has saved me when third-party APIs returned duplicate records. One memorable midnight outage was fixed by switching from UNION ALL to UNION when a sensor started duplicating readings.

Start with this decision tree:

Are source tables disjoint? → UNION ALL
Do I need duplicates? → UNION ALL
Is performance critical? → UNION ALL
Can duplicates break reports? → UNION
Unsure? Test both with production-like data

Remember that SQL UNION vs UNION All choices cascade through your data pipeline. Choose wisely and measure always.