MySQL 8.0中的INTERSECT和EXCEPT

oracleace 2023-02-17

606

摘要：随着MySQL最新版本（8.0.31）的推出，MySQL增加了对SQL标准INTERSECT和EXCEPT运算符的支持。

原文：https://blogs.oracle.com/mysql/post/intersect-and-except-in-mysql-80 （本文被Oracle评为2022年最佳博客）

作者：Frédéric Descamps，Oracle公司MySQL社区经理，知名MySQL布道师。

关于译者，姚远：

Oracle ACE（Oracle和MySQL数据库方向）
华为云MVP
《MySQL 8.0运维与优化》的作者
中国唯一一位Oracle高可用大师
拥有包括 Oracle 10g和12c OCM在内的20+数据库相关认证。
曾任IBM公司数据库部门经理
现在一家第三方公司任首席数据库专家，服务2万+客户。

随着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）和寿司（sushis），每条记录代表一个团队成员选择甜点的信息：

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

INTERSECT输出多个SELECT语句查询结果中的共有行。INTERSECT运算符是ANSI/ISO SQL标准的一部分(ISO/IEC 9075-2:2016(E))。

我们运行两个查询，第一个会列出团队成员选择玉米饼的所有记录，第二个会返回团队成员选择寿司的所有记录。这两个单独的查询是：

(query 1) select * from new where tacos>0;
(query 2) select * from new where sushis>0;

INTERSECT的插图

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

让我们使用INTERSECT来确认：

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

EXCEPT输出在第一个SELECT语句结果中存在但不在第二个SELECT语句结果中的行。

让我们找出所有只使用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 |
+----+--------+-------+--------+

EXCEPT的插图

如果我们想反过来，让所有只吃寿司的人，我们就会像这样反转查询顺序：

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 |
+----+-------------+-------+--------+