MySQL 8.0新特性INTERSECT和EXCEPT用于集合运算

MySQL8.0.31 新版本的推出，MySQL增加了对SQL标准INTERSECT和EXCEPT运算符的支持。

1、INTERSECT

INTERSECT输出多个SELECT语句查询结果中的共有行。INTERSECT运算符是ANSI/ISO SQL标准的一部分(ISO/IEC 9075-2:2016(E))。

我们运行两个查询，第一个会列出ID是 1，2，3 记录，第二个会列出ID是 2，3，4记录。这两个单独的查询是：

we had two result sets T1 and T2:

• T1 result set includes 1, 2, and 3.
• T2 result set includes 2, 3, and 4.

The intersection of T1 and T2 result sets returns the distinct rows which are 2 and 3

(root@localhost)[(none)]> with t1 as (select 1 as id union all select 2 as id union all select 3 as id),
    ->  t2 as (select 2 as id union all select 3 as id union all select 4 as id)
    -> select id from t1 intersect select id from t2;
+----+
| id |
+----+
|  2 |
|  3 |
+----+
2 rows in set (0.02 sec)

2、EXCEPT

except输出在第一个select语句结果中存在但不在二个查询结果中的记录的行。
The following are the rules for combining the result sets of two queries in the above syntax:

The number and order of columns must be the same in both queries.

The data types of the corresponding columns must be the same or compatible.

The following picture shows the EXCEPT operation of the two result sets T1 and T2:

(root@localhost)[(none)]> with t1 as (select 1 as id union all select 2 as id union all select 3 as id),
-> t2 as (select 2 as id union all select 3 as id union all select 4 as id) 
-> select id from t1 except select id from t2;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

3、MySQL 8.0及之前的版本中

在MySQL 8.0及之前的版本中，直接并不支持SQL标准中的INTERSECT和EXCEPT操作符，这两个操作符在SQL中用于集合运算，分别用来找出两个查询结果的交集和差集。但是，你可以通过其他方式来实现类似INTERSECT和EXCEPT的功能。

3.1、模拟INTERSECT

要模拟INTERSECT的行为，你可以使用INNER JOIN或者EXISTS子查询。但是，对于简单的交集查询，通常使用INNER JOIN的方式更为直观。

使用INNER JOIN模拟INTERSECT
假设你有两个表TableA和TableB，你想要找出在TableA.column1和TableB.column1中都存在的值，可以这样做

SELECT A.column1  
FROM TableA A  
  INNER JOIN TableB B ON A.column1 = B.column1;

如果这两个列来自不同的查询，你可以使用子查询或临时表/CTE（公用表表达式，MySQL 8.0支持）来模拟

WITH CTE_A AS (  
    SELECT column1 FROM (your_query_for_TableA)  
), CTE_B AS (  
    SELECT column1 FROM (your_query_for_TableB)  
)  
SELECT A.column1  
FROM CTE_A AS A
  INNER JOIN CTE_B AS B ON A.column1 = B.column1;

3.2、模拟EXCEPT

要模拟EXCEPT的行为，你可以使用LEFT JOIN或者NOT EXISTS子查询。使用LEFT JOIN的方式通常更为直接。

使用LEFT JOIN模拟EXCEPT
假设你仍然使用TableA和TableB，并且想要找出在TableA.column1中存在但在TableB.column1中不存在的值，可以这样做：

SELECT A.column1  
FROM TableA A  
  LEFT JOIN TableB B ON A.column1 = B.column1  
WHERE B.column1 IS NULL;

或者，如果你有两个不同的查询结果，你可以使用子查询或CTE来模拟

WITH CTE_A AS (  
    SELECT column1 FROM (your_query_for_TableA)  
), CTE_B AS (  
    SELECT column1 FROM (your_query_for_TableB)  
)  
SELECT A.column1  
FROM CTE_A AS A  
  LEFT JOIN CTE_B AS B ON A.column1 = B.column1  
WHERE B.column1 IS NULL;

4、注意事项

• 当使用 INTERSECT 或 EXCEPT 时，两个查询的列数和列的数据类型必须兼容。这意味着两个查询必须选择相同数量的列，并且这些列在数据类型上必须能够相互比较。

• 默认情况下，INTERSECT 和 EXCEPT 返回的结果集中的列没有特定的顺序。如果你需要结果按特定顺序返回，你应该使用 ORDER BY 子句。

• 这两个操作符在处理大量数据时可能会比使用 JOIN 或其他集合操作更慢，因为 MySQL 需要分别执行两个查询，然后在内部对它们的结果集进行比较。因此，在性能敏感的应用程序中，建议仔细考虑查询的设计和优化。