⚖️ 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, and JOIN

---

✅ 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 JOIN or IN clause with DISTINCT

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 IN as 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 :