INTERSECT – Get What’s Common Between Two Queries
(because sometimes, you only want the stuff that shows up in both lists)
🔧 Quick Syntax
SELECT column1, column2
FROM table1
INTERSECT
SELECT column1, column2
FROM table2;
This tells SQL:
“Give me the rows that are in both result sets — only the overlapping ones.”
Note: INTERSECT isn’t supported in MySQL — it works in databases like PostgreSQL, SQL Server, Oracle, and SQLite.
Context: Suppose You Have Two Customer Lists
Imagine you run a business with two different product lines, and you want to find customers who bought from both.
Table: product_a_customers
| customer_name |
|---|
| Alice |
| Bob |
| Carol |
Table: product_b_customers
| customer_name |
|---|
| Alice |
| Dave |
| Carol |
Suppose You Need To…
…find customers who bought from both products — in other words, customers who exist in both tables.
SQL Using INTERSECT
SELECT customer_name
FROM product_a_customers
INTERSECT
SELECT customer_name
FROM product_b_customers;
Output:
| customer_name |
|---|
| Alice |
| Carol |
That’s what INTERSECT gives you:
Only the customers who appear in both result sets.
But Wait… Why Not Use JOIN Instead?
Good question! A JOIN works between tables, based on keys.
INTERSECT works between queries — think of it more like set logic in math:
- UNION = everything from both
- INTERSECT = only what they share
- EXCEPT = what’s in one, but not the other
Also: with INTERSECT, no duplicates are returned — even if duplicates exist in both sides.
Recap
INTERSECTshows only rows that appear in both queries- It automatically removes duplicates
- Useful when you want the common ground between two lists
- Not supported in MySQL, but works in PostgreSQL, SQL Server, Oracle, SQLite
Beginner Tips & Gotchas
| Confusion | Clarification |
|---|---|
| Can I INTERSECT tables directly? | No — it’s between SELECT queries |
| Do column names need to match? | No, but the number and type of columns must |
| Will I get duplicates in the result? | Nope, they’re removed automatically |
| What if I’m using MySQL? | You’ll need to use a JOIN with filtering instead |