INTERSECT

INTERSECT combines the results of two SELECT queries and returns only the rows that appear in both result sets. Think of it as the set-theory intersection of two lists — a row survives only if it was produced by both queries.

Like UNION, both queries must return the same number of columns with compatible types. INTERSECT automatically removes duplicate rows from its output, similar to DISTINCT.

Basic syntax

SQL

SELECT column1, column2 FROM table_a
INTERSECT
SELECT column1, column2 FROM table_b;

Worked example

Suppose a company has a list of employees who completed a compliance training course, and a separate list of employees who are currently assigned to handle sensitive customer data. To find employees who satisfy both conditions, INTERSECT is a natural fit.

training_completed and sensitive_data_access

SQL

SELECT employee_id FROM training_completed
INTERSECT
SELECT employee_id FROM sensitive_data_access;

-- training_completed: 101, 102, 103, 104
-- sensitive_data_access: 102, 104, 105

-- Result: employees present in BOTH lists
-- 102
-- 104

INTERSECT vs an equivalent JOIN

INTERSECT is conceptually similar to an INNER JOIN on all selected columns combined with DISTINCT, but it is usually easier to read when you are comparing whole rows rather than joining on a specific key.

SQL

-- Using INTERSECT
SELECT employee_id FROM training_completed
INTERSECT
SELECT employee_id FROM sensitive_data_access;

-- Equivalent using INNER JOIN
SELECT DISTINCT a.employee_id
FROM training_completed AS a
INNER JOIN sensitive_data_access AS b
  ON a.employee_id = b.employee_id;

-- Equivalent using EXISTS
SELECT DISTINCT employee_id FROM training_completed AS a
WHERE EXISTS (
  SELECT 1 FROM sensitive_data_access AS b
  WHERE b.employee_id = a.employee_id
);

MySQL support is version-dependent

MySQL only added native INTERSECT support in version 8.0.31. If you are on an older MySQL version (or any MySQL 5.x installation), INTERSECT will raise a syntax error. Check your version with SELECT VERSION(); and, if it is older than 8.0.31, use the INNER JOIN or EXISTS rewrite shown above instead.

Dialect support

Database	INTERSECT support
PostgreSQL	Yes, fully supported
SQL Server	Yes, fully supported
Oracle	Yes, fully supported
MySQL	Yes, but only from version 8.0.31 onward
SQLite	Yes, fully supported

INTERSECT returns rows common to both result sets.
Column count and compatible types must match, just like UNION.
Duplicates are removed automatically from the output.
Older MySQL versions need an INNER JOIN or EXISTS rewrite instead.