MySQL差集join

转载

技术领航博主 2024-10-21 15:49:42

文章标签 MySQL差集join mysql 数据库 dba SQL 文章分类 MySQL 数据库

文章目录

概览
准备工作
交集（ 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)

MySQL差集join_SQL

差集（ 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子句，如下例所示：

交集

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)

差集

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 优化的一个万古不变的宗旨是能提前过滤结果集（也可理解为使结果集缩小）的工作尽量提前做。