Learn the key differences between UNION and UNION ALL in SQL. Visual examples showing when duplicates matter, performance tips, and a decision flowchart.
Jump into the SQLNoir case files and put these tips to work.
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.
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.
Scene A Witnesses:
Scene B Witnesses:
Notice: Detective Mills and Dr. Lopez appear at both scenes.
Duplicates removed. Each person listed once.
All rows kept. Duplicates preserved.
That's the core difference. UNION gives you unique rows. UNION ALL gives you everything. The question is: which one do you actually need?
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.
name, rolescene_a_witnessesname, rolescene_b_witnessesAnatomy of a UNION query
-- Combine witness lists, removing duplicates SELECT name, role FROM scene_a_witnesses UNION SELECT name, role FROM scene_b_witnesses;
Result: 4 rows. Detective Mills and Dr. Lopez appear once each, even though they were at both scenes.
Practice combining data from multiple tables in SQLNoir's detective cases, where you'll query witnesses, suspects, and clues across different crime scenes.
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;
Result: 6 rows. All original rows from both tables, including duplicates.
| Feature | UNION | UNION ALL |
|---|---|---|
| Duplicates | Removed | Kept |
| Performance | Slower (sorts + dedupes) | Faster (no extra step) |
| Result size | Smaller or equal | Always sum of both |
| When to use | Need unique rows | Need all rows or know no dupes exist |
| Memory usage | Higher (sorting) | Lower |
UNION vs UNION ALL at a Glance
The decision is simpler than most tutorials make it. Start with this flowchart:
If you answered NO to either question, use UNION ALL (faster)
In practice, UNION ALL is the right choice more often than you'd think. Here are the key scenarios:
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;
A case is either active or closed, never both. UNION would sort everything for no benefit.
Think you understand how to combine data from multiple sources? Put your detective skills to the test.
Start InvestigationThese are the errors that trip up most SQL users when working with UNION operations.
SELECT name, role, badge_number FROM officers UNION ALL SELECT name, role FROM detectives;
•First query has 3 columns, second has 2
•SQL will throw an error
SELECT name, role, badge_number FROM officers UNION ALL SELECT name, role, NULL AS badge_number FROM detectives;
•Both queries now have 3 columns
•NULL fills the missing data
Every SELECT in a UNION must have the same number of columns
SELECT name FROM suspects ORDER BY name UNION ALL SELECT name FROM witnesses;
•ORDER BY can only appear once, at the very end
•Most databases will throw a syntax error
SELECT name FROM suspects UNION ALL SELECT name FROM witnesses ORDER BY name;
•ORDER BY applies to the combined result
•Sorts all rows together
ORDER BY comes after all UNION operations, and only once
-- 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;
When you know duplicates are impossible, UNION ALL is always the right choice.
SELECT name, hire_date FROM employees UNION ALL SELECT name, salary FROM contractors;
•hire_date is a DATE, salary is a NUMBER
•Can't combine incompatible types in same position
SELECT name, hire_date, NULL AS salary FROM employees UNION ALL SELECT name, NULL AS hire_date, salary FROM contractors;
•Each data type gets its own column
•NULL fills gaps appropriately
Columns at the same position must have compatible data types
This confusion is common. UNION and JOIN both combine data, but in completely different ways.
| Aspect | UNION / UNION ALL | JOIN |
|---|---|---|
| Direction | Vertical (stacks rows) | Horizontal (adds columns) |
| Result shape | Same columns, more rows | More columns, varies rows |
| Requirements | Same column count & types | Related key columns |
| Use case | Combine similar datasets | Connect related datasets |
UNION stacks data vertically, JOIN connects data horizontally
-- 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.
Q1.You need to combine two tables with identical structure. One is 'current_employees' and one is 'former_employees'. Some people might appear in both (rehires). You want a list of all unique people. Which do you use?
Q2.You're combining sales data from Q1 and Q2 tables. Each row is a distinct transaction with a unique ID. No transaction can appear in both tables. Which is faster?
Q3.You run: SELECT name FROM tableA UNION ALL SELECT name FROM tableB. TableA has 100 rows, tableB has 50 rows. How many rows in the result?
Q4.Which requires more database resources to execute?
Ready to put your SQL skills to work on real mysteries?
SQLNoir's 6 detective cases challenge you to write queries, combine clues, and crack cases using real SQL.
Start Your Investigation →UNION combines results and removes duplicate rows. UNION ALL combines results and keeps all rows including duplicates. UNION ALL is faster because it skips the deduplication step.
Use UNION when you need unique rows and duplicates would be incorrect for your use case. Use UNION ALL when you need all rows, or when you know your source data cannot have overlapping rows (which is the more common scenario).
UNION ALL is faster because it simply appends results. UNION must sort and compare all rows to find and remove duplicates, which takes extra CPU time and memory.
Yes, but only once at the very end of your combined query. ORDER BY applies to the final combined result, not to individual SELECT statements. Putting ORDER BY before UNION will cause a syntax error.
You'll get an error. Both SELECT statements must have the same number of columns, and the data types must be compatible. You can use NULL or type casting to align columns when needed.