SQL INTERSECT – Return Common Rows Between SELECT Queries
Introduction – What is SQL INTERSECT?
The INTERSECT operator in SQL is used to return only the rows that appear in both result sets of two SELECT queries. It performs a set operation that retains common rows only, similar to set intersections in mathematics.
In this guide, you’ll learn:
- Syntax and structure of
INTERSECT - Use cases for identifying overlaps
- Differences from
UNION,EXCEPT, andJOIN
1. INTERSECT Syntax
SELECT column1, column2 FROM table1
INTERSECT
SELECT column1, column2 FROM table2;
Returns only rows that exist in both SELECT queries.
2. INTERSECT Rules
- Each query must return the same number of columns
- Columns must be of compatible data types
- Duplicates are automatically removed (like
UNION) - Final result uses column names from the first query
3. Example – Common Customers in Two Tables
SELECT name, email FROM website_signups
INTERSECT
SELECT name, email FROM store_buyers;
Returns users who signed up and made a purchase.
4. INTERSECT vs JOIN
| Operation | Behavior |
|---|---|
| JOIN | Combines data from multiple tables |
| INTERSECT | Returns only identical rows across queries |
| INTERSECT is not used to combine columns—only to find matching rows. |
5. INTERSECT Not Supported in MySQL
- MySQL does not support INTERSECT directly.
- Workaround using
INNER JOINorINclause withDISTINCT
SELECT name, email FROM website_signups
WHERE (name, email) IN (
SELECT name, email FROM store_buyers
);
Best Practices
| Recommended | Avoid This |
|---|---|
| Use for intersection of identical rows | Using INTERSECT on mismatched columns |
| Confirm column order and type | Expecting duplicates without ALL |
| Use JOIN if you need combined columns | Comparing NULLs without handling |
Summary – Recap & Next Steps
INTERSECT is useful for finding overlaps between datasets without needing a JOIN. It gives you clean, deduplicated results shared between queries.
Key Takeaways:
- INTERSECT returns only common rows from both queries
- Output is deduplicated
- MySQL lacks native support—use JOIN or
INas workarounds
Real-World Relevance:
Used in lead tracking, user overlap analysis, de-duping, fraud detection, and audit logic.
FAQ – SQL INTERSECT
What does SQL INTERSECT do?
It returns only rows that appear in both queries.
Does INTERSECT remove duplicates?
Yes. It behaves like UNION, retaining unique rows only.
Can I use INTERSECT in MySQL?
No. Use JOIN or IN (...) workaround instead.
What is the difference between INTERSECT and INNER JOIN?
JOIN merges columns; INTERSECT finds identical rows.
Share Now :
