MySQL 中的 INTERSECT 运算符
本文将帮助你了解 INTERSECT
运算符。虽然 MySQL 不支持 INTERSECT
和 MINUS
/EXCEPT
,但还有其他方法可以模拟此功能。
我们将看到 INTERSECT
是什么,它的好处,以及学习如何在 MySQL 中模拟 INTERSECT
的各种方法。
MySQL 中的 INTERSECT
运算符简介
INTERSECT
是一个集合运算符,用于从两个集合中检索公共元素。它还用于从两个表中获取 DISTINCT
(或常见)记录(行)。
我们也可以说 INTERSECT
运算符只返回相同的行,这些行作为两个 SELECT
语句的输出检索。看看下面的维恩图来理解 INTERSECTION
。
在这里,黄色网格区域是 INTERSECTION
。INTERSECT
的主要好处是你可以从许多表中访问相同的记录。
尽管 MySQL
不支持 INTERSECT
运算符,但我们可以使用其他替代方法来完成此功能。
MySQL 中的 INTERSECT 运算符
如前所述,MySQL
中没有 INTERSECT
运算符。尽管如此,我们可以根据查询的复杂性和要求使用 INNER JOIN
和 IN
子句以及 EXISTS
子句来模拟这一点。
我们正在使用两个名为 order
和 customer
的表。customer
表的字段包括 customer_id
、customer_firstname
、customer_lastname
、customer_age
和 customer_salary
。
order
表有 order_id
、order_date
、order_amount
和 customer_id
(customer_id
是这里的外键)。我们的 customer
表和 order
表的数据如下所示。
你可以使用以下示例代码创建两个表并插入数据。
#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);
客户表:
订单表:
在 MySQL 中通过 INNER JOIN
模拟 INTERSECT
我们想找到订单
详细信息(order_id
、order_amount
、order_date
)和客户
详细信息(customer_id
、customer_firstname
、customer_lastname
),以了解哪个客户
放置了在什么日期订购
。
这意味着我们要找到与 customer
表和 order
表相同的 customer
。还需要观察数据来自两个表;我们可以使用名为 INNER JOIN
的 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;
在上面的代码中,它将检索 customer_id
在 customer
表和 order
表中的 customer_id
相同的那些 order_id
、customer_id
、customer_firstname
、customer_lastname
、order_amount
和 order_date
.
输出:
通过 MySQL 中的 IN
子句模拟 INTERSECT
现在,我们有不同的情况。在这里,我们只需要与客户相关的数据。
该数据包括 customer_id
、customer_firstname
、customer_lastname
和 customer_age
。并且该客户必须出现在 order
表中。
在这里,我们可以使用 IN
子句来模拟 INTERSECT
操作。
#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);
子查询将首先通过从上面的 order
表中收集所有 customer_id
来执行。然后它将仅选择
那些在子查询结果中出现 customer_id
的客户相关详细信息。
输出:
通过 MySQL 中的 EXISTS
子句模拟 INTERSECT
在这种情况下,我们只需要年龄小于 45
并且必须至少下一个订单
的客户
详细信息。EXISTS
子句以下列方式使用。
如果省略 EXISTS
子句,以下代码也将产生相同的输出。
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);
子查询将首先执行,并给出 order
表中的所有 customer_id
,其中 customer_id
在两个表(order
和 customer
表)中是相同的。然后它将仅选择
那些年龄小于 45
和 customer_id
的客户相关详细信息出现在子查询结果中。
输出:
结论
本文总结了执行一项操作的许多其他替代方案。
我们还学习了在 MySQL 中模拟 INTERSECT
的不同方法。它包括 INNER JOIN
、IN
子句和 EXISTS
子句。
我们甚至看到了如何在 MySQL 中模拟 INTERSECT
操作时使用 WHERE
和 AND
。