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.
- The
INNER JOIN
clause returns rows from both left and right tables. - 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.
- The subquery returns the first result set.
- 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.