MySQL 8.0新特性之集合操作符INTERSECT和EXCEPT

吾爱主题阅读：198 2022-11-28 15:04:00 评论：0

前言

最近几年，MySQL 不断致力于兼容 SQL 标准。例如 MySQL 8.0 中的窗口函数、通用表表达式、检查约束等等。

最新发布的 MySQL 8.0.31 继续对 SQL 语句进行了增强，提供了缺失已久的两个集合操作符：INTERSECT 和 EXCEPT。

交集操作符（INTERSECT）

INTERSECT 操作符用于返回两个查询结果中的共同部分，即同时出现在第一个查询结果和第二个查询结果中的数据，并且对最终结果进行了去重操作。交集运算的示意图如下：

其中，1 和 2 是两个查询中都存在的数据；因此交集运算的结果只包含 1 和 2。

我们创建一个年度优秀员工表（excellent_emp），用于演示集合操作：

1 2 3 4 5 6 7 8 9 10 CREATE TABLE excellent_emp( year INT NOT NULL , emp_id INTEGER NOT NULL , CONSTRAINT pk_excellent_emp PRIMARY KEY ( YEAR , emp_id) ); INSERT INTO excellent_emp VALUES (2018, 9); INSERT INTO excellent_emp VALUES (2018, 11); INSERT INTO excellent_emp VALUES (2019, 9); INSERT INTO excellent_emp VALUES (2019, 20);

以下示例用于查找 2018 年和 2019 年都是优秀员工的员工编号：

1 2 3 4 5 6 7 8 9 10 11 SELECT emp_id FROM excellent_emp WHERE year = 2018 INTERSECT SELECT emp_id FROM excellent_emp WHERE year = 2019; emp_id| ------| 9|

其中，INTERSECT 表示交集运算。第一个查询语句返回了 9 和 11，第二个查询语句返回了 9 和 20，最终结果返回共同的 9。集合操作返回的字段名由第一个语句决定，此处两个语句拥有相同的字段名（emp_id）。

对于 MySQL 5.7 以及之前的版本，以上示例可以改写为等价的连接查询：

1 2 3 4 5 6 7 8 9 10 SELECT t1.emp_id FROM excellent_emp t1 JOIN excellent_emp t2 ON (t1.emp_id = t2.emp_id AND t1. year = 2018 AND t2. year = 2019); emp_id| ------| 9|

交集运算都可以改写为等价的等值内连接查询。

INTERSECT 操作符的完整语法如下：

1 2 3 SELECT ... INTERSECT [ ALL | DISTINCT ] SELECT ... [ INTERSECT [ ALL | DISTINCT ] SELECT ...]

ALL 选项表示保留查询结果集中的重复记录，DISTINCT 选项表示去除查询结果集中的重复记录，默认选项为 DISTINCT。

另外，INTERSECT 操作符的优先级比 UNION 和 EXCEPT 更高，因此以下两种写法等价：

1 2 3 TABLE r EXCEPT TABLE s INTERSECT TABLE t; TABLE r EXCEPT ( TABLE s INTERSECT TABLE t);

关于 INTERSECT 操作符的更多信息，可以参考官方文档。

差集操作符（EXCEPT）

EXCEPT 操作符用于返回出现在第一个查询结果中，但不在第二个查询结果中的记录，并且对最终结果进行了去重操作。差集运算的示意图如下：

第一个查询的结果中只有 3 没有出现在第二个查询的结果中，因此差集运算的结果只保留了 3。

以下语句查找 2019 年被评为优秀，但是 2018 年不是优秀的员工：

1 2 3 4 5 6 7 8 9 10 11 SELECT emp_id FROM excellent_emp WHERE year = 2019 EXCEPT SELECT emp_id FROM excellent_emp WHERE year = 2018; emp_id| ------| 20|

查询结果显示，只有 20 号员工是 2019 年新晋的优秀员工。

对于 MySQL 5.7 以及之前的版本，以上示例可以改写为等价左外连接查询：

1 2 3 4 5 6 7 8 9 SELECT t1.emp_id FROM excellent_emp t1 LEFT JOIN excellent_emp t2 ON (t1.emp_id = t2.emp_id AND t2. year = 2018) WHERE t1. year = 2019 AND t2.emp_id IS NULL ; emp_id| ------| 20|