Difference Between UNION and UNION ALL

Feature	UNION	UNION ALL
Duplicates	Removes duplicate rows (DISTINCT).	Keeps all rows, including duplicates.
Performance	Slower — must sort and remove duplicates.	Faster — no extra work to remove duplicates.
Result Size	Smaller or equal (no duplicates).	Can be bigger (duplicates allowed).
Sorting	May add overhead due to de-duplication.	No sorting unless you explicitly order.

---

1. UNION

Combines the result sets of two queries and removes duplicates automatically.

Example

SELECT city FROM Customers
UNION
SELECT city FROM Suppliers;

✅ Combines all cities from Customers and Suppliers, but if the same city appears in both — it will show only once.

• Behind the scenes: database does a DISTINCT to eliminate duplicates.
• Slower on big datasets because of sorting/de-duplication.

---

2. UNION ALL

Combines the result sets without removing duplicates — faster.

Example

SELECT city FROM Customers
UNION ALL
SELECT city FROM Suppliers;

✅ Combines all cities — if “New York” appears 10 times, it will appear 10 times in the result.

• No DISTINCT — much faster because no need to sort or compare rows.
• Duplicates are kept exactly as they are.

Visual Example

Query 1 Result	Query 2 Result
Paris	New York
London	Paris
Berlin	Tokyo

---

• UNION result:

Paris
London
New York
Berlin
Tokyo

(✅ No duplicates — Paris appears once.)

UNION ALL result:

Paris
London
New York
Paris
Berlin
Tokyo

(✅ Duplicates kept — Paris appears twice.)

When Should You Use Which?

Use UNION when	Use UNION ALL when
You want unique rows only.	You want all rows including duplicates.
You are okay with slower performance for clean results.	You need faster performance and duplicates don’t matter (or you’ll clean them later).
Data might have overlaps you want to eliminate.	Data should preserve frequency/count.

Important Notes

• Both queries must have the same number of columns and compatible types.
• Column names are taken from the first query.
• If you want an ordered result, you must add an ORDER BY at the end: sqlCopyEdit

SELECT city FROM Customers
UNION
SELECT city FROM Suppliers
ORDER BY city;

In Short

UNION removes duplicates (DISTINCT).
UNION ALL keeps all rows (no DISTINCT).

✅ UNION = clean but slower.
✅ UNION ALL = fast but may have duplicates.