SQL INTERSECT

What is the SQL INTERSECT operator?

Returns the distinct rows that appear in the result sets of two queries.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL INTERSECT: PostgreSQL, SQL Server, Oracle, SQLite, Snowflake, DB2, MariaDB 10.3+, MySQL 8.0.31+. Not available in older MySQL or in some NoSQL-based engines.

SQL INTERSECT Full Explanation

INTERSECT is a set-operation operator that produces the common rows from two SELECT statements. Each input query must return the same number of columns, and each column pair must be of compatible data types. By default the operator performs INTERSECT DISTINCT, meaning duplicate rows are eliminated after the intersection is computed. Some systems also allow INTERSECT ALL to keep duplicates. The column names of the first SELECT are preserved in the final result.The comparison treats NULL as a comparable value, so a row containing NULLs is returned if an identical row (including NULL in the same positions) exists in both result sets. ORDER BY can be applied only once, to the combined result, and must reference output column positions or names. INTERSECT is evaluated after FROM, WHERE, GROUP BY, and HAVING but before ORDER BY and LIMIT clauses. Because it forces a deduplication step, it can be more expensive than INNER JOIN on primary keys when large row counts are involved.

SQL INTERSECT Syntax

SELECT column_list
FROM table_a
[WHERE condition]
INTERSECT [ALL]
SELECT column_list
FROM table_b
[WHERE condition];

SQL INTERSECT Parameters

Example Queries Using SQL INTERSECT

-- Find users who are both customers and newsletter subscribers
SELECT email FROM customers
INTERSECT
SELECT email FROM newsletter_subscribers;

-- Show product IDs that sold in both 2022 and 2023
SELECT product_id FROM sales WHERE EXTRACT(year FROM sold_at) = 2022
INTERSECT
SELECT product_id FROM sales WHERE EXTRACT(year FROM sold_at) = 2023;

-- Preserve duplicates (if supported)
SELECT user_id FROM logins_2024
INTERSECT ALL
SELECT user_id FROM logins_2023;

Expected Output Using SQL INTERSECT

The statement returns only the rows that exist in the result sets of both SELECT queries
Duplicate rows are removed unless INTERSECT ALL is used
Column names come from the first SELECT

Use Cases with SQL INTERSECT

Identify overlapping members of two lists, such as customers who are also employees
Compare yearly or monthly snapshots to find entities present in multiple periods
Validate data migrations by intersecting IDs from source and target tables
Combine complex queries where INNER JOIN would be less readable

Common Mistakes with SQL INTERSECT

Mismatching column counts or incompatible data types between the two SELECTs
Expecting duplicates to be retained without using INTERSECT ALL
Applying ORDER BY to each subquery instead of the final result
Assuming MySQL versions prior to 8.0.31 support INTERSECT

First Introduced In

SQL:1999

Frequently Asked Questions

What is the difference between INTERSECT and UNION?

UNION returns all distinct rows that appear in either query, whereas INTERSECT returns only the rows present in both queries.

Does INTERSECT work with NULL values?

Yes. A row containing NULLs is returned if an identical row with NULL in the same columns exists in both result sets.

Can I intersect more than two queries?

Yes. Chain multiple INTERSECT operators: `query1 INTERSECT query2 INTERSECT query3`, keeping column counts and data types aligned.

How do I keep duplicates when intersecting?

Use the optional `INTERSECT ALL` if your database supports it. Otherwise, duplicates are removed by default.