SQL UNION, INTERSECT, EXCEPT

What are Set Operations?

Set operations combine the results of two or more SELECT queries:

• UNION — All unique rows from both queries.
• UNION ALL — All rows including duplicates.
• INTERSECT — Rows that appear in both queries.
• EXCEPT — Rows in the first query but not the second.

Syntax

SELECT columns FROM table1
UNION
SELECT columns FROM table2;

When to Use

• Combining results from different tables with the same structure
• Finding common records between datasets
• Identifying records in one set but not another

Key Points

1. Same Columns — All queries must return the same number of columns with compatible types.
2. UNION vs UNION ALL — UNION removes duplicates (slower). UNION ALL keeps them (faster).
3. ORDER BY — Apply ORDER BY only at the end, after the last SELECT.
4. Column Names — The column names come from the first SELECT.
5. EXCEPT vs NOT IN — EXCEPT handles NULLs correctly and is more readable.