INTERSECT
Find rows that appear in both query results
Advanced 8 minINTERSECTset operations
Common Rows Only
INTERSECT returns only the rows that appear in both result sets — the set-theory intersection. It answers the question "what do these two queries have in common?" Here's what to know:
Automatic deduplication — Results are deduplicated automatically even without DISTINCT. If a value appears in both queries, it shows up exactly once in the final output regardless of how many times it appears in either source.
Same column requirements — Both queries must match in column count and have compatible data types — the same rule as UNION. Mismatched column counts will produce a syntax error.
Cleaner than INNER JOIN — When you just need to find common values between two lists or result sets, INTERSECT is more readable and more concise than writing an equivalent INNER JOIN with deduplication.
Knowledge Canvas
How INTERSECT Works
Find rows common to both queries
- Returns only rows appearing in BOTH result sets
- Automatically deduplicates the output
- Same column requirements as UNION
- Cleaner than INNER JOIN for simple overlap checks
INTERSECT vs INNER JOIN
Two ways to find overlap
INTERSECT: set operation, auto-dedup
INNER JOIN: row-level matching
Compares entire rows
Matches on specific conditions
Simpler for value overlap
More flexible for column combinations
When to Use
INTERSECT scenarios
- Values in two different lists
- Data consistency validation
- Finding shared attributes between groups
- Verifying migration: old vs new system data
INTERSECT Rules
Quick reference
- Both queries: same column count and types
- NULLs are treated as equal
- Auto-deduplicates (no INTERSECT ALL in most databases)
- Not supported in all databases (MySQL added it in 8.0)
Syntax
Syntax Template
1SELECT
2 cols
3FROM table_a
4INTERSECT
5SELECT
6 cols
7FROM table_b;
INTERSECTOnly rows present in BOTH result sets Sample Data
customers
12 rows
| country | city |
|---|---|
| India | Mumbai |
| Singapore | Singapore |
| UK | London |
| Spain | Madrid |
| Japan | Tokyo |
| India | Delhi |
| USA | New York |
| China | Shanghai |
| Australia | Sydney |
| UAE | Dubai |
| Germany | Berlin |
| India | Bangalore |
orders
12 rows
| id | customer_id | status | total |
|---|---|---|---|
| 5001 | C1001 | delivered | 59.98 |
| 5002 | C1002 | delivered | 449 |
| 5003 | C1001 | shipped | 12.5 |
| 5004 | C1003 | delivered | 89.99 |
| 5005 | C1006 | pending | 245 |
| 5006 | C1005 | delivered | 159 |
| 5007 | C1004 | shipped | 45.5 |
| 5008 | C1009 | cancelled | 79 |
| 5009 | C1012 | delivered | 425 |
| 5010 | C1007 | pending | 18.99 |
| 5011 | C1008 | delivered | 134 |
| 5012 | C1010 | cancelled | 65.5 |
reviews
12 rows
| product_id | customer_id | rating |
|---|---|---|
| P101 | C1001 | 5 |
| P101 | C1003 | 4 |
| P101 | C1005 | 3 |
| P102 | C1002 | 5 |
| P102 | C1006 | 4 |
| P103 | C1004 | 4 |
| P104 | C1007 | 5 |
| P105 | C1009 | 5 |
| P105 | C1012 | 4 |
| P107 | C1008 | 3 |
| P108 | C1011 | 5 |
| P109 | C1001 | 5 |
Worked Example
Find values that appear as both a country and a city in the customers table.
SQL
1SELECT DISTINCT
2 country
3FROM customers
4INTERSECT
5SELECT DISTINCT
6 city
7FROM customers;
Singapore appears as both a country ('Singapore') and a city ('Singapore'). INTERSECT finds this overlap.
Output
1 row
| country |
|---|
| Singapore |
Key Concepts
1INTERSECT returns only rows present in BOTH result sets
2Auto-deduplicates — no need for DISTINCT
3Simpler than INNER JOIN for pure overlap checks
4Both queries must match in column count and types
Pro Tip
INTERSECT answers "what exists in both A and B?" — useful for finding overlap, validating consistency, and set comparisons.
When to Use
Values in two different lists, verifying data overlap between systems, identifying shared attributes.
Find customer IDs that have BOTH placed an order AND left a review. Use INTERSECT.
Your Query