MySQL 8.0.31 新增支持SQL标准中的交集（INTERSECT）和差集（EXCEPT）表操作符

概览

最近的 MySQL 版本（ 8.0.31 ） 中，新增了对 SQL 标准 INTERSECT 和 EXCEPT表操作符（集合运算）的支持。

准备工作

以下表为例，建表语句如下：

mysql> CREATE TABLE `t_stu_cou_rel`(
	id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	stu_id INT UNSIGNED NOT NULL,
	cou_id INT UNSIGNED NOT NULL,
	notes VARCHAR(100),
	start_date DATE,
	end_date DATE) ENGINE=INNODB;

插入一些样例数据：

mysql> INSERT INTO t_stu_cou_rel VALUES(1,90001,3001,NULL,'20220905','20230117'),
(2,90002,3001,NULL,'20220905','20230117'),
(3,90003,3001,NULL,'20220905','20230117'),
(4,90001,2015,NULL,'20220901','20230110'),
(NULL,90003,3003,'Public Elective Course.','20221013',NULL);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from t_stu_cou_rel;
+----+--------+--------+-------------------------+------------+------------+
| id | stu_id | cou_id | notes                   | start_date | end_date   |
+----+--------+--------+-------------------------+------------+------------+
|  1 |  90001 |   3001 | NULL                    | 2022-09-05 | 2023-01-17 |
|  2 |  90002 |   3001 | NULL                    | 2022-09-05 | 2023-01-17 |
|  3 |  90003 |   3001 | NULL                    | 2022-09-05 | 2023-01-17 |
|  4 |  90001 |   2015 | NULL                    | 2022-09-01 | 2023-01-10 |
|  5 |  90003 |   3003 | Public Elective Course. | 2022-10-13 | NULL       |
+----+--------+--------+-------------------------+------------+------------+
5 rows in set (0.00 sec)

交集（ INTERSECT ）

**Query 1：**查询选修了课程 3001 的学生的学号：

mysql> SELECT stu_id FROM t_stu_cou_rel where cou_id = '3001';
+--------+
| stu_id |
+--------+
|  90001 |
|  90002 |
|  90003 |
+--------+
3 rows in set (0.00 sec)

**Query 2：**查询选修了课程 2015 的学生的学号：

mysql> SELECT stu_id FROM t_stu_cou_rel where cou_id = '2015';
+--------+
| stu_id |
+--------+
|  90001 |
+--------+
1 row in set (0.00 sec)

执行如下语句，查询选修了课程 3001 和 2015 的学生的学号 stu_id ,即 Query 1 INTERSECTQuery 2：

mysql> SELECT stu_id FROM t_stu_cou_rel where cou_id = '3001' INTERSECT SELECT stu_id FROM t_stu_cou_rel where cou_id = '2015';
+--------+
| stu_id |
+--------+
|  90001 |
+--------+
1 row in set (0.00 sec)

差集（ EXCEPT）

同样，以上面两个查询 Query 1 和 Query 2 为前提，这次我们查询选修了 3001 课程而没选修 2015 课程的学生的学号 stu_id ，即 Query 1 EXCEPTQuery 2 ：

mysql> SELECT stu_id FROM t_stu_cou_rel where cou_id = '3001' EXCEPT SELECT stu_id FROM t_stu_cou_rel where cou_id = '2015';
+--------+
| stu_id |
+--------+
|  90002 |
|  90003 |
+--------+
2 rows in set (0.00 sec)

EXCEPT相当于 Oracle 的MINUS集合运算符。

旧版本中交集、差集操作是如何实现的

在之前版本，实现交集、差集操作的最简单的办法是使用外连接+WHERE子句，如下例所示：

1. 交集

mysql> SELECT a.stu_id FROM t_stu_cou_rel a LEFT JOIN t_stu_cou_rel b ON a.stu_id = b.stu_id AND a.cou_id = '3001' AND b.cou_id = '2015' WHERE b.stu_id IS NOT NULL;
+--------+
| stu_id |
+--------+
|  90001 |
+--------+
1 row in set (0.01 sec)
--或
mysql> SELECT a.stu_id FROM (SELECT stu_id FROM t_stu_cou_rel WHERE cou_id = '3001') a LEFT JOIN (SELECT stu_id FROM t_stu_cou_rel WHERE cou_id = '2015') b ON a.stu_id = b.stu_id WHERE b.stu_id IS NOT NULL;
+--------+
| stu_id |
+--------+
|  90001 |
+--------+
1 row in set (0.00 sec)

2. 差集

mysql> SELECT a.stu_id FROM (SELECT stu_id FROM t_stu_cou_rel WHERE cou_id = '3001') a LEFT JOIN (SELECT stu_id FROM t_stu_cou_rel WHERE cou_id = '2015') b ON a.stu_id = b.stu_id WHERE b.stu_id IS NULL;
+--------+
| stu_id |
+--------+
|  90002 |
|  90003 |
+--------+
2 rows in set (0.01 sec)

注意
一定要充分理解后再使用外连接。
使用外连接时一定要核对结果集是否如你预期，如否，则查询书写有误，重新检查SQL语句，不建议通过添加额外的WHERE条件来删除多余的行，因为一定程度上讲，这是为了结果正确而强行做的操作，不符合实际情况。
正确的做法是将查询条件写入派生表表中，利用谓词前推提前过滤数据集，再进行表外连接。如上所示。
下面这种做法因外连接使用不当会得到与你预期不符的结果，而这个结果对你所写的SQL语句来讲是“正确”的：

mysql> SELECT a.stu_id FROM t_stu_cou_rel a LEFT JOIN t_stu_cou_rel b ON a.stu_id = b.stu_id AND a.cou_id = '3001' AND b.cou_id = '2015' WHERE b.stu_id IS NULL;
+--------+
| stu_id |
+--------+
|  90002 |
|  90003 |
|  90001 |
|  90003 |
+--------+
4 rows in set (0.00 sec)

总之，SQL 优化的一个万古不变的宗旨是能提前过滤结果集（也可理解为使结果集缩小）的工作尽量提前做。

总结

现在可以更方便地对结果集做交集、差集运算了，增强了对更新版本的SQL标准的兼容性。

由此可见，MySQL 正逐渐贴近 Oracle ， Oracle 公司正在将 Oracle 数据库的优点和特色功能在 MySQL 的 InnoDB 引擎上实现，同时增强了对更新版本的SQL标准的兼容性。

本次 8.0.31 版本更新还包含了对标准SQL中FULL关键字的预告支持，先将其作为关键字保留。如果非要使用 full 作为列、表、视图、存储过程或存储函数的名称，则建议使用**反引号（`）**包围，直接使用会产生警告。