EasyConcept

What is the difference between UNION and UNION ALL in SQL?

Technology:
SQL
Experience:
Junior,
Mid-level
Topics:
SQL

Quick answer

UNION combines two result sets and removes duplicate rows, while UNION ALL combines them and keeps every row, which makes it faster.

Detailed explanation

UNION has to sort or hash the combined result to find duplicates, which costs time and memory. If you know the two queries cannot return the same row, or you want the duplicates, use UNION ALL. Both require the same number of columns with compatible types, and the column names come from the first query.

Example

sql
SELECT email FROM newsletter_subscribers
UNION                      -- one row per distinct email
SELECT email FROM customers;

SELECT email FROM newsletter_subscribers
UNION ALL                  -- keeps duplicates, faster
SELECT email FROM customers;