SQL Set Operators โ UNION, UNION ALL, INTERSECT & EXCEPT
Set operators allow you to combine the results of multiple SELECT queries.
They are extremely useful when you have similar datasets and want to:
โข Combine records from different sources
โข Remove duplicates
โข Find common records
โข Find records existing in one dataset but not another
โข Compare two datasets
โข Combine current and historical data
The four important set operators are: UNION, UNION ALL, INTERSECT, EXCEPT
In Oracle, "MINUS" is commonly used instead of "EXCEPT".
1๏ธโฃ What Are Set Operators?
Suppose you have two tables: customers_2025 and customers_2026. Both contain: customer_id, customer_name. You want one result containing customers from both years.
You can use:
SELECT customer_id, customer_name FROM customers_2025
UNION
SELECT customer_id, customer_name FROM customers_2026;
The result combines the two result sets.
2๏ธโฃ UNION
"UNION" combines two result sets and removes duplicate rows.
SELECT customer_id FROM customers_2025
UNION
SELECT customer_id FROM customers_2026;
If customer "101" appears in both tables, it appears only once in the final result.
Example: 2025: 101, 102, 103 | 2026: 102, 103, 104 | Result: 101, 102, 103, 104
When to use UNION?
Use "UNION" when duplicate rows should be removed.
SELECT email FROM online_customers
UNION
SELECT email FROM store_customers;
This can produce a unique list of customer emails across both channels.
3๏ธโฃ UNION ALL
"UNION ALL" also combines result sets, but keeps duplicates.
SELECT customer_id FROM customers_2025
UNION ALL
SELECT customer_id FROM customers_2026;
Using the previous example: Result: 101, 102, 103, 102, 103, 104. The duplicate records remain.
4๏ธโฃ UNION vs UNION ALL
This is one of the most common SQL interview questions.
UNION โ Combines results โ Removes duplicates
UNION ALL โ Combines results โ Keeps duplicates
Performance consideration: "UNION" generally needs additional work to identify and remove duplicates.
"UNION ALL" does not need that deduplication step. Therefore, when duplicates are valid and you don't need to remove them, "UNION ALL" is generally preferable.
5๏ธโฃ Rules for Using Set Operators
The queries being combined must be compatible.
SELECT customer_id, customer_name FROM customers
UNION
SELECT customer_id, customer_name FROM archived_customers;
Both queries return 2 columns - This is valid.
But:
SELECT customer_id, customer_name FROM customers
UNION
SELECT customer_id FROM archived_customers;
is invalid because the number of columns doesn't match.
Important rule:
The participating SELECT statements should have:
1. The same number of columns
2. Compatible data types in corresponding positions. The column names in the final result generally come from the first SELECT.
6๏ธโฃ Column Order Matters
Set operators match columns by position, not by column name. So structure your queries carefully:
SELECT customer_id, customer_name FROM customers
UNION ALL
SELECT customer_id, customer_name FROM archived_customers;
7๏ธโฃ ORDER BY with UNION
If you want to sort the final combined result, put "ORDER BY" at the end.
SELECT customer_id, customer_name FROM customers_2025
UNION
SELECT customer_id, customer_name FROM customers_2026
ORDER BY customer_id;
8๏ธโฃ INTERSECT
"INTERSECT" returns rows that exist in both result sets.
SELECT customer_id FROM customers_2025
INTERSECT
SELECT customer_id FROM customers_2026;