新特性解读 | MySQL 8.0 的交集和差集介绍

比如在MySQL 5.7.x 中，想要实现如下两个需求：

简单创建表t1、表t2，并且插入几条样例数据：

<mysql:5.7.34:(ytt)> create table t1(c1 int);
Query OK, 0 rows affected (0.02 sec)

<mysql:5.7.34:(ytt)> create table t2 like t1;
Query OK, 0 rows affected (0.02 sec)

<mysql:5.7.34:(ytt)> insert t1 values (10),(20),(20),(30),(40),(40),(50);
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

<mysql:5.7.34:(ytt)> insert t2 values (10),(30),(30),(50),(50),(70),(90);
Query OK, 7 rows affected (0.02 sec)
Records: 7  Duplicates: 0  Warnings: 0 
<mysql:5.7.34:(ytt)> select * from t1;
+------+
| c1   |
+------+
|   10 |
|   20 |
|   20 |
|   30 |
|   40 |
|   40 |
|   50 |
+------+
7 rows in set (0.00 sec)

<mysql:5.7.34:(ytt)> select * from t2;
+------+
| c1   |
+------+
|   10 |
|   30 |
|   30 |
|   50 |
|   50 |
|   70 |
|   90 |
+------+
7 rows in set (0.00 sec)

复制

<mysql:5.7.34:(ytt)> select distinct t1.c1 from t1 join t2 using(c1);
+------+
| c1   |
+------+
|   10 |
|   30 |
|   50 |
+------+
3 rows in set (0.00 sec)

复制

<mysql:5.7.34:(ytt)> select distinct t1.c1 from t1 left join t2 using(c1) where t2.c1 is null;
+------+
| c1   |
+------+
|   20 |
|   40 |
+------+
2 rows in set (0.00 sec)

复制

创建好同样的表结构和数据，用 intersect 来求交集：

<mysql:8.0.31:(ytt)>table t1 intersect table t2;
+------+
| c1   |
+------+
|   10 |
|   30 |
|   50 |
+------+
3 rows in set (0.00 sec)

复制

用 except 来求差集：

<mysql:8.0.31:(ytt)>table t1 except table t2;
+------+
| c1   |
+------+
|   20 |
|   40 |
+------+
2 rows in set (0.00 sec)

复制

intersect 和 except 操作符默认去重。比如需要保留原始结果，则可以带上all 关键词：如下求两表差集的结果会保留所有符合条件的记录。

<mysql:8.0.31:(ytt)>table t1 except all table t2;
+------+
| c1   |
+------+
|   20 |
|   20 |
|   40 |
|   40 |
+------+
4 rows in set (0.00 sec)

复制