MySQL INTERSECT

The INTERSECT operator is a kind of SET operation in SQL that includes UNION, UNION ALL, MINUS, and INTERSECT. The INTERSECT operator returns the distinct (common) elements in two sets or common records from two or more tables. In other words, it compares the result obtained by two queries and produces unique rows, which are the result returned by both queries.

Syntax

The following are the syntax that illustrates the use of the INTERSECT operator:

NOTE: MySQL does not provide support for the INTERSECT operator. This article shows us how to emulate the INTERSECT query in MySQL using the JOIN and IN clause.

The following are the rules for a query that uses the INTERSECT operator:

• The number and order of columns in all the SELECT statements must be the same.
• The data types of the corresponding columns in both SELECT statements must be the same or convertible.

We can understand the INTERSECT operator with the following visual representation. Here this operator compares the result obtained by two queries and produces only distinct rows from both queries:

Simulation of MySQL INTERSECT Operator

Since MySQL does not provide support for the INTERSECT operator. However, we can use the INNER JOIN and IN clause to emulate this operator.

Let us first creates two tables with the following structure to understand the INTERSECT operator:

Table1 structure and data:

Table2 structure and data:

INTERSECT Operator using DISTINCT and INNER JOIN Clause

The following statement uses the DISTINCT operator and INNER JOIN clause for returning the distinct rows from both tables:

Example:

Output:

After executing the above query, we should get the output as below image:

INTERSECT Operator using IN and Subquery

The following syntax uses the IN and Subquery clause for returning the distinct rows from both tables:

Example:

Output:

After executing the above query, we should get the output as below image: