MySQL INTERSECT

MySQL INTERSECT Clause

The INTERSECT operator in SQL is used to return the common records (intersection) from two SELECT statements. It retrieves only the rows that exist in both result sets. Unfortunately, MySQL does not natively support the INTERSECT operator. However, you can achieve the same functionality using alternative approaches.

Workarounds for INTERSECT in MySQL

Since MySQL lacks a native INTERSECT support, you can simulate it using the following methods:

1. Using INNER JOIN
2. Using EXISTS
3. Using a Common Table Expression (CTE) (if MySQL version ≥ 8.0)

1. Using INNER JOIN

You can use an INNER JOIN between two queries to get their intersection.

Syntax

SELECT column1, column2, ...
FROM table1
WHERE condition1
INNER JOIN (
    SELECT column1, column2, ...
    FROM table2
    WHERE condition2
) AS t
ON table1.column1 = t.column1
AND table1.column2 = t.column2;

Example

Find employees present in both department1 and department2.

SELECT employee_id, first_name, last_name
FROM department1
INNER JOIN department2
ON department1.employee_id = department2.employee_id;

2. Using EXISTS

The a EXISTS keyword can also be used to find the intersection of two queries.

Syntax

SELECT column1, column2, ...
FROM table1
WHERE EXISTS (
    SELECT 1
    FROM table2
    WHERE table1.column1 = table2.column1
    AND table1.column2 = table2.column2
);

Example

Find students enrolled in both course_a and course_b.

SELECT student_id, name
FROM course_a
WHERE EXISTS (
    SELECT 1
    FROM course_b
    WHERE course_a.student_id = course_b.student_id
);

3. Using Common Table Expressions (CTE)

For MySQL 8.0 and later, you can use CTEs to find the intersection.

Syntax

WITH first_query AS (
    SELECT column1, column2, ...
    FROM table1
    WHERE condition1
),
second_query AS (
    SELECT column1, column2, ...
    FROM table2
    WHERE condition2
)
SELECT column1, column2, ...
FROM first_query
INNER JOIN second_query
ON first_query.column1 = second_query.column1
AND first_query.column2 = second_query.column2;

Example

Find products listed in both store1 and store2.

WITH store1_products AS (
    SELECT product_id, product_name
    FROM store1
),
store2_products AS (
    SELECT product_id, product_name
    FROM store2
)
SELECT store1_products.product_id, store1_products.product_name
FROM store1_products
INNER JOIN store2_products
ON store1_products.product_id = store2_products.product_id;

4. Using GROUP BY and HAVING

Another method is to combine both result sets with UNION ALL and then filter records that appear in both using GROUP BY and HAVING.

Syntax

SELECT column1, column2, ...
FROM (
    SELECT column1, column2, ...
    FROM table1
    WHERE condition1
    UNION ALL
    SELECT column1, column2, ...
    FROM table2
    WHERE condition2
) AS combined
GROUP BY column1, column2, ...
HAVING COUNT(*) > 1;

Example

Find students in both math_class and science_class.

SELECT student_id, name
FROM (
    SELECT student_id, name
    FROM math_class
    UNION ALL
    SELECT student_id, name
    FROM science_class
) AS combined_classes
GROUP BY student_id, name
HAVING COUNT(*) > 1;

Comparison of Methods

Method	Performance	Use Case
INNER JOIN	Fast for indexed columns	Best for simple intersections.
EXISTS	Good for subqueries	Works well with correlated subqueries.
CTE	Readable, modern	Requires MySQL 8.0 or later.
GROUP BY	Resource-intensive	Useful when combining multiple tables.

Conclusion

While MySQL does not have a built-in INTERSECT operator, the same functionality can be achieved using INNER JOIN, EXISTS, GROUP BY, or CTEs. The best approach depends on your specific use case and the size of your dataset.