在MySQL 官网彩蛋中学习intersect和except语法

##

在MySQL 官网彩蛋中学习intersect和except语法

在最新的 MySQL 版本（8.0.31）中，MySQL 添加了对 SQL 标准INTERSECT和EXCEPT表运算符的支持，让我们案例出发并寻找MySQL官网的彩蛋.

1、环境准备

1.1、创建表

CREATE TABLE `new` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`tacos` int DEFAULT NULL,
`sushis` int DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;


1.2、插入数据

root@localhost [db01] (16:56:42) > insert into new values(1,'Kenny',NULL,10);
Query OK, 1 row affected (0.01 sec)

root@localhost [db01] (16:58:23) > select * from new;
+----+-------------+-------+--------+
| id | name | tacos | sushis |
+----+-------------+-------+--------+
| 1 | Kenny | NULL | 10 |
| 2 | Miguel | 5 | 0 |
| 3 | lefred | 4 | 5 |
| 4 | Kajiyamasan | NULL | 10 |
| 5 | Scott | 10 | NULL |
| 6 | Lenka | NULL | NULL |
+----+-------------+-------+--------+
6 rows in set (0.00 sec)


1.3、查询语句

(query 1) select * from new where tacos>0;
(query 2) select * from new where sushis>0;


2、INTERSECT

2.1、INTERSECT语法

query_expression_body INTERSECT [ALL | DISTINCT] query_expression_body
[INTERSECT [ALL | DISTINCT] query_expression_body]
[...]

query_expression_body:
See Section 13.2.14, “Set Operations with UNION, INTERSECT, and EXCEPT”


INTERSECT与UNION一样，如果既没有 指定DISTINCT也没有ALL指定，则默认为DISTINCT。

2.2、INTERSECT案例

root@localhost [db01] (16:58:32) > select * from new where tacos>0 intersect select * from new where sushis>0;
+----+--------+-------+--------+
| id | name | tacos | sushis |
+----+--------+-------+--------+
| 3 | lefred | 4 | 5 |
+----+--------+-------+--------+
1 row in set (0.00 sec)


在以前版本的 MySQL 上，此类查询的结果将是：

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version
for the right syntax to use near 'intersect select * from new where sushis > 0' at line 1


3、except

3.1、except语法

query_expression_body EXCEPT [ALL | DISTINCT] query_expression_body
[EXCEPT [ALL | DISTINCT] query_expression_body]
[...]

query_expression_body:
See Section 13.2.14, “Set Operations with UNION, INTERSECT, and EXCEPT”


except与UNION一样，如果既没有 指定DISTINCT也没有ALL指定，则默认为DISTINCT。

3.2、except案例

root@localhost [db01] (16:59:11) > select * from new where tacos > 0 except select * from new where sushis > 0;
+----+--------+-------+--------+
| id | name | tacos | sushis |
+----+--------+-------+--------+
| 2 | Miguel | 5 | 0 |
| 5 | Scott | 10 | NULL |
+----+--------+-------+--------+
2 rows in set (0.00 sec)


4、官网彩蛋

https://dev.mysql.com/doc/refman/8.0/en/except.html
As with UNION and INTERSECT, if neither DISTINCT nor ALL is specified, the default is DISTINCT.

https://dev.mysql.com/doc/refman/8.0/en/intersect.html
As with UNION and EXCEPT, if neither DISTINCT nor ALL is specified, the default is DISTINCT.