What is the difference between UNION and UNION ALL?

The difference between UNION and UNION ALL in SQL is subtle but important. Let’s break it down clearly:

1. UNION

• Purpose: Combines the results of two or more SELECT queries into a single result set.
• Duplicates: Automatically removes duplicate rows from the combined result.
• Sorting: Internally, the database may perform a sort to eliminate duplicates, which can make it slower for large datasets.
• Syntax Example:

SELECT name FROM employees
UNION
SELECT name FROM managers;

• Result: A list of names from both tables without duplicates.

2. UNION ALL

• Purpose: Also combines the results of two or more SELECT queries.
• Duplicates: Keeps all duplicate rows; no removal is performed.
• Performance: Faster than UNION because it doesn’t need to check for duplicates.
• Syntax Example:

SELECT name FROM employees
UNION ALL
SELECT name FROM managers;

• Result: A list of names from both tables, including duplicates.

✅ Key Points