迹忆客 专注技术分享

本文将帮助你了解 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。