Combining Result Sets: UNION, INTERSECT, EXCEPT

Set operations combine the results of two separate SELECT statements — vertically stacking or comparing them, rather than joining tables side by side.

The golden rule

Both queries must return the same number of columns, in a compatible order and type. Column names in the final result come from the first query.

UNION — combine and remove duplicates

-- All distinct cities that are either customer cities or department locations
SELECT city AS location FROM customers
UNION
SELECT location FROM departments;

UNION ALL — combine and keep duplicates

UNION ALL is faster than UNION because it skips the duplicate-removal step — use it whenever you don't specifically need deduplication:

SELECT city AS location FROM customers
UNION ALL
SELECT location FROM departments;

INTERSECT — only rows in both

Returns rows that appear in both result sets. Supported in PostgreSQL, SQL Server, and modern SQLite — not in MySQL (use an INNER JOIN or IN subquery there instead).

-- Cities that are BOTH a customer city AND a department location
SELECT city FROM customers
INTERSECT
SELECT location FROM departments;

EXCEPT (MINUS in some engines) — rows in the first but not the second

Supported in PostgreSQL and SQL Server (SQL Server also historically supports it as EXCEPT; Oracle instead calls it MINUS). Not available in MySQL or SQLite < 3.39.

-- Customer cities that are NOT department locations
SELECT city FROM customers
EXCEPT
SELECT location FROM departments;

Open the Code Playground, pick any SQL engine (SQLite, PostgreSQL, MySQL, or SQL Server), and paste these queries in directly — the practice database above is already loaded for you, every time.

-- A ready-to-query practice database is loaded automatically before every run —
-- no CREATE TABLE needed. Available tables:
--   departments  (id, name, location)
--   employees    (id, first_name, last_name, email, department_id, job_title, salary, hire_date, manager_id)
--   customers    (id, customer_name, city, country, signup_date)
--   products     (id, product_name, category, price, stock_quantity)
--   orders       (id, customer_id, employee_id, order_date, status)
--   order_items  (id, order_id, product_id, quantity, unit_price)

SELECT * FROM employees WHERE department_id = 1;