Unfortunately, MySQL does not support the INTERSECT operator. However, you can simulate the INTERSECT operator.

Let’s create some sample data for the demonstration.

The following statements create tables t1 and t2, and then insert data into both tables.

1
2
3
4
5
6
7
8
9

CREATE TABLE t1 (
    id INT PRIMARY KEY
);
 
CREATE TABLE t2 LIKE t1;
 
INSERT INTO t1(id) VALUES(1),(2),(3);
 
INSERT INTO t2(id) VALUES(2),(3),(4);

The following query returns rows from the t1 table .

1
2

SELECT id
FROM t1;

1
2
3
4
5

id
----
1
2
3

The following query returns the rows from the t2 table:

1
2

SELECT id
FROM t2;

1
2
3
4
5

id
---
2
3
4

Simulate MySQL INTERSECT operator using DISTINCT operator and INNER JOIN clause.

The following statement uses DISTINCT operator and INNER JOIN clause to return the distinct rows in both tables:

1
2
3
4

SELECT DISTINCT 
   id 
FROM t1
   INNER JOIN t2 USING(id);

1
2
3
4

id
----
2
3

How it works.

1. The INNER JOIN clause returns rows from both left and right tables.
2. The DISTINCT operator removes the duplicate rows.

Simulate MySQL INTERSECT operator using IN operator and subquery

The following statement uses the IN operator and a subquery to return the intersection of the two result sets.

1
2
3
4
5
6
7
8
9

SELECT DISTINCT
    id
FROM
    t1
WHERE
    id IN (SELECT 
            id
        FROM
            t2);

1
2
3
4

id
----
2
3

How it works.

1. The subquery returns the first result set.
2. The outer query uses the IN operator to select only values that are in the first result set. The DISTINCT operator ensures that only distinct values are selected.