SQL INTERSECT Operator: Compare Common Rows Across Queries

Two queries run separately give you two result sets, and the answer you are after is the part they have in common. INTERSECT is the operator for that: put it between the two SELECT statements, and the database returns the rows that appear in both results, each one once.

The examples run on three small tables:

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    email       VARCHAR(40)
);

CREATE TABLE suppliers (
    supplier_id INT PRIMARY KEY,
    email       VARCHAR(40)
);

CREATE TABLE enrollments (
    student_id INT,
    course_id  INT
);

INSERT INTO customers VALUES
    (1, '[email protected]'),
    (2, '[email protected]'),
    (3, '[email protected]');

INSERT INTO suppliers VALUES
    (10, '[email protected]'),
    (11, '[email protected]'),
    (12, '[email protected]');

INSERT INTO enrollments VALUES
    (1, 101), (1, 202), (1, 303),
    (2, 101), (2, 101), (2, 202), (2, 202),
    (3, 101);

enrollments has no primary key, and student 2 is signed up twice for each of two courses, which is what the section on duplicates needs.

What the SQL INTERSECT operator does

Some of the people you buy from are also people you sell to, and both lists carry an email address. One statement finds the overlap:

SELECT email FROM customers
INTERSECT
SELECT email FROM suppliers
ORDER BY email;

Ada is only a customer and Ken is only a supplier, so neither address survives. What comes back is a set of values, not rows of either table: the result has one column, email, and nothing that says which customer or which supplier it belonged to. If the SELECT half of that is still new, SQL SELECT covers it.

INTERSECT syntax and rules

The keyword sits between two complete queries:

SELECT column1, column2
FROM table1

INTERSECT

SELECT column1, column2
FROM table2;

Five constraints apply to every INTERSECT:

1. both queries return the same number of columns
2. columns in matching positions hold compatible data types
3. the column names of the result come from the first query
4. duplicate rows are removed unless you write INTERSECT ALL
5. ORDER BY belongs to the whole statement and goes at the end, after the second query

The last one catches people out. A branch of a set operation cannot carry its own ORDER BY, and SQL Server adds one condition on top: you can only sort by a column that the left-side query returns.

INTERSECT examples

Both sides can read the same table with different filters, which is how you ask whether the same student is in two courses:

SELECT student_id
FROM enrollments
WHERE course_id = 101

INTERSECT

SELECT student_id
FROM enrollments
WHERE course_id = 202

ORDER BY student_id;

Student 3 is in 101 only, so the second query never produces that id. Chaining a third query narrows the answer again, to the students who are in all three courses:

SELECT student_id FROM enrollments WHERE course_id = 101
INTERSECT
SELECT student_id FROM enrollments WHERE course_id = 202
INTERSECT
SELECT student_id FROM enrollments WHERE course_id = 303;

Only student 1 is enrolled in 303, and each additional INTERSECT can only take rows away, never add them.

How INTERSECT handles duplicates

Student 2 has two rows in course 101 and two in course 202, and the first example above returned that id once. Plain INTERSECT returns a set: each row that both queries produce appears exactly once, however many times either side produced it.

What about INTERSECT ALL?

INTERSECT ALL keeps the copies instead of collapsing them:

SELECT student_id
FROM enrollments
WHERE course_id = 101

INTERSECT ALL

SELECT student_id
FROM enrollments
WHERE course_id = 202

ORDER BY student_id;

The rule is arithmetic, and the PostgreSQL manual states it as such: a row with m duplicates on the left and n on the right appears min(m,n) times in the result. Student 2 has two rows on each side, so two come back; student 1 has one on each side, so one does. Support is narrower than for INTERSECT itself, and the table further down says where it exists.

INTERSECT vs INNER JOIN

The join version of the customers and suppliers query can carry both keys, which the INTERSECT version has no way to express:

SELECT c.customer_id, s.supplier_id, c.email
FROM customers c
INNER JOIN suppliers s
  ON s.email = c.email
ORDER BY c.email;

The two operations work differently even where they agree. Emails are unique in both tables here, so the join returns the same two people the INTERSECT did. INTERSECT compares whole result rows and returns the ones both queries produced, in the columns of the first query. The join matches one table's column against another's. It returns one row per matching pair, and the SELECT list may name columns from either table, which is where customer_id and supplier_id come from above. A join is also free of the first two constraints listed earlier: the two sides need neither the same number of columns nor compatible types.

Pairing decides the row count too. An address repeated three times among the customers and twice among the suppliers gives six joined rows, so a join can return more rows than either table holds. INTERSECT can never return more rows than the smaller of the two results, and it collapses the repeats to one row unless you write ALL. SQL joins explained covers the join side in full.

INTERSECT vs UNION and EXCEPT

The three set operators take the same two queries and combine them in the three ways available:

Swapping the keyword in the opening example turns it into a list of the people you sell to and never buy from:

SELECT email FROM customers
EXCEPT
SELECT email FROM suppliers
ORDER BY email;

The order of the two queries matters for EXCEPT and not for INTERSECT: reverse them and you get Ken instead. Oracle AI Database 26ai spells this operator MINUS, and its SQL Language Reference says EXCEPT "is a synonym for MINUS and has the exact same semantics". The UNION row of that table has a walkthrough of its own in SQL UNION operator.

Database support and workarounds

INTERSECT is standard SQL and the engines have caught up with it at different times. INTERSECT ALL is the part still missing in places:

MySQL added the operator in 8.0.31 and takes ALL or DISTINCT after it, with DISTINCT as the default. SQL Server returns distinct rows and has no ALL form of the operator. SQLite has none either: its compound-select syntax attaches ALL to UNION only. The Oracle AI Database 26ai row comes from the SQL Language Reference linked above, which lists INTERSECT ALL alongside MINUS ALL and EXCEPT ALL.

Workaround when INTERSECT is unavailable

On a MySQL older than 8.0.31, EXISTS asks the same question one row at a time:

SELECT DISTINCT c.email
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM suppliers s
  WHERE s.email = c.email
)
ORDER BY c.email;

IN puts the same question in a subquery:

SELECT email
FROM customers
WHERE email IN (SELECT email FROM suppliers)
ORDER BY email;

The INNER JOIN above returns those two addresses as well, once you select c.email alone and add DISTINCT. The join needs that DISTINCT because it produces one row per matching pair. EXISTS and IN return each customers row at most once, so a DISTINCT there matters only when customers itself holds the same address twice. All three are longer than INTERSECT, which removes duplicates itself.

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

Run INTERSECT queries in DbSchema

A set operator that returns nothing is hard to debug from the statement alone, because the fault is usually in one branch: a filter that matches no rows, or two columns whose types do not line up. DbSchema connects through the PostgreSQL JDBC driver, the MySQL JDBC driver or another supported driver and reverse-engineers the schema into a diagram, so the type of each column is on screen while you write.

Run each branch on its own in the SQL Editor first, check the row count and the column order, then add the operator between them and run the whole statement. When the result raises a question about the rows behind it, open the tables in the Relational Data Editor, which shows a parent table with its child rows beside it and refilters the children as you move through the parent. The SQL Editor and the diagram are in the free Community Edition, and the Relational Data Editor is Pro. None of them writes to the database while you are reading a result.

Reach for INTERSECT when both halves of your question return the same columns and you want the values they agree on, and for a join when you want the columns of two tables side by side. Next time a set operator comes back empty, run its two branches one at a time before you touch the query. DbSchema runs each branch in the SQL Editor and draws the column types on the diagram beside it, both in the free Community Edition at https://dbschema.com/download.html.

FAQ

Does MySQL support INTERSECT?

Yes, from MySQL 8.0.31 onwards. Where one statement mixes the set operators, MySQL 8.4 evaluates INTERSECT first, before UNION and EXCEPT.

When should I use INTERSECT instead of EXISTS?

Use INTERSECT when both halves already return the same columns, since it says what you mean in one keyword and removes duplicates for you. Use EXISTS when the two sides have different shapes, or when you want columns from the outer table that the set operator cannot return.