JIYIK CN >

This article will help you understand INTERSECTthe operator. Although MySQL does not support INTERSECTand MINUS/ EXCEPT, there are other ways to simulate this functionality.

We will see INTERSECTwhat is , its benefits, and learn various ways to emulate in MySQL INTERSECT.

INTERSECTIntroduction to operators in MySQL

INTERSECTIs a set operator that is used to retrieve common elements from two collections. It is also used to get DISTINCT(or common) records (rows) from two tables.

We can also say that INTERSECTthe operator returns only the same rows, which are SELECTretrieved as the output of both statements. Take a look at the following Venn diagram to understand INTERSECTION.

Here, the yellow grid area is INTERSECTION. INTERSECTThe main benefit of is that you can access the same records from many tables.

Although the operator MySQLis not supported INTERSECT, we can use other alternatives to accomplish this functionality.

INTERSECT Operator in MySQL

As mentioned earlier, there is no operator MySQLin . Nevertheless, we can simulate this using the and clauses along with the clause depending on the complexity and requirement of the query.INTERSECTINNER JOININEXISTS

We are using two tables named orderand customer. customerThe fields of the tables include customer_id, customer_firstname, customer_lastname, , customer_ageand customer_salary.

orderThe tables are order_id, order_date, order_amountand customer_id( customer_idwhich is the foreign key here). The data of our customertables and ordertables are as follows.

You can use the following sample code to create two tables and insert data.

#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:

In MySQL through INNER JOINsimulationINTERSECT

We want to find 订单details ( order_id, order_amount, order_date) and 客户details ( customer_id, customer_firstname, customer_lastname) to know which one 客户placed 在什么日期订购.

This means we want to find the same as customertable and table . We also need to observe that the data comes from both tables; we can use a join called .ordercustomerINNER 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 customer_idretrieve those , , , , and .customerordercustomer_idorder_idcustomer_idcustomer_firstnamecustomer_lastnameorder_amountorder_date

Output:

INSimulate via clause in MySQLINTERSECT

Now, we have a different situation. Here, we need only data related to the customer.

The data includes customer_id, customer_firstname, customer_lastnameand customer_age. And the customer must appear in orderthe table.

Here, we can use INthe clause to simulate INTERSECTthe 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);

orderThe subquery will first execute by collecting all the from the above table customer_id. Then it will retrieve the relevant details of only 选择those customers which appear in the subquery result .customer_id

Output:

EXISTSSimulate via clause in MySQLINTERSECT

In this case, we need only the details of those whose age is less than 45and must be at least the next . The clause is used in the following way.订单客户EXISTS

EXISTSThe following code will produce the same output if the clause is omitted .

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 will give orderall the in table customer_idwhere is same in customer_idboth the tables ( orderand ). Then it will present only those customers whose age is less than and in the subquery result.customer选择45customer_id

Output:

in conclusion

This article summarizes many other alternatives for performing an operation.

We also learned different ways to emulate in MySQL INTERSECT. It includes INNER JOIN, INclause, and EXISTSclause.

We even saw how to use and INTERSECTwhen emulating the operation in MySQL .WHEREAND