##

在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:56:49) > insert into new values(2,'Miguel',5,0);
Query OK, 1 row affected (0.01 sec)

root@localhost [db01] (16:57:13) > insert into new values(3,'lefred',4,5);
Query OK, 1 row affected (0.00 sec)

root@localhost [db01] (16:57:30) > insert into new values(4,'Kajiyamasan',null,10);
Query OK, 1 row affected (0.01 sec)

root@localhost [db01] (16:57:51) > insert into new values(5,'Scott',10,null);
Query OK, 1 row affected (0.01 sec)

root@localhost [db01] (16:58:07) > insert into new values(6,'Lenka',null,null);
Query OK, 1 row affected (0.01 sec)

root@localhost [db01] (16:58:21) > commit;
Query OK, 0 rows affected (0.00 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.