SQL UNION vs UNION ALL: When to Use Each (With Visual Examples)

UNION and UNION ALL both combine query results vertically, but one removes duplicates while the other keeps them all. Choosing wrong can tank your query performance or give you incorrect results. This guide shows the difference visually.

UNION vs UNION ALL: The Visual Difference

Before we look at any SQL, let's see what these operators actually do to your data. Imagine you're a detective combining witness lists from two different crime scenes.

That's the core difference. UNION gives you unique rows. UNION ALL gives you everything. The question is: which one do you actually need?

How UNION Works (With Code)

UNION combines the results of two or more SELECT statements and automatically removes duplicate rows. It's essentially doing a DISTINCT on the combined results.

-- Combine witness lists, removing duplicates
SELECT name, role FROM scene_a_witnesses
UNION
SELECT name, role FROM scene_b_witnesses;

Practice combining data from multiple tables in SQLNoir's detective cases, where you'll query witnesses, suspects, and clues across different crime scenes.

How UNION ALL Works (With Code)

UNION ALL combines the results of two or more SELECT statements and keeps all rows, including duplicates. No deduplication means no extra work for the database.

-- Combine witness lists, keeping ALL rows
SELECT name, role FROM scene_a_witnesses
UNION ALL
SELECT name, role FROM scene_b_witnesses;

Side-by-Side Comparison

When to Use UNION vs UNION ALL

The decision is simpler than most tutorials make it. Start with this flowchart:

In practice, UNION ALL is the right choice more often than you'd think. Here are the key scenarios:

Performance: Why UNION ALL is Usually Faster

UNION has hidden work. To remove duplicates, the database must sort all rows and compare them. On small datasets this is negligible. On millions of rows, it can make a real difference.

-- Active and closed cases CAN'T overlap (mutually exclusive)
-- Using UNION here wastes database resources
SELECT case_id, title FROM active_cases
UNION ALL  -- ← Correct: skip unnecessary deduplication
SELECT case_id, title FROM closed_cases;

Common Mistakes (And How to Avoid Them)

These are the errors that trip up most SQL users when working with UNION operations.

Mistake 1: Column count mismatch

SELECT name, role, badge_number FROM officers
UNION ALL
SELECT name, role FROM detectives;

SELECT name, role, badge_number FROM officers
UNION ALL
SELECT name, role, NULL AS badge_number FROM detectives;

Mistake 2: ORDER BY in the wrong place

SELECT name FROM suspects ORDER BY name
UNION ALL
SELECT name FROM witnesses;

SELECT name FROM suspects
UNION ALL
SELECT name FROM witnesses
ORDER BY name;

Mistake 3: Using UNION when UNION ALL is correct

-- Combining January and February transactions
-- Each transaction has a unique ID - duplicates IMPOSSIBLE

-- ❌ Wasteful: UNION sorts and checks for duplicates that can't exist
SELECT * FROM january_transactions
UNION
SELECT * FROM february_transactions;

-- ✅ Better: UNION ALL skips unnecessary work
SELECT * FROM january_transactions
UNION ALL
SELECT * FROM february_transactions;

Mistake 4: Incompatible data types

SELECT name, hire_date FROM employees
UNION ALL
SELECT name, salary FROM contractors;

SELECT name, hire_date, NULL AS salary FROM employees
UNION ALL
SELECT name, NULL AS hire_date, salary FROM contractors;

UNION vs JOIN: What's the Difference?

This confusion is common. UNION and JOIN both combine data, but in completely different ways.

-- UNION: Stack two lists vertically
-- Result: More rows, same columns
SELECT name FROM team_a    -- 3 rows
UNION ALL
SELECT name FROM team_b;   -- 3 rows = 6 total rows

-- JOIN: Connect tables horizontally
-- Result: Same(ish) rows, more columns
SELECT a.name, b.department
FROM employees a
JOIN departments b ON a.dept_id = b.id;

Think of UNION as stacking two spreadsheets on top of each other. JOIN is like adding columns from one spreadsheet next to another based on a matching key.

Test Your Knowledge

FAQ