SQL INTERSECT: Rows Present in Both Queries

The INTERSECT operator returns only the rows that appear in the result of both queries at once. It is the most direct way to answer "who is in both sets" without hand-rolling a join, and it is one of SQL's three set operators alongside UNION and EXCEPT.

Basic syntax

INTERSECT sits between two SELECT statements and compares whole rows, column by column. Both queries must return the same number of columns with compatible types.

-- Users who both placed an order and requested a refund
SELECT user_id FROM purchases
INTERSECT
SELECT user_id FROM refunds;

The result is a sorted set of unique user_id values that occur in each input. A concrete example on our schema: countries that have both registered users and paid orders.

SELECT country FROM users
INTERSECT
SELECT u.country
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid';

Key properties of the operator:

• Every column is compared, and NULL is treated as equal to NULL (unlike a plain =).
• Duplicates collapse by default, just like DISTINCT.
• Column order matters: the first column matches the first, the second matches the second.

INTERSECT vs INTERSECT ALL

By default INTERSECT removes duplicates. When you need a row repeated as many times as it appears in both inputs, use INTERSECT ALL: it keeps the minimum number of occurrences from each side.

-- DISTINCT semantics: each matching id appears once
SELECT user_id FROM orders WHERE amount > 100
INTERSECT
SELECT user_id FROM orders WHERE status = 'paid';

-- Multiplicity preserved: min(count_left, count_right) copies
SELECT user_id FROM orders WHERE amount > 100
INTERSECT ALL
SELECT user_id FROM orders WHERE status = 'paid';

If user_id = 7 appears 3 times on the left and 2 times on the right, INTERSECT ALL returns it twice, while plain INTERSECT returns it once. In practice the plain form is what you want most of the time: you usually care whether a row belongs to both sets, not how many copies of it exist. And do not treat INTERSECT ALL as free — it still has to match rows and count occurrences on each side to keep min(count_left, count_right) copies. The only saving versus plain INTERSECT is that the final deduplication pass is skipped; the matching and counting still happen.

Gotcha: in PostgreSQL INTERSECT binds tighter than UNION and EXCEPT. In a chain like A UNION B INTERSECT C, the engine evaluates B INTERSECT C first. Use explicit parentheses so you never have to guess.

INTERSECT vs JOIN and EXISTS

The same "common membership" can be expressed with an INNER JOIN or EXISTS, but the semantics differ in the details.

-- INNER JOIN can multiply rows if either side has duplicates
SELECT DISTINCT a.user_id
FROM orders a
JOIN refunds b ON a.user_id = b.user_id;

-- EXISTS: clean, no row multiplication, handles NULL via predicate
SELECT DISTINCT o.user_id
FROM orders o
WHERE EXISTS (
  SELECT 1 FROM refunds r WHERE r.user_id = o.user_id
);

How to choose:

• INTERSECT when you compare whole row sets and want automatic deduplication.
• EXISTS when you need extra columns from the driving table that the second query does not expose.
• INNER JOIN when you need data from both tables, but watch for row multiplication and reach for DISTINCT.

NULL handling is the key difference: INTERSECT treats NULL as equal to NULL and matches such rows, while a join on a.user_id = b.user_id drops rows where the key is NULL. INTERSECT shines when both sets come from complex expressions: instead of a clumsy subquery in WHERE, you write two readable SELECT blocks.

Database support

INTERSECT has long been available in PostgreSQL, Oracle, SQL Server, and SQLite. In MySQL the operator only arrived in version 8.0.31 — earlier releases emulated it with an INNER JOIN plus DISTINCT, or with IN.

-- MySQL < 8.0.31 emulation of INTERSECT
SELECT DISTINCT user_id FROM purchases
WHERE user_id IN (SELECT user_id FROM refunds);

In ClickHouse, INTERSECT is supported, and whether it deduplicates or keeps duplicates is governed by the intersect_default_mode setting rather than being fixed. To avoid relying on that setting, spell out INTERSECT DISTINCT for classic set semantics or INTERSECT ALL when you want multiplicity preserved.

Quick summary:

• INTERSECT — set intersection with duplicate removal.
• INTERSECT ALL — intersection that respects multiplicity: min(count_left, count_right) copies of each row.
• Available in MySQL since 8.0.31; in ClickHouse the default is controlled by intersect_default_mode, so write DISTINCT or ALL explicitly.

INTERSECT makes "common membership" queries declarative and readable — reach for it when comparing whole row sets, and keep EXISTS/JOIN for cases where you need extra columns.