UNION, INTERSECT and EXCEPT

Joins go sideways, set operations go down

A join puts columns from two tables side by side. A set operation stacks two results on top of each other, or compares them row by row. You use it when the same kind of thing lives in two places: cities of customers and cities of offices, this year's and last year's customer lists, active and archived orders.

The rules

• Both SELECTs must return the same number of columns.
• Matching columns should have compatible types: text with text, numbers with numbers. PostgreSQL raises an error for text against integer; SQLite is lenient and allows it, which can hide mistakes.
• Columns are matched by position, not by name. The result takes its column names from the first SELECT.
• ORDER BY (and LIMIT) is written once, after the last SELECT, and sorts the whole combined result.

UNION and UNION ALL

SELECT city FROM customers
UNION
SELECT city FROM departments
ORDER BY city;
-- Bengaluru, Chennai, Delhi, Hyderabad, Mumbai, Pune

UNION removes duplicate rows: Pune appears in both tables (and twice among customers) but once in the result. UNION ALL keeps every row, so the same query with UNION ALL returns 11 rows. Removing duplicates costs work (sorting or hashing every row), so when you know there are no duplicates, or you want them (for example to count), use UNION ALL. It is the faster default.

SELECT name, 'customer' AS kind FROM customers WHERE city = 'Pune'
UNION ALL
SELECT name, 'employee' FROM employees WHERE dept_id = 3
ORDER BY name;
-- Asha Verma | customer
-- Farhan Ali | employee
-- Imran Khan | customer
-- Neha Joshi | employee

INTERSECT: rows in both

SELECT city FROM customers
INTERSECT
SELECT city FROM departments
ORDER BY city;
-- Chennai, Delhi, Mumbai, Pune

EXCEPT: rows in the first but not the second

SELECT id FROM products
EXCEPT
SELECT product_id FROM order_items;
-- 8   (the Power Bank, never ordered)

Set operations compare whole rows, and unlike WHERE they treat two NULLs as equal, so a NULL row in both inputs counts as a match. That makes EXCEPT a handy, NULL-safe way to compare two tables: run A EXCEPT B and B EXCEPT A; if both are empty, the tables hold the same rows.

Set operation or join or subquery?

Products never ordered can be found with EXCEPT, with NOT EXISTS (topic 2) or with LEFT JOIN ... IS NULL (module 5). EXCEPT is shortest when you only need the key columns; if you also need other columns, such as the name, NOT EXISTS is usually simpler, because with EXCEPT both sides must return the same columns.

Common mistakes

• Different column counts: "SELECTs to the left and right of UNION do not have the same number of result columns". Make both lists match.
• Columns in a different order on each side, such as name, city UNION city, name. The rows mix up silently, because matching is by position.
• Writing ORDER BY inside the first SELECT. Put one ORDER BY at the very end.
• Using UNION when duplicates are impossible or wanted. UNION ALL is faster and keeps every row.