INTERSECT | PostgreSQL Tutorial

The INTERSECT operator returns only the rows that appear in the result sets of both SELECT queries. It is the set-theoretic intersection — any row not present in both results is excluded. Like UNION, INTERSECT automatically removes duplicate rows from the output.

INTERSECT syntax

SELECT select_list FROM A
INTERSECT
SELECT select_list FROM B;

Both queries must return the same number of columns in the same order with compatible data types. To sort the result, add ORDER BY after the final query:

SELECT select_list FROM A
INTERSECT
SELECT select_list FROM B
ORDER BY sort_expression;

INTERSECT example

Find films that are both top-rated and most popular:

SELECT * FROM top_rated_films
INTERSECT
SELECT * FROM most_popular_films;

     title      | release_year
----------------+--------------
 The Godfather  |         1972
 The Dark Knight|         2008
(2 rows)

Only the two films that appear in both tables are returned. Films exclusive to one table are excluded.

INTERSECT with ORDER BY

SELECT * FROM top_rated_films
INTERSECT
SELECT * FROM most_popular_films
ORDER BY release_year;

     title      | release_year
----------------+--------------
 The Godfather  |         1972
 The Dark Knight|         2008
(2 rows)

INTERSECT vs INNER JOIN

Both can find common records, but they work differently:

INTERSECT: compares entire rows for equality across two result sets. No join key needed. Automatically deduplicates.
INNER JOIN: connects tables on a specific key and can return additional columns from each side. More flexible but requires a shared key column.

Prefer INTERSECT when both queries have the same column structure and you want exact row matching. Use INNER JOIN when you need to pull related columns from both tables or when the key columns differ.

Key tips for using INTERSECT

INTERSECT always removes duplicates. Use INTERSECT ALL to preserve duplicate rows, returning each common row as many times as it appears in the smaller count across both result sets.
Column names in the output are taken from the first SELECT. Use aliases in the first query to control output column names.
All set operators (UNION, INTERSECT, EXCEPT) have the same precedence and are evaluated left to right. Use parentheses to control evaluation order when mixing operators.
INTERSECT can be used with more than two queries: chain additional INTERSECT clauses to find rows common across three or more result sets.
For large tables, an INNER JOIN on an indexed key may be faster than INTERSECT since INTERSECT must hash or sort the full result sets to find common rows.

Reference: PostgreSQL documentation — Combining Queries (UNION, INTERSECT, EXCEPT).

Frequently Asked Questions

What does the PostgreSQL INTERSECT operator do?

INTERSECT returns only the rows that appear in both result sets. It performs an implicit DISTINCT, so duplicate rows in the intersection are removed. If a row exists in one query result but not the other, it is excluded. This makes INTERSECT useful for finding common members between two data sets — for example, customers who appear in both a subscriber list and an order history.

What are the rules for using INTERSECT in PostgreSQL?

Both SELECT statements must return the same number of columns in the same order, and the data types of corresponding columns must be compatible. ORDER BY can only be placed after the final SELECT statement, not inside individual component queries. These rules are the same as for UNION and EXCEPT.

What is the difference between INTERSECT and INNER JOIN?

INTERSECT compares entire rows across two result sets and returns rows where all column values match. An INNER JOIN connects two tables on a specific key and can return additional columns from either table. Use INTERSECT when both queries return the same column structure and you want rows that are identical in both; use INNER JOIN when you want to combine related data from two tables using a join key.

Does PostgreSQL support INTERSECT ALL?

Yes. INTERSECT ALL returns all matching rows, including duplicates, based on the minimum count of each row in both result sets. For example, if a row appears 3 times in the first result and 2 times in the second, INTERSECT ALL returns it 2 times. INTERSECT (without ALL) always returns at most one copy of each distinct matching row.

Can INTERSECT be used with more than two queries?

Yes. You can chain multiple INTERSECT operators: SELECT ... INTERSECT SELECT ... INTERSECT SELECT .... Each INTERSECT is evaluated left to right, narrowing the result set to rows common to all queries. This is useful when checking membership across three or more data sets simultaneously.