SQL Server Interview Question #79

What is the difference between UNION and UNION ALL?

Joins, Subqueries, CTEs & Set Operators Mid-Level Intermediate

Detailed Explanation

UNION combines compatible result sets and removes duplicates. UNION ALL combines them without duplicate elimination.

UNION ALL is generally less expensive because SQL Server does not need to perform the extra work required to enforce distinctness. Therefore, use UNION ALL when duplicate rows are acceptable or when the source sets are already known to be disjoint.

Code Example

SELECT Email
FROM dbo.Customers
UNION ALL
SELECT Email
FROM dbo.Suppliers;