Set Operations

Combining Whole Result Sets

Set operators glue together two result sets that have the same columns.

UNION and UNION ALL

SELECT name FROM customers_2023
UNION
SELECT name FROM customers_2024;

SELECT name FROM customers_2023
UNION ALL
SELECT name FROM customers_2024;

UNION removes duplicate rows; UNION ALL keeps everything and is faster.

INTERSECT and EXCEPT

-- customers present in both years
SELECT name FROM customers_2023
INTERSECT
SELECT name FROM customers_2024;

-- customers only in 2023
SELECT name FROM customers_2023
EXCEPT
SELECT name FROM customers_2024;

INTERSECT returns rows present in both. EXCEPT (MINUS in Oracle) returns rows in the first set but not the second.

Rules for Set Operations

• Both sides must have the same number of columns.
• The column types must be compatible.
• ORDER BY goes at the very end of the whole statement.

SELECT name FROM customers_2023
UNION
SELECT name FROM customers_2024
ORDER BY name;

Key Points

• UNION deduplicates; UNION ALL does not.
• INTERSECT and EXCEPT express set difference.
• Matching columns and types are required.
• ORDER BY belongs at the end of the combined query.