UNION vs UNION ALL in SQL: What’s the Difference and When to Use Each

The difference between UNION and UNION ALL in SQL is mainly about duplicates and performance.

Here’s the breakdown 👇

Feature	UNION	UNION ALL
Duplicates	Removes duplicate rows from the result set.	Keeps all duplicates (does not remove them).
Performance	Slower, because SQL has to check and remove duplicates using DISTINCT.	Faster, because it simply combines results without checking for duplicates.
Use Case	When you need unique results from multiple queries.	When you need all results, including duplicates.

🔹 Example

Table: Customers_USA

id	name
1	John
2	Mary

Table: Customers_Canada

id	name
2	Mary
3	Alex

Query 1 — Using UNION

SELECT name FROM Customers_USA
UNION
SELECT name FROM Customers_Canada;

✅ Result (no duplicates):

name
John
Mary
Alex

Query 2 — Using UNION ALL

SELECT name FROM Customers_USA
UNION ALL
SELECT name FROM Customers_Canada;

✅ Result (includes duplicates):

name
John
Mary
Mary
Alex

🔹 Summary

• Use UNION → when you want unique combined results.
• Use UNION ALL → when you want all rows (and better performance).