Master the four set operations in SQL to combine, compare, and filter result sets from multiple queries efficiently.
What it is
Set operations allow you to combine the results of two or more SELECT statements into a single result set. The four primary operations are UNION, UNION ALL, INTERSECT, and EXCEPT (also known as MINUS in Oracle). These operations require that each query returns the same number of columns with compatible data types.
- UNION: Combines results and removes duplicate rows.
- UNION ALL: Combines results but keeps all duplicates.
- INTERSECT: Returns only rows present in both result sets.
- EXCEPT: Returns rows from the first result set that are not in the second.
Why it matters
- Data Consolidation: Merge data from similar tables (e.g., current year and previous year sales) without complex joins.
- Duplicate Handling: Choose between performance (
UNION ALL) and uniqueness (UNION) based on business needs. - Comparison Analysis: Quickly identify missing records or commonalities between datasets using
EXCEPTandINTERSECT. - Simplified Logic: Replace verbose subqueries or self-joins with cleaner, readable set operations.
Syntax or steps
The basic syntax places the operator between two SELECT statements. Optional ORDER BY clauses apply to the final combined result.
SELECT column1 FROM tableA
[UNION | UNION ALL | INTERSECT | EXCEPT]
SELECT column1 FROM tableB;Example
Assume two tables: employees_us and employees_uk, both containing name and department.
-- 1. Combine unique employees from both regions
SELECT name, department FROM employees_us
UNION
SELECT name, department FROM employees_uk;
-- 2. Find employees who work in BOTH regions (same name/dept)
SELECT name, department FROM employees_us
INTERSECT
SELECT name, department FROM employees_uk;
-- 3. Find employees in US who are NOT in UK
SELECT name, department FROM employees_us
EXCEPT
SELECT name, department FROM employees_uk;Explanation: The first query merges lists, removing exact duplicates. The second finds common entries. The third identifies exclusive entries in the US list. Note that UNION ALL would be faster for the first query if duplicates are acceptable or impossible.
Common mistakes
- Mismatched Columns: Queries must have the same number of columns. Fix by selecting specific columns instead of
*. - Incompatible Data Types: Column types must match (e.g., integer vs. string). Use casting functions like
CAST()to align types. - Unnecessary Duplicates Removal: Using
UNIONwhenUNION ALLsuffices causes performance hits due to sorting/deduplication overhead. - Ordering Confusion:
ORDER BYapplies to the entire result set, not individual queries. Place it at the very end.
When to use it
| Operation | Use Case | Performance Note |
|---|---|---|
UNION | You need distinct values across sources. | Slower due to deduplication. |
UNION ALL | You want all rows, including duplicates. | Faster; no deduplication step. |
INTERSECT | Find common records between two sets. | Often implemented via semi-joins internally. |
EXCEPT | Find differences (anti-join). | Useful for validation checks. |
Practice
Guided Exercise: Write a query to find all product IDs sold in either Q1 or Q2, ensuring no duplicates appear in the final list.
Challenge: Modify the query above to include duplicates if a product was sold in both quarters. What operator do you switch?
Hint: For the challenge, replace UNION with UNION ALL.
Quick check
Question: Which operation should you use if you want to see every row from both tables, even if they are identical?
Answer: UNION ALL.
Summary
Set operations provide a declarative way to merge and compare query results. Choosing between UNION and UNION ALL balances data cleanliness against performance, while INTERSECT and EXCEPT simplify comparative analysis tasks.