Intersect Operator in MySQL
This article will help you to learn about the INTERSECT operator. Although MySQL does not support INTERSECT and MINUS/EXCEPT, there are other ways around to simulate this functionality.
We will see what INTERSECT is, its benefits, and the various ways to learn how to emulate INTERSECT in MySQL.
Introduction to the INTERSECT Operator in MySQL
INTERSECT is a set operator used to retrieve the common elements from two sets. It is also used to get DISTINCT (or common) records (rows) from two tables.
We can also say that the INTERSECT operator returns identical rows only, which are retrieved as an output of two SELECT statements. Have a look at the following Venn diagram to understand INTERSECTION.
Here, the yellow grid area is the INTERSECTION. The major benefit of INTERSECT is that you can access identical records from many tables.
Although MySQL does not support the INTERSECT operator, we can use other alternatives to get this functionality done.
INTERSECT Operator in MySQL
As already stated, the INTERSECT operator is not available in MySQL. Still, we can simulate this using the INNER JOIN and IN Clause and EXISTS Clause depending on the complexity and requirements of the query.
We are using two tables named order and customer. The fields of the customer table includes customer_id, customer_firstname, customer_lastname, customer_age, and customer_salary.
The order table has order_id, order_date, order_amount, and customer_id (customer_id is a foreign key here). Our customer table and order table look as follows with data.
You can create both tables and insert data using the following sample code.
#create customer table
CREATE TABLE customer(
customer_id INT NOT NULL PRIMARY KEY,
customer_firstname VARCHAR(60) NOT NULL,
customer_lastname VARCHAR(60) NOT NULL,
customer_age INT NOT NULL,
customer_salary INT NOT NULL
);
#create order table
CREATE TABLE order(
order_id INT NOT NULL PRIMARY KEY,
order_date DATETIME NOT NULL,
order_amount INT NOT NULL,
customer_id INT NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
);
#insert into customer table
INSERT INTO customer VALUES
(1, 'Shajeel', 'Daniel', 23, 9000),
(2, 'Nayya', 'Preston', 54, 1500),
(3, 'James', 'Robert', 36, 2500),
(4, 'Jennifer', 'John', 29, 5000),
(5, 'Sarah', 'Paul', 15, 8000),
(6, 'Karen', 'Donald', 40, 3500);
#insert into order table
INSERT INTO order VALUES
(1, '2019-12-03 10:25:30', 500, 2),
(2, '2019-12-10 12:00:30', '1500', 4);
Customer Table:
Order Table:
Emulate INTERSECT via INNER JOIN in MySQL
We want to find the order details (order_id, order_amount, order_date) and customer details (customer_id, customer_firstname, customer_lastname) to know which customer has placed the order on what date.
That means we want to find the customer identical to the customer table and order table. It is also necessary to observe that data comes from two tables; we can use a join named INNER JOIN.
#MySQL Version 8.0.27
SELECT
order.order_id, customer.customer_id, customer.customer_firstname,
customer.customer_lastname,order.order_amount,order.order_date
FROM order
INNER JOIN
customer ON order.customer_id = customer.customer_id;
In the above code it will retrieve the order_id, customer_id, customer_firstname, customer_lastname, order_amount, and order_date for those whose customer_id is same in customer table and order table.
Output:
Emulate INTERSECT via IN Clause in MySQL
Now, we have a different situation. Here we only need to have the customer-related data.
This data includes customer_id, customer_firstname, customer_lastname, and customer_age. And that customer must be present in the order table.
Here, we can use the IN clause to simulate the INTERSECT operation.
#MySQL version 8.0.27
SELECT
customer.customer_id, customer.customer_firstname,
customer.customer_lastname, customer.customer_age
FROM customer
WHERE customer.customer_id IN ( SELECT order.customer_id FROM order);
The subquery will be executed first by collecting all customer_id from the order table above. Then it will SELECT only those customer-related details whose customer_id is present in the result of the subquery.
Output:
Simulate INTERSECT via EXISTS Clause in MySQL
In this scenario, we only want the customer details whose age is less than 45 and must place at least one order. The EXISTS clause is used in the following manner.
The following code will also produce the same output if you omit the EXISTS clause.
SELECT
customer.customer_id, customer.customer_firstname,
customer.customer_lastname, customer.customer_age
FROM customer
WHERE customer.customer_age < 45
AND EXISTS
(SELECT order.customer_id FROM order where order.customer_id = customer.customer_id);
The subquery will be executed first and give all the customer_id from the order table where the customer_id is the same in both tables (order and customer table). Then it will SELECT only those customer-related details whose age is less than 45 and customer_id are present in the subquery result.
Output:
Conclusion
This article has concluded many other alternatives to do one operation.
We also learned different ways to simulate INTERSECT in MySQL. It includes the INNER JOIN, IN Clause, and EXISTS Clause.
We even saw how we could use WHERE and AND while emulating INTERSECT operations in MySQL.
