在MySQL 8.0中的交集和差集

发布于2022年11月11日，作者：Frederic Descamps
分类：Optimizer
标签：optimizer
在最新的MySQL发布版本（8.0.31）中，MySQL添加了对SQL标准INTERSECT（交集）和EXCEPT（差集）表操作符的支持：

让我们看看如何使用它们。

我们将使用下面的表：

CREATE TABLE `new` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `tacos` int DEFAULT NULL,
  `sushis` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

在我们的团队会议上，我们将预订tacos和sushi。

每条记录代表每个团队成员的订餐：

select * from new;
+----+-------------+-------+--------+
| id | name        | tacos | sushis |
+----+-------------+-------+--------+
|  1 | Kenny       |  NULL |     10 |
|  2 | Miguel      |     5 |      0 |
|  3 | lefred      |     4 |      5 |
|  4 | Kajiyamasan |  NULL |     10 |
|  5 | Scott       |    10 |   NULL |
|  6 | Lenka       |  NULL |   NULL |
+----+-------------+-------+--------+

交集

用户手册介绍说，INTERSECT将多个SELECT语句的结果限制为所有语句中都存在的行。INTERSECT操作符是ANSI/ISO SQL标准的一部分（ISO/IEC 9075-2:2016(E)）

我们想要运行两个查询，第一个将列出所有选择了tacos的团队成员的记录，第二个将返回所有选择了sushi的人员的记录。

这两个单独的查询是：

（查询 1） select * from new where tacos>0;

（查询 2） select * from new where sushis>0;

交集的说明

在两个结果中都存在的唯一记录是id=3的记录。

我们用交集验证一下：

select * from new where tacos > 0 
intersect 
select * from new where sushis > 0;
+----+--------+-------+--------+
| id | name   | tacos | sushis |
+----+--------+-------+--------+
|  3 | lefred |     4 |      5 |
+----+--------+-------+--------+

很好，在MySQL的先前版本中，这样的查询的结果会是：

ERROR 1064 (42000): You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version 
for the right syntax to use near 
'intersect select * from new where sushis > 0' at line 1

差集

在手册中，我们可以读到，EXCEPT将第一个SELECT语句的结果限制为那些在第二个语句中找不到的行。

让我们找出所有只会吃tacos的团队成员，使用EXCEPT:

select * from new where tacos > 0 
except 
select * from new where sushis > 0;
+----+--------+-------+--------+
| id | name   | tacos | sushis |
+----+--------+-------+--------+
|  2 | Miguel |     5 |      0 |
|  5 | Scott  |    10 |   NULL |
+----+--------+-------+--------+

差集的说明

如果我们想做反向的操作，得到所有只吃sushi的人，我们可以像这样换一下查询的顺序：

select * from new where sushis > 0 
except 
select * from new where tacos > 0;
+----+-------------+-------+--------+
| id | name        | tacos | sushis |
+----+-------------+-------+--------+
|  1 | Kenny       |  NULL |     10 |
|  4 | Kajiyamasan |  NULL |     10 |
+----+-------------+-------+--------+

结论

MySQL 8.0.31继续承继8.0的传统，包括对诸如窗口函数、公共表表达式、Lateral Derived Tables、JSON_TABLES、JSON_VALUE等 SQL 标准的支持。

享受MySQL！