Emulating INTERSECT in MySQL0%
Emulating EXCEPT / MINUS in MySQL

Emulating INTERSECT in MySQL

Intermediate12 min read•Updated: 2026-09-12
Study Materials

Emulating INTERSECT in MySQL: Set Intersections Made Easy

In mathematical set theory, the Intersection of two sets represents the elements that exist in both Set A and Set B simultaneously. In standard ANSI SQL, this is expressed using the INTERSECT operator.

While modern MySQL 8.0.31+ natively added INTERSECT support, millions of production applications run on earlier MySQL versions or need alternative, highly optimized relational equivalents. In this tutorial, you will master both native INTERSECT and how to emulate it using INNER JOIN and IN.

1. The Mathematical Concept of INTERSECT

Output
Dataset A: Customers who bought Laptops: { Aarav, Priya, Rohan }
Dataset B: Customers who bought Monitors: { Priya, Rohan, Kabir }
 
INTERSECT (Common Elements in BOTH sets):
===> { Priya, Rohan }

2. Native INTERSECT (MySQL 8.0.31+)

In modern MySQL (version 8.0.31 and newer), you can write INTERSECT directly:

SQL
-- Find customer IDs who placed orders in BOTH 2025 and 2026:
SELECT customer_id FROM orders WHERE YEAR(order_date) = 2025
INTERSECT
SELECT customer_id FROM orders WHERE YEAR(order_date) = 2026;

Rules for INTERSECT:

  • Automatically removes duplicate values from the output.
  • If duplicate retention is needed, use INTERSECT ALL.

3. Emulating INTERSECT Using INNER JOIN (High-Performance)

In any version of MySQL, the most performant and idiomatic way to calculate an intersection is using an INNER JOIN with DISTINCT:

SQL
-- Emulating INTERSECT using an INNER JOIN on subqueries:
SELECT DISTINCT a.customer_id
FROM (
SELECT customer_id FROM orders WHERE YEAR(order_date) = 2025
) a
INNER JOIN (
SELECT customer_id FROM orders WHERE YEAR(order_date) = 2026
) b ON a.customer_id = b.customer_id;

4. Emulating INTERSECT Using the IN Operator

You can also express set intersection cleanly using an IN subquery:

SQL
-- Find employees who are certified in Python AND certified in AWS:
SELECT DISTINCT employee_id
FROM employee_certifications
WHERE certification_name = 'Python'
AND employee_id IN (
SELECT employee_id
FROM employee_certifications
WHERE certification_name = 'AWS'
);

5. Emulating INTERSECT Using EXISTS

For large tables with indexes, the EXISTS semi-join pattern is often the fastest execution strategy:

SQL
SELECT DISTINCT o1.customer_id
FROM orders o1
WHERE YEAR(o1.order_date) = 2025
AND EXISTS (
SELECT 1
FROM orders o2
WHERE o2.customer_id = o1.customer_id
AND YEAR(o2.order_date) = 2026
);

6. Best Practices & Common Pitfalls

  • Beware of NULL Handling: In standard SQL INTERSECT, two NULL values are considered equal to each other! In contrast, when emulating with INNER JOIN on a.id = b.id, NULL = NULL evaluates to UNKNOWN, meaning rows with NULL keys will not match. Use <=> (spaceship operator) if your key columns are nullable.
  • Deduplication Overhead: Remember that native INTERSECT automatically performs deduplication. If your datasets are already distinct, an INNER JOIN is often faster.

Multiple Choice Questions

1. What does the INTERSECT operator return in relational database SQL?

A. All rows from both queries combined B. Only the rows that exist in both queries simultaneously C. Rows in the first query that are absent from the second query D. A Cartesian product Answer: B Explanation: INTERSECT computes the mathematical set intersection, returning only records present in both participating result sets.

2. Starting with which version did MySQL introduce native support for the INTERSECT operator?

A. MySQL 5.7 B. MySQL 8.0.31 C. MySQL 8.0.1 D. MySQL 5.1 Answer: B Explanation: MySQL officially added native support for INTERSECT and EXCEPT in version 8.0.31.

3. Which relational operator can be used in older MySQL versions to emulate an INTERSECT between two result sets?

A. LEFT JOIN B. INNER JOIN with DISTINCT C. CROSS JOIN D. FULL OUTER JOIN Answer: B Explanation: An INNER JOIN combined with DISTINCT matches identical keys between two datasets and removes duplicates, accurately emulating INTERSECT.

4. How does standard INTERSECT treat two NULL values during comparison?

A. It throws an error B. It treats them as identical matching values C. It treats them as unequal D. It converts them to zero Answer: B Explanation: In SQL set operations (INTERSECT and UNION), two NULL values are treated as matching distinct values, unlike in scalar comparisons where NULL = NULL is UNKNOWN.

5. Which clause tests for the presence of matching records in a correlated subquery to emulate an intersection?

A. EXISTS B. LIKE C. BETWEEN D. LIMIT Answer: A Explanation: The EXISTS semi-join construct efficiently verifies whether corresponding records exist in a secondary query, emulating set intersection.

Next Lesson

Emulating EXCEPT / MINUS in MySQL

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous LessonNext Lesson
Combining Results with UNION & UNION ALLEmulating EXCEPT / MINUS in MySQL

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

PreviousNext
PrevNext