🔄 SQL INTERSECT – Find Common Rows Between Two Queries

The INTERSECT operator in SQL is used to return only the rows that appear in both SELECT queries.

It’s a powerful tool for identifying overlapping data, such as customers in two datasets, products sold online and offline, or users active on multiple platforms.

📘 What is SQL INTERSECT?

INTERSECT is a set operator in SQL that compares the result sets of two SELECT statements and returns only the distinct rows that are present in both.

It’s like the mathematical intersection: only the common elements survive.

🧾 SQL INTERSECT Syntax

SELECT column1, column2 FROM table1
INTERSECT
SELECT column1, column2 FROM table2;

✅ Rules:

  • Both SELECT statements must return the same number of columns
  • The data types of the corresponding columns must be compatible
  • The result will exclude duplicates automatically

✅ Example: Find Employees in Both Departments

Let’s say you have two tables:

project_a_employees

namedepartment
AliceIT
BobHR
CharlieSales

project_b_employees

namedepartment
AliceIT
DavidMarketing
CharlieSales

To find employees who worked on both projects:

SELECT name, department FROM project_a_employees
INTERSECT
SELECT name, department FROM project_b_employees;

🧾 Result:

namedepartment
AliceIT
CharlieSales

🔁 INTERSECT vs INTERSECT ALL

Unlike some databases (like Oracle), many systems do not support INTERSECT ALL. But where available:

  • INTERSECT: returns distinct common rows
  • INTERSECT ALL: returns all matching rows, including duplicates

🧠 Use Cases for SQL INTERSECT

  • Find customers who purchased from two different stores
  • Identify students enrolled in multiple courses
  • Get users who completed both signup and payment flows
  • Detect employees present in both HR and payroll systems

⚠️ Common Mistakes to Avoid

MistakeSolution
Mismatched column countsEnsure both queries return the same number of columns
Incompatible data typesUse CAST() to align column types if necessary
Expecting duplicate rowsUse INTERSECT ALL (if supported) to retain them
Ignoring column namesOutput takes column names from the first SELECT

🧮 Real-Life Example: Loyal Customers

Imagine a business wants to find customers who made purchases in both 2023 and 2024.

SELECT customer_id FROM sales_2023
INTERSECT
SELECT customer_id FROM sales_2024;

This shows loyal customers who returned to buy again.

🔍 SQL INTERSECT vs UNION vs EXCEPT

OperatorDescription
UNIONCombines all rows from both queries (removes duplicates)
UNION ALLCombines all rows including duplicates
INTERSECTReturns only common rows
EXCEPTReturns rows from the first query not in the second

📝 Summary

  • SQL INTERSECT returns rows common to both SELECT queries
  • Removes duplicates automatically
  • Use when comparing lists, detecting overlaps, or finding shared data
  • Ensure column count and data types match between queries
  • Not all databases support INTERSECT ALL, but INTERSECT is widely available