INTERSECT
SQL · SET Operators
Theory
INTERSECT returns only the rows that appear in the results of both queries, with duplicates removed. SELECT email FROM newsletter INTERSECT SELECT email FROM customers gives the emails of people who are both subscribers and customers. As with UNION, both queries need the same number of columns with compatible types, and rows are compared on all columns.
Unlike a join condition, INTERSECT treats two NULLs as matching, which makes it handy for comparing full rows. PostgreSQL, SQL Server, Oracle, SQLite and Snowflake support it, as do recent MySQL versions; on older MySQL you emulate it with INNER JOIN or EXISTS. INTERSECT ALL, which keeps duplicate counts, exists in PostgreSQL but not in every engine.
Why we use it
Finding overlap between two sets is a common question: customers who bought in both years, users in both lists, rows present in both source and target. INTERSECT states it directly without join logic.
When to use it
Use INTERSECT to find rows common to two result sets with the same shape. If you also need extra columns from either table, use INNER JOIN or EXISTS. For rows in one set but not the other, use EXCEPT / MINUS.
How it works
- Run the first SELECT
- Run the second SELECT
- Take a row from the first result
- Does the same row exist in the second result? Yes: Keep it (once). No: Discard it.
- Return the common rows
Syntax
SELECT columns FROM t1
INTERSECT
SELECT columns FROM t2;Example
SELECT email FROM newsletter
INTERSECT
SELECT email FROM customers;Key points
- Returns rows present in both queries
- Removes duplicates automatically
- Treats NULLs as equal when comparing rows
- Older MySQL lacks it: emulate with INNER JOIN or EXISTS
- Both queries must have the same column count and compatible types