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   |
+----------------+

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More