Back to all posts

SQL INTERSECT Operator: Compare Common Rows Across Queries

For someone who writes SELECT with WHERE and now needs the rows two queries have in common; every query is run on PostgreSQL, MySQL, MariaDB and SQLite.

On this page

You have two lists, such as the email addresses of your customers and those of your suppliers, and you want the addresses that are on both. INTERSECT gives you that: write it between two SELECT statements, and the database returns each row that both queries return, once.

SELECT column1, column2 FROM table1
INTERSECT
SELECT column1, column2 FROM table2
ORDER BY column1;

The two queries have to agree:

  • They return the same number of columns.
  • Columns pair up by position, so each position needs compatible types.
  • The result takes its column names from the first query.
  • Repeated rows come back once; INTERSECT ALL keeps them, on the engines that have it.
  • One ORDER BY, after the last query, sorts the whole result.

What the SQL INTERSECT operator does

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, so student 2 can be signed up twice for each of courses 101 and 202. The section on duplicates needs those repeats.

The people you sell to and also buy from:

SELECT email FROM customers
INTERSECT
SELECT email FROM suppliers
ORDER BY email;
email
[email protected]
[email protected]
The customers query returns ada, grace and linus, the suppliers query returns grace, linus and ken, and INTERSECT returns grace and linus

Ada is only a customer and Ken is only a supplier, so neither address comes back. The result is a list of values, not rows of either table: it has one column, email, and nothing in it says which customer or supplier an address belongs to.

Every query in this article returned the same rows on PostgreSQL 17.9, MySQL 9.1, MariaDB 11.8 and SQLite 3.50, except where a section says otherwise. If the SELECT part is still new, SQL SELECT covers it.

Common rows from one table, and from three queries

Both queries can read the same table with different filters. These are the students in both course 101 and course 202:

SELECT student_id FROM enrollments WHERE course_id = 101
INTERSECT
SELECT student_id FROM enrollments WHERE course_id = 202
ORDER BY student_id;
student_id
1
2

Student 3 takes only course 101, so the second query never returns that id. A single WHERE course_id = 101 AND course_id = 202 cannot ask the same question: WHERE tests one row at a time, no row holds two course ids, and that query returns no rows on any of the four engines.

A third query narrows the answer again. Add INTERSECT SELECT student_id FROM enrollments WHERE course_id = 303 to the end, and only student 1, the one student in course 303, is left. Each extra INTERSECT can remove rows and never adds one.

How INTERSECT handles duplicates

Student 2 has two rows in each course, yet the query above returned that id once. Plain INTERSECT returns each common row once, however many times either query produced it.

INTERSECT ALL keeps the repeats:

SELECT student_id FROM enrollments WHERE course_id = 101
INTERSECT ALL
SELECT student_id FROM enrollments WHERE course_id = 202
ORDER BY student_id;
student_id
1
2
2

A row that the first query returns m times and the second n times appears min(m, n) times in the result (PostgreSQL 17). Student 2 has two rows on each side, so two come back. MySQL also accepts INTERSECT DISTINCT, which spells out the default (MySQL 8.4). SQLite has no INTERSECT ALL and stops at near "ALL": syntax error; the support table further down covers the other engines.

INTERSECT vs INNER JOIN

A join can answer the customers and suppliers question too. customers c INNER JOIN suppliers s ON s.email = c.email returns grace and linus, and it can also return c.customer_id and s.supplier_id beside each address, which INTERSECT has no way to do. The two agree here only because each address appears once per table. INTERSECT compares whole result rows and returns the columns of the first query. A join returns one row per matching pair.

That pairing changes the row count. The course question, written as a join of enrollments with itself:

SELECT a.student_id
FROM enrollments a
INNER JOIN enrollments b ON b.student_id = a.student_id
WHERE a.course_id = 101 AND b.course_id = 202
ORDER BY a.student_id;
student_id
1
2
2
2
2

Each of student 2's two rows in course 101 pairs with each of the two in course 202, which makes four rows, where INTERSECT returned one and INTERSECT ALL two. SELECT DISTINCT brings the join back to the INTERSECT answer, except for NULLs, which the NULL section below covers. SQL joins explained covers joins in full.

INTERSECT vs UNION and EXCEPT

The three set operators combine the same two queries in three ways:

OperatorReturns
UNIONrows from either query, duplicates removed
INTERSECTrows that both queries return
EXCEPTrows of the first query that the second does not return

Swap INTERSECT for EXCEPT in the opening example, and only [email protected] comes back: the one person you sell to and never buy from. The order of the two queries matters for EXCEPT and not for INTERSECT: reverse them and you get Ken instead. Oracle calls this operator MINUS, and its SQL Language Reference for Oracle AI Database 26ai says EXCEPT "is a synonym for MINUS and has the exact same semantics". SQL UNION operator walks through UNION.

Which operator runs first

Mix the operators in one statement, and the engines disagree on the order. This query is meant to take the customers plus supplier 12 and keep the suppliers among them:

SELECT email FROM customers
UNION
SELECT email FROM suppliers WHERE supplier_id = 12
INTERSECT
SELECT email FROM suppliers
ORDER BY email;

PostgreSQL, MySQL and MariaDB run the INTERSECT first, as customers UNION (supplier 12 INTERSECT suppliers), and return all four addresses. SQLite runs the UNION first, Ada drops out at the INTERSECT, and it returns grace, ken and linus:

EngineOrder without parentheses
PostgreSQL 17, MySQL 8.4 and 9.1, MariaDB 11.8INTERSECT first, then UNION and EXCEPT left to right
SQL ServerINTERSECT first, then EXCEPT and UNION left to right
SQLite 3.50left to right
Oracle AI Database 26aileft to right

To get one answer everywhere, move the part that must run first into a derived table, SELECT email FROM (... UNION ...) AS everyone, and put the INTERSECT after it. All four engines then return grace, ken and linus. Plain parentheses around the UNION are not enough, because SQLite rejects them with near "(": syntax error.

Errors when the two queries don't line up

Break one of the rules from the top of the page, and each engine answers in its own words:

MistakePostgreSQL 17.9MySQL 9.1 and MariaDB 11.8SQLite 3.50
different number of columnseach INTERSECT query must have the same number of columnsERROR 1222 (21000): The used SELECT statements have a different number of columnsSELECTs to the left and right of INTERSECT do not have the same number of result columns
customer_id paired with emailINTERSECT types integer and character varying cannot be matchedno error, no rowsno error, no rows

The middle row is the one to watch. MySQL, MariaDB and SQLite compare the number with the text, find no match, and return an empty result, so a pairing mistake looks like a real answer of "nothing in common".

How INTERSECT treats NULL

Add a customer and a supplier who have no email address yet:

INSERT INTO customers VALUES (4, NULL);
INSERT INTO suppliers VALUES (13, NULL);

Run the opening INTERSECT again, and the NULL comes back as a common row. PostgreSQL sorts it last:

email
[email protected]
[email protected]
NULL

MySQL, MariaDB and SQLite return the same three rows with the NULL first. Set operators compare rows the way DISTINCT does, and there two NULLs count as equal (SQLite, SQL Server). A comparison with = works differently: NULL = NULL is NULL, not true, so the INNER JOIN above, an IN subquery and an EXISTS with = still return grace and linus only.

The two answers fit different questions. If a missing address means "unknown", two unknowns are not the same person, so add WHERE email IS NOT NULL to both queries.

Database support and workarounds

INTERSECT is standard SQL, and INTERSECT ALL is the part still missing in places:

DatabaseINTERSECTINTERSECT ALL
PostgreSQL 17yesyes
MySQL8.0.31 and later8.0.31 and later
MariaDB 11.8yesyes
SQL Serveryesno
SQLite 3.50yesno
Oracle AI Database 26aiyesyes

The SQL Server row comes from its syntax, which has no ALL form, and the Oracle row from Oracle's page linked above. MySQL's version is from its manual, and the other rows are from the runs.

Workaround when INTERSECT is unavailable

MySQL 5.7.44 rejects the opening query with ERROR 1064 (42000): You have an error in your SQL syntax. There, 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
);

With the two NULL rows from the previous section in place, it returns NULL, grace and linus, as INTERSECT does on MySQL 8.0.31 and later. <=> is MySQL's NULL-safe equality, so a NULL matches a NULL; with = in its place, the NULL row drops out.

Run INTERSECT queries in DbSchema

When an INTERSECT returns fewer rows than you expect, run its two queries one at a time. A filter that matches nothing shows up there, and so does a pair of columns that can never match, such as the number and the text in the error table above.

Connect DbSchema to the database through its PostgreSQL, MySQL, MariaDB or SQLite JDBC driver, open the DbSchema SQL Editor with Query → SQL Editor, and type both queries and the combined statement. Select the first query and press Run, or Ctrl+Enter: Run runs the selected text and shows its rows in the result grid. Do the same for the second query, compare the row counts and the column order, then run the whole statement.

The DbSchema SQL Editor with a selected query on customers and orders, and its rows in the result grid under the editor

Download DbSchema, connect it to your database, and run both halves of your next INTERSECT one at a time in the SQL Editor, which the free Community Edition includes.

DbSchema ER diagram designer DbSchema ER diagram designer

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

FAQ

Does MySQL support INTERSECT?

Yes, from MySQL 8.0.31 (MySQL 8.0), with both INTERSECT ALL and INTERSECT DISTINCT. Older versions reject it with a syntax error, and the EXISTS query above replaces it.