INTERSECT Operator in MySQL

Introduction
Finding common records between datasets requires set intersection logic.
MySQL lacks native INTERSECT but provides efficient alternatives.
It is widely used for:

  • Finding common elements between tables
  • Data validation across sources
  • Overlap analysis

What is the INTERSECT Operation
INTERSECT returns rows common to both queries (set intersection).
MySQL equivalent: INNER JOIN, EXISTS, or IN subquery.

Basic INNER JOIN Alternative

sql

SELECT DISTINCT s.name, s.city 

FROM Students s 

INNER JOIN Graduates g ON s.name = g.name AND s.city = g.city; 

Common students in both tables.

Example

sql

-- Find students who also appear in the toppers list 

SELECT DISTINCT s.id, s.name 

FROM Students s 

INNER JOIN Toppers t ON s.id = t.student_id; 

IN Subquery Alternative
Simpler syntax for a single column.

sql

SELECT name FROM Students 

WHERE name IN (SELECT name FROM Graduates); 

EXISTS Alternative
Best for complex matching.

sql

SELECT name FROM Students s 

WHERE EXISTS ( 

    SELECT 1 FROM Graduates g WHERE g.name = s.name 

); 

INTERSECT Multiple Columns
Match on composite keys.

sql

SELECT s.name, s.city 

FROM Students s 

INNER JOIN Graduates g ON s.name = g.name AND s.city = g.city; 

INTERSECT with ORDER BY
Sort common results.

sql

SELECT s.name FROM Students s 

INNER JOIN Graduates g ON s.name = g.name 

ORDER BY s.name; 

INTERSECT with WHERE
Filter before intersection.

sql

SELECT s.name FROM Students s 

INNER JOIN Graduates g ON s.name = g.name 

WHERE s.marks > 90; 

INTERSECT with GROUP BY
Aggregate common data.

sql

SELECT s.city, COUNT(*) 

FROM Students s 

INNER JOIN Graduates g ON s.city = g.city 

GROUP BY s.city; 

Performance Comparison

Method

Speed

Readability

Memory

INNER JOIN

Fastest

High

Low

EXISTS

Fast

Medium

Low

IN

Slowest

High

High

INTERSECT with Subqueries
Complex intersection logic.

sql

SELECT product FROM Inventory 

WHERE product IN ( 

    SELECT product FROM SoldItems 

    INTERSECT 

    SELECT product FROM PopularItems 

);  -- Use JOIN for this in MySQL 

Execution Order with INTERSECT
FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

Performance Considerations

  • INNER JOIN is fastest for INTERSECT
  • Index join columns always
  • EXISTS is good for complex conditions
  • IN subquery is slow for large sets

Important Notes

  • INNER JOIN with DISTINCT = INTERSECT
  • All methods need the same column structure
  • IN subquery fails with NULLs
  • Multiple matches create duplicate rows
  • MySQL 8.0+ may support native INTERSECT

Example Scenario
Find products in both inventory AND sales:

sql

SELECT DISTINCT p.product_name, p.category 

FROM Products p 

INNER JOIN Sales s ON p.product_id = s.product_id 

WHERE p.stock > 0 

ORDER BY p.category, p.product_name 

LIMIT 100;

Common Mistakes

  • INNER JOIN creates duplicate rows
  • IN subquery with NULL values
  • Missing indexes on join columns
  • Wrong column matching logic
  • Expecting native INTERSECT syntax

Key Points to Remember

  • Use INNER JOIN for INTERSECT operations
  • EXISTS works well for complex matching
  • In a subquery, slowest (avoid for large data)
  • Index join columns for performance
  • DISTINCT needed with INNER JOIN
  • Same data types required for matching