MySQL 8.0.31新特性--INTERSECT and EXCEPT

MySQL 8.0.31版本开始支持了INTERSECT（交集）和EXCEPT（差集）运算。INTERSECT 返回两个结果集中都包含的行；EXCEPT返回左侧结果集存在，右侧不存在的行。

INTERSECT和EXCEPT都支持DISTINCT和ALL指定返回行是否去重，默认DISTINCT。

INTERSECT比EXCEPT优先级高，如t1 EXCEPT t2 INTERSECT t3 等价于 t1 EXCEPT (t2 INTERSECT t3)。

创建两张简单的测试表，插入少量数据。

mysql> create table  t_1 (id int  not null auto_increment primary key  invisible , name varchar(64),age int);    
Query OK, 0 rows affected (0.02 sec)

mysql> create table  t_2 (id int  not null auto_increment primary key  invisible , name varchar(64),age int); 
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t_1 values ('lee',60),('candy',20),('Jone',40),('Jack',50);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into t_2 values ('Chen',20),('candy',20),('Ham',45),('Jack',50);          
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from t_1;
+-------+------+
| name  | age  |
+-------+------+
| lee   |   60 |
| candy |   20 |
| Jone  |   40 |
| Jack  |   50 |
+-------+------+
4 rows in set (0.00 sec)

mysql> select * from t_2;
+-------+------+
| name  | age  |
+-------+------+
| Chen  |   20 |
| candy |   20 |
| Ham   |   45 |
| Jack  |   50 |
+-------+------+
4 rows in set (0.00 sec)

在8.0.31之前要实现交集和差集的运算只能分别使用内连接和外连接的方式实现。 如果多个结果集的运算SQL相对复杂。

#交集运算
mysql> select distinct t_1.* from t_1  join t_2 using(name,age);
+-------+------+
| name  | age  |
+-------+------+
| candy |   20 |
| Jack  |   50 |
+-------+------+
2 rows in set (0.00 sec)

#差集运算
mysql> select distinct t_1.* from t_1 left  join t_2 using(name,age) where t_2.name is null;
+------+------+
| name | age  |
+------+------+
| lee  |   60 |
| Jone |   40 |
+------+------+
2 rows in set (0.00 sec)

在8.0.31版本中使用INTERSECT和EXCEPT就非常简便了

#交集
mysql> table t_1 INTERSECT table t_2;
+-------+------+
| name  | age  |
+-------+------+
| candy |   20 |
| Jack  |   50 |
+-------+------+
2 rows in set (0.00 sec)



mysql> select * from t_1 INTERSECT  select * from t_2;
+-------+------+
| name  | age  |
+-------+------+
| candy |   20 |
| Jack  |   50 |
+-------+------+
2 rows in set (0.00 sec)

#差集

mysql> table t_1 EXCEPT table t_2;
+------+------+
| name | age  |
+------+------+
| lee  |   60 |
| Jone |   40 |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from t_1 EXCEPT  select * from t_2;             
+------+------+
| name | age  |
+------+------+
| lee  |   60 |
| Jone |   40 |
+------+------+
2 rows in set (0.00 sec)