MySQL INTERSECT – Find Similar Rows in Multiple Tables

In this tutorial, we will see what is the INTERSECT operator and what is its use. It is one of the three set operators in SQL standard- UNION, INTERSECT and MINUS. We will see two examples to emulate the intersect in MySQL.

Note that, MySQL does not support the INTERSECT operator. In this tutorial, we will use the JOIN clause to emulate the interect operation.

What is the INTERSECT Operator

The INTERSECT operator is very similar to the intersection operation in real life. To find the similar factors between the two terms, we use the intersection operation.

Similarly, the INTERSECT operator returns the number of records that are in the first table as well as in the second table.

In more detailed words, the INTERSECT operator will compare the results of two queries and returns the rows from the result of the first query that are present in the result of the second query.

Syntax of the INTERSECT Operator

Following is the syntax of the INTERSECT operator in SQL.

SELECT col_names 
FROM table_name1
INTERSECT
SELECT col_names 
FROM table_name2;Code language: SQL (Structured Query Language) (sql)

Note that,

The column number of the first table and second table must be the same and of compatible type with each other. Else, it will give an error.

Also, the above syntax is of the SQL and not of MySQL as MySQL does not support the INTERSECT operator.

INTERSECT Operation Using JOIN in MySQL

Here, we will see the example of the INTERSECT operator using a graphical representation first.

Let’s suppose we have two tables, id1 and id2 which consist of only the ids of a few members. Note that, few ids are present in both tables.

The above table looks something like this in vein diagram representation-

Now let’s create tables and insert values into them.

Note that, MySQL does not support the INTERSECT operator. However, we can perform the same operation using the JOIN clause.

CREATE TABLE id1(
id INT
);
 
CREATE TABLE id2)
id INT
);
 
INSERT INTO id1 VALUES(1),(2),(3),(4);
 
INSERT INTO id2 VALUES(3),(4),(5),(6);Code language: SQL (Structured Query Language) (sql)

SELECT * FROM id1;
SELECT * FROM id12;Code language: SQL (Structured Query Language) (sql)

Finding intersect using INNER JOIN

SELECT id1.id FROM id1
INNER JOIN id2
ON id1.id=id2.id;Code language: SQL (Structured Query Language) (sql)

Here, we have used the inner join to find the intersection of two table records.

The above query can also be written as-

SELECT id FROM id1 INNER JOIN id2 USING(id);Code language: SQL (Structured Query Language) (sql)

Let’s suppose there are duplicate rows in the first table, we can escape them using the DISTINCT clause.

So, the query becomes:

SELECT DISTINCT id FROM id1 INNER JOIN id2 USING(id);Code language: SQL (Structured Query Language) (sql)

Finding intersect using IN and Subquery

This is another way to find the intersection of two tables.

SELECT id FROM id1
WHERE id IN (SELECT id FROM id2);Code language: SQL (Structured Query Language) (sql)

Here, we will find the records of table id1 which have the same id as in table id2.

Conclusion

In this tutorial, we have learned what is the INTERSECT operator and how it works. We have seen that MySQL doesn’t support the INTERSECT operator. However, we can emulate the intersect operator using the INNER JOIN clause as well as IN operator.