SQL UNION vs UNION ALL
🔹 What They Do
UNION and UNION ALL combine result sets produced by two or more SELECT statements. Both require matching column counts and compatible data types so the rows line up correctly.
🔹 1. UNION
- Combines rows and automatically removes duplicates.
- Performs an implicit
DISTINCT check.
- Runs slightly slower because duplicate elimination takes extra work.
SELECT city FROM customers
UNION
SELECT city FROM suppliers;
✅ Result: only the unique city names from both tables.
🔹 2. UNION ALL
- Combines rows and keeps duplicates intact.
- Skips the distinct comparison, so it is faster.
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;
✅ Result: every city value, including duplicates.
🔹 3. Rules for Both
- Each
SELECT must return the same number of columns.
- Column data types must be compatible across the combined queries.
🔹 Example Tables
| Customers |
|---|
| id | city |
|---|
| 1 | New York |
| 2 | London |
| Suppliers |
|---|
| id | city |
|---|
| 1 | London |
| 2 | Paris |
🔹 UNION Result
New York
London
Paris
🔹 UNION ALL Result
New York
London
London
Paris
🔹 When to Use
- Use UNION when you only need unique values in the combined result.
- Use UNION ALL when duplicates carry meaning—such as counting total transactions or occurrences.
