UNION, INTERSECT & EXCEPT

Joins combine tables side by side, adding columns. Set operations stack query results on top of each other: UNION combines them, INTERSECT keeps rows present in both, and EXCEPT keeps rows in the first but not the second.

UNION

SELECT city FROM customers WHERE city IS NOT NULL
UNION
SELECT location FROM departments WHERE location IS NOT NULL;

Every city with a customer or an office, each listed once. UNION removes duplicates across the combined result.

The rules

• Each query must return the same number of columns.
• Corresponding columns should have compatible types.
• Column names come from the first query.
• One ORDER BY at the very end sorts the combined result.

UNION ALL

SELECT 'customer' AS kind, name AS person, city FROM customers WHERE city = 'Lisbon'
UNION ALL
SELECT 'employee', first_name || ' ' || last_name, d.location
FROM employees AS e JOIN departments AS d ON d.id = e.department_id
WHERE d.location = 'Lisbon'
ORDER BY kind, person;

UNION ALL keeps every row, duplicates included. It's faster (no duplicate check) and is the right choice when you know the parts don't overlap or you want every row. A literal column like 'customer' AS kind labels where each row came from.

INTERSECT

SELECT city FROM customers
INTERSECT
SELECT location FROM departments;

Cities that have both customers and an office — plus a surprise NULL. Unlike =, set operations (and DISTINCT) treat two NULLs as equal, so Farah's missing city matches Research's missing location. Add WHERE city IS NOT NULL to the queries if you don't want it.

EXCEPT

SELECT id FROM customers
EXCEPT
SELECT customer_id FROM orders;

Customer ids with no orders. Oracle calls this operator MINUS. MySQL supports INTERSECT and EXCEPT from version 8.0.31.

Comparing whole rows

SELECT product_id FROM order_items WHERE order_id IN (SELECT id FROM orders WHERE order_date < '2026-01-01')
INTERSECT
SELECT product_id FROM order_items WHERE order_id IN (SELECT id FROM orders WHERE order_date >= '2026-01-01');

Products sold in both 2025 and 2026. Set operations compare complete rows, which makes them a neat way to compare two query results — for example, checking that a rewritten query returns exactly the same rows as the original (A EXCEPT B and B EXCEPT A should both be empty).

Building a report with UNION ALL

SELECT category AS label, COUNT(*) AS products FROM products GROUP BY category
UNION ALL
SELECT 'All categories', COUNT(*) FROM products;

Adding a total row beneath grouped results. (Some databases offer GROUP BY ROLLUP for this.)

Try it yourself

List every person in the database — customers and employees — with a label, sorted by name.