INTERSECT in MySQL
In this tutorial, we are going to see the INTERSECT operator and show you how to simulate the MySQL INTERSECT operator.
You should know that MySQL does not support the INTERSECT operator. This tutorial explains how to simulate the INTERSECT operator in MySQL using IN clause or using Join.
INTERSECT operator
First, let’s explain what an INTERSECT query is. An INTERSECT query returns the intersection of 2 or more sets. If a record exists in both data sets, it will be included in the intersection results. However, if a record exists in one data set and not in the other, it will be omitted from the intersection results.

The INTERSECT query returns the records in the red shaded area (B and C).
Syntax:
SELECT column1, column2, ... column_n FROM table1 [WHERE conditions] INTERSECT SELECT column1, column2, ... column_n FROM table2 [WHERE conditions];
Example using the IN clause:
Let’s look at an example, how to simulate an INTERSECT query in MySQL having a field with the same data type.
If your DBMS supports the INTERSECT operator (which MySQL doesn’t), then you would have used the INTERSECT operator to return the ClientId values that are in the “Clients” and “Orders” table.
SELECT ClientId FROM Orders INTERSECT SELECT ClientId FROM Clients;
Since you cannot use the INTERSECT operator in MySQL, you will use the IN operator to simulate the INTERSECT operator as follows:
SELECT Orders.ClientId FROM Orders WHERE Orders.ClientId IN (SELECT Clients.ClientId FROM Clients);
Output:
+----------------+ | ClientID | +----------------+ | 1 | | 1 | | 2 | | 3 | +----------------+
In this simple example, we have used the IN operator to return all the ClientId values present in the “Clients” and “Orders” tables.
Example, using the INTERSECT operator with a (WHERE) condition:
SELECT ClientId FROM Orders WHERE Total > 50 INTERSECT SELECT ClientId FROM Clients;
Here’s how to simulate the INTERSECT query using the IN operator with a (WHERE) condition:
SELECT Orders.ClientId FROM Orders WHERE Orders.Totale > 50 AND Orders.ClientId IN ( SELECT Clients.ClientId FROM Clients );
Example using join
The following query uses the DISTINCT operator and the INNER JOIN clause to return distinct rows from the two tables:
SELECT DISTINCT ClientID FROM Clients INNER JOIN Orders USING(ClientID);
Output:
+----------------+ | ClientID | +----------------+ | 1 | | 2 | | 3 | +----------------+