INTERSECT / EXCEPT

-- INTERSECT: Returns rows common to both SELECT results.
SELECT column FROM tableA
INTERSECT
SELECT column FROM tableB;

-- EXCEPT: Returns rows in the left SELECT that are not in the right SELECT (SQL standard).
SELECT column FROM tableA
EXCEPT
SELECT column FROM tableB;

-- MINUS: Equivalent to EXCEPT (used in Oracle instead of EXCEPT).
SELECT column FROM tableA
MINUS
SELECT column FROM tableB;

-- Get customer IDs who ordered in both January and February (INTERSECT).
SELECT customer_id
FROM orders
WHERE MONTH(order_date) = 1
INTERSECT
SELECT customer_id
FROM orders
WHERE MONTH(order_date) = 2;

-- Get customers who ordered in January but not in February (EXCEPT).
SELECT customer_id
FROM orders
WHERE MONTH(order_date) = 1
EXCEPT
SELECT customer_id
FROM orders
WHERE MONTH(order_date) = 2;

-- MySQL does not support INTERSECT / EXCEPT before 8.0.31 — use IN / NOT IN instead.
-- Alternative for INTERSECT.
SELECT DISTINCT customer_id FROM orders WHERE MONTH(order_date) = 1
  AND customer_id IN (SELECT customer_id FROM orders WHERE MONTH(order_date) = 2);

-- Alternative for EXCEPT.
SELECT DISTINCT customer_id FROM orders WHERE MONTH(order_date) = 1
  AND customer_id NOT IN (SELECT customer_id FROM orders WHERE MONTH(order_date) = 2);

-- Customer IDs who ordered in both January and February (INTERSECT).
customer_id
-----------
1001
1005
1012

-- Customer IDs who ordered only in January (EXCEPT).
customer_id
-----------
1003
1008

PostgreSQL, SQL Server, and SQLite support both INTERSECT and EXCEPT directly.

-- PostgreSQL / SQL Server / SQLite
SELECT customer_id FROM orders WHERE MONTH(order_date) = 1
INTERSECT
SELECT customer_id FROM orders WHERE MONTH(order_date) = 2;

In Oracle, use MINUS instead of EXCEPT. INTERSECT uses the same syntax as the SQL standard.

-- Oracle: use MINUS instead of EXCEPT
SELECT customer_id FROM orders WHERE EXTRACT(MONTH FROM order_date) = 1
MINUS
SELECT customer_id FROM orders WHERE EXTRACT(MONTH FROM order_date) = 2;

MySQL supports INTERSECT and EXCEPT from version 8.0.31 onward. For earlier versions, use IN or NOT IN as alternatives.

-- MySQL (before 8.0.31): alternative for INTERSECT
SELECT DISTINCT customer_id FROM orders WHERE MONTH(order_date) = 1
  AND customer_id IN (SELECT customer_id FROM orders WHERE MONTH(order_date) = 2);

-- MySQL (before 8.0.31): alternative for EXCEPT
SELECT DISTINCT customer_id FROM orders WHERE MONTH(order_date) = 1
  AND customer_id NOT IN (SELECT customer_id FROM orders WHERE MONTH(order_date) = 2);

INTERSECT and EXCEPT are SQL set operators that automatically remove duplicate rows, just like UNION. The number of columns and their data types must be compatible, the same requirement as UNION.

MySQL supports INTERSECT and EXCEPT from version 8.0.31 onward. For earlier versions, use IN, NOT IN, EXISTS, or NOT EXISTS as alternatives. In Oracle, use MINUS instead of EXCEPT.

For usage of the related set operators UNION and UNION ALL, see 'UNION / UNION ALL'.