INTERSECT equivalent in MySQL

Introduction

Sometimes you want to find common data between two lists, but MySQL does not have a direct INTERSECT command. We use other ways to get the same result.

You want to find customers who bought both product A and product B.

You want to find employees who work in both department X and department Y.

You want to find students enrolled in both course 1 and course 2.

Syntax

MySQL

SELECT column_list FROM table1
WHERE column_name IN (SELECT column_name FROM table2);

Use the IN clause with a subquery to find common rows.

Make sure the columns compared have the same data type and meaning.

Examples

Finds ids that appear in both table1 and table2.

MySQL

SELECT id FROM table1
WHERE id IN (SELECT id FROM table2);

Finds names that are both employees and managers.

MySQL

SELECT name FROM employees
WHERE name IN (SELECT name FROM managers);

Sample Program

This finds product ids that are in both products_a and products_b tables.

MySQL

CREATE TABLE products_a (id INT);
CREATE TABLE products_b (id INT);

INSERT INTO products_a VALUES (1), (2), (3);
INSERT INTO products_b VALUES (2), (3), (4);

SELECT id FROM products_a
WHERE id IN (SELECT id FROM products_b);

Important Notes

This method works well for simple cases where you want common rows.

If you want to compare multiple columns, use tuples: WHERE (col1, col2) IN (SELECT col1, col2 FROM ...).

Performance may vary; for large tables, consider indexes on the columns used.

Summary

MySQL does not have INTERSECT, but you can use WHERE ... IN (subquery) to get the same effect.

This finds rows common to both queries or tables.

Make sure the columns compared match in type and meaning.