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;

Syntax	Description
INTERSECT	Returns rows common to both SELECT results. Duplicates are removed.
EXCEPT	Returns rows from the left SELECT result that are not present in the right SELECT result (PostgreSQL, SQL Server, SQLite, etc.).
MINUS	Equivalent to EXCEPT (used in Oracle and DB2).

The following orders table is used in the examples below.

Get customer IDs who ordered in both January and February (INTERSECT).

sample_intersect_except.sql

SELECT customer_id
FROM orders
WHERE MONTH(order_date) = 1
INTERSECT
SELECT customer_id
FROM orders
WHERE MONTH(order_date) = 2;

+-------------+
| customer_id |
+-------------+
|        1001 |
|        1005 |
+-------------+
2 rows in set

Get customers who ordered in January but not in February (EXCEPT).

sample_intersect_except.sql

SELECT customer_id
FROM orders
WHERE MONTH(order_date) = 1
EXCEPT
SELECT customer_id
FROM orders
WHERE MONTH(order_date) = 2;

+-------------+
| customer_id |
+-------------+
|        1003 |
+-------------+
1 row in set

MySQL does not support INTERSECT / EXCEPT before 8.0.31 — use IN / NOT IN instead.

sample_intersect_except.sql

-- 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_id |
+-------------+
|        1001 |
|        1005 |
+-------------+
2 rows in set

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'.