Emulating INTERSECT in MySQL0%

Emulating INTERSECT in MySQL

Intermediate12 min read•Updated: 2026-09-12

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

 Dataset A: Customers who bought Laptops:   { Aarav, Priya, Rohan }
 Dataset B: Customers who bought Monitors:  { Priya, Rohan, Kabir }
3 
 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:

1-- Find customer IDs who placed orders in BOTH 2025 and 2026:
2SELECT customer_id FROM orders WHERE YEAR(order_date) = 2025
3INTERSECT
4SELECT 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:

1-- Emulating INTERSECT using an INNER JOIN on subqueries:
2SELECT DISTINCT a.customer_id
3FROM (
4    SELECT customer_id FROM orders WHERE YEAR(order_date) = 2025
5) a
6INNER JOIN (
7    SELECT customer_id FROM orders WHERE YEAR(order_date) = 2026
8) 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:

1-- Find employees who are certified in Python AND certified in AWS:
2SELECT DISTINCT employee_id
3FROM employee_certifications
4WHERE certification_name = 'Python'
5  AND employee_id IN (
6      SELECT employee_id 
7      FROM employee_certifications 
8      WHERE certification_name = 'AWS'
9  );

5. Emulating INTERSECT Using `EXISTS`

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

1SELECT DISTINCT o1.customer_id
2FROM orders o1
3WHERE YEAR(o1.order_date) = 2025
4  AND EXISTS (
5      SELECT 1 
6      FROM orders o2 
7      WHERE o2.customer_id = o1.customer_id 
8        AND YEAR(o2.order_date) = 2026
9  );

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.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What does the INTERSECT operator return in relational database SQL?

Question 2

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

Question 3

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

Question 4

How does standard INTERSECT treat two NULL values during comparison?

Question 5

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

Next Lesson

Emulating EXCEPT / MINUS in MySQL

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

Related Lessons

Previous Lesson	Next Lesson
Combining Results with UNION & UNION ALL	Emulating EXCEPT / MINUS in MySQL

Practice Quiz

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

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Emulating EXCEPT / MINUS in MySQL

Emulating INTERSECT in MySQL

Intermediate12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Emulating INTERSECT in MySQL: Set Intersections Made Easy

1. The Mathematical Concept of INTERSECT

 Dataset A: Customers who bought Laptops:   { Aarav, Priya, Rohan }
 Dataset B: Customers who bought Monitors:  { Priya, Rohan, Kabir }
3 
 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:

1-- Find customer IDs who placed orders in BOTH 2025 and 2026:
2SELECT customer_id FROM orders WHERE YEAR(order_date) = 2025
3INTERSECT
4SELECT 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:

1-- Emulating INTERSECT using an INNER JOIN on subqueries:
2SELECT DISTINCT a.customer_id
3FROM (
4    SELECT customer_id FROM orders WHERE YEAR(order_date) = 2025
5) a
6INNER JOIN (
7    SELECT customer_id FROM orders WHERE YEAR(order_date) = 2026
8) 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:

1-- Find employees who are certified in Python AND certified in AWS:
2SELECT DISTINCT employee_id
3FROM employee_certifications
4WHERE certification_name = 'Python'
5  AND employee_id IN (
6      SELECT employee_id 
7      FROM employee_certifications 
8      WHERE certification_name = 'AWS'
9  );

5. Emulating INTERSECT Using `EXISTS`

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

1SELECT DISTINCT o1.customer_id
2FROM orders o1
3WHERE YEAR(o1.order_date) = 2025
4  AND EXISTS (
5      SELECT 1 
6      FROM orders o2 
7      WHERE o2.customer_id = o1.customer_id 
8        AND YEAR(o2.order_date) = 2026
9  );

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.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What does the INTERSECT operator return in relational database SQL?

Question 2

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

Question 3

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

Question 4

How does standard INTERSECT treat two NULL values during comparison?

Question 5

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

Next Lesson

Emulating EXCEPT / MINUS in MySQL

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

Related Lessons

Previous Lesson	Next Lesson
Combining Results with UNION & UNION ALL	Emulating EXCEPT / MINUS in MySQL

Practice Quiz

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

Previous Next

Emulating INTERSECT in MySQL: Set Intersections Made Easy

1. The Mathematical Concept of INTERSECT

2. Native INTERSECT (MySQL 8.0.31+)

Rules for INTERSECT:

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

4. Emulating INTERSECT Using the IN Operator

5. Emulating INTERSECT Using EXISTS

6. Best Practices & Common Pitfalls

Multiple Choice Questions

Emulating EXCEPT / MINUS in MySQL

Related Lessons

Practice Quiz

Loading Page Content...

Emulating INTERSECT in MySQL: Set Intersections Made Easy

1. The Mathematical Concept of INTERSECT

2. Native INTERSECT (MySQL 8.0.31+)

Rules for INTERSECT:

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

4. Emulating INTERSECT Using the IN Operator

5. Emulating INTERSECT Using EXISTS

6. Best Practices & Common Pitfalls

Multiple Choice Questions

Emulating EXCEPT / MINUS in MySQL

Related Lessons

Practice Quiz

2. Native `INTERSECT` (MySQL 8.0.31+)

Rules for `INTERSECT`:

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

4. Emulating INTERSECT Using the `IN` Operator

5. Emulating INTERSECT Using `EXISTS`

2. Native `INTERSECT` (MySQL 8.0.31+)

Rules for `INTERSECT`:

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

4. Emulating INTERSECT Using the `IN` Operator

5. Emulating INTERSECT Using `EXISTS`