MySQL :: MySQL 8.4 参考手册 :: 15.2.14 使用 UNION、INTERSECT 和 EXCEPT 的集合运算

版本 8.4

9.0
8.0

MySQL 8.4 参考手册 / ... / 使用 UNION、INTERSECT 和 EXCEPT 的集合运算

15.2.14 使用 UNION、INTERSECT 和 EXCEPT 的集合运算

SQL 集合运算将多个查询块的结果组合成一个结果。一个查询块，有时也被称为简单表，是任何返回结果集的 SQL 语句，例如SELECT。MySQL 8.4 还支持TABLE 和VALUES 语句。有关更多信息，请参见本章中其他地方对这些语句的单独描述。

SQL 标准定义了以下三种集合运算

UNION：将两个查询块的所有结果组合成一个结果，省略任何重复项。
INTERSECT：仅将两个查询块结果中共同存在的行组合起来，省略任何重复项。
EXCEPT: 对于两个查询块 A 和 B，返回 A 中所有不在 B 中的结果，并去除重复项。
(某些数据库系统，例如 Oracle，使用 MINUS 作为此运算符的名称。MySQL 不支持此运算符。)

MySQL 支持 UNION、INTERSECT 和 EXCEPT。

这些集合运算符中的每一个都支持 ALL 修饰符。当 ALL 关键字位于集合运算符之后时，它会导致结果中包含重复项。有关更多信息和示例，请参阅介绍各个运算符的以下部分。

所有三个集合运算符也支持 DISTINCT 关键字，它会抑制结果中的重复项。由于这是集合运算符的默认行为，因此通常不需要显式指定 DISTINCT。

通常，查询块和集合运算可以以任何数量和顺序组合。这里显示了一个大大简化的表示

query_block [set_op query_block] [set_op query_block] ...

query_block:
    SELECT | TABLE | VALUES

set_op:
    UNION | INTERSECT | EXCEPT

这可以更准确地表示，并且更详细，如下所示

query_expression:
  [with_clause] /* WITH clause */ 
  query_expression_body
  [order_by_clause] [limit_clause] [into_clause]

query_expression_body:
    query_term
 |  query_expression_body UNION [ALL | DISTINCT] query_term
 |  query_expression_body EXCEPT [ALL | DISTINCT] query_term

query_term:
    query_primary
 |  query_term INTERSECT [ALL | DISTINCT] query_primary

query_primary:
    query_block
 |  '(' query_expression_body [order_by_clause] [limit_clause] [into_clause] ')'

query_block:   /* also known as a simple table */
    query_specification                     /* SELECT statement */
 |  table_value_constructor                 /* VALUES statement */
 |  explicit_table                          /* TABLE statement  */

您应该知道，INTERSECT 在 UNION 或 EXCEPT 之前进行评估。这意味着，例如，TABLE x UNION TABLE y INTERSECT TABLE z 始终被评估为 TABLE x UNION (TABLE y INTERSECT TABLE z)。有关更多信息，请参阅第 15.2.8 节“INTERSECT 子句”。

此外，您应该记住，虽然 UNION 和 INTERSECT 集合运算符是可交换的（顺序不重要），但 EXCEPT 不是（操作数的顺序会影响结果）。换句话说，以下所有语句都是正确的

TABLE x UNION TABLE y 和 TABLE y UNION TABLE x 会产生相同的结果，尽管行的顺序可能不同。您可以使用 ORDER BY 强制它们相同；请参阅带有 ORDER BY 和 LIMIT 的集合运算。
TABLE x INTERSECT TABLE y 和 TABLE y INTERSECT TABLE x 返回相同的结果。
TABLE x EXCEPT TABLE y 和 TABLE y EXCEPT TABLE x 不会产生相同的结果。有关示例，请参阅第 15.2.4 节“EXCEPT 子句”。

更多信息和示例可以在接下来的部分中找到。

结果集列名和数据类型

集合运算结果的列名取自第一个查询块的列名。示例

mysql> CREATE TABLE t1 (x INT, y INT);
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO t1 VALUES ROW(4,-2), ROW(5,9);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE t2 (a INT, b INT);
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO t2 VALUES ROW(1,2), ROW(3,4);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> TABLE t1 UNION TABLE t2;
+------+------+
| x    | y    |
+------+------+
|    4 |   -2 |
|    5 |    9 |
|    1 |    2 |
|    3 |    4 |
+------+------+
4 rows in set (0.00 sec)

mysql> TABLE t2 UNION TABLE t1;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    3 |    4 |
|    4 |   -2 |
|    5 |    9 |
+------+------+
4 rows in set (0.00 sec)

这对 UNION、EXCEPT 和 INTERSECT 查询都是正确的。

每个查询块中对应位置的选定列应具有相同的数据类型。例如，第一个语句选择的第一个列应与其他语句选择的第一个列具有相同的类型。如果对应结果列的数据类型不匹配，则结果中的列的类型和长度将考虑所有查询块检索的值。例如，结果集中的列长度不受第一个语句的值长度的约束，如下所示

mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',20);
+----------------------+
| REPEAT('a',1)        |
+----------------------+
| a                    |
| bbbbbbbbbbbbbbbbbbbb |
+----------------------+

带有 TABLE 和 VALUES 语句的集合运算

您也可以使用 TABLE 语句或 VALUES 语句，只要您可以使用等效的 SELECT 语句。假设表 t1 和 t2 已创建并填充，如下所示

CREATE TABLE t1 (x INT, y INT);
INSERT INTO t1 VALUES ROW(4,-2),ROW(5,9);

CREATE TABLE t2 (a INT, b INT);
INSERT INTO t2 VALUES ROW(1,2),ROW(3,4);

在上述情况下，不考虑以 VALUES 开头的查询的输出中的列名，以下所有 UNION 查询都将产生相同的结果

SELECT * FROM t1 UNION SELECT * FROM t2;
TABLE t1 UNION SELECT * FROM t2;
VALUES ROW(4,-2), ROW(5,9) UNION SELECT * FROM t2;
SELECT * FROM t1 UNION TABLE t2;
TABLE t1 UNION TABLE t2;
VALUES ROW(4,-2), ROW(5,9) UNION TABLE t2;
SELECT * FROM t1 UNION VALUES ROW(4,-2),ROW(5,9);
TABLE t1 UNION VALUES ROW(4,-2),ROW(5,9);
VALUES ROW(4,-2), ROW(5,9) UNION VALUES ROW(4,-2),ROW(5,9);

要强制列名相同，请将左侧的查询块包装在 SELECT 语句中，并使用别名，如下所示

mysql> SELECT * FROM (TABLE t2) AS t(x,y) UNION TABLE t1;
+------+------+
| x    | y    |
+------+------+
|    1 |    2 |
|    3 |    4 |
|    4 |   -2 |
|    5 |    9 |
+------+------+
4 rows in set (0.00 sec)

使用 DISTINCT 和 ALL 的集合运算

默认情况下，集合运算的结果中会删除重复行。可选的 DISTINCT 关键字具有相同的效果，但会使其明确。使用可选的 ALL 关键字，不会删除重复行，结果将包含来自联合中所有查询的所有匹配行。

您可以在同一个查询中混合使用 ALL 和 DISTINCT。混合类型将被视为，使用 DISTINCT 的集合运算会覆盖其左侧的所有使用 ALL 的集合运算。可以使用 DISTINCT 与 UNION、INTERSECT 或 EXCEPT 显式地生成 DISTINCT 集，或者使用不带任何后续 DISTINCT 或 ALL 关键字的集合运算隐式地生成 DISTINCT 集。

当一个或多个 TABLE 语句、VALUES 语句或两者都用于生成集合时，集合运算的工作方式相同。

带有 ORDER BY 和 LIMIT 的集合运算

要将 ORDER BY 或 LIMIT 子句应用于用作联合、交集或其他集合运算一部分的单个查询块，请将查询块括起来，并将子句放在括号内，如下所示

(SELECT a FROM t1 WHERE a=10 AND b=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND b=2 ORDER BY a LIMIT 10);

(TABLE t1 ORDER BY x LIMIT 10) 
INTERSECT 
(TABLE t2 ORDER BY a LIMIT 10);

对单个查询块或语句使用 ORDER BY 并不暗示最终结果中行的顺序，因为集合运算产生的行默认情况下是无序的。因此，在这种情况下，ORDER BY 通常与 LIMIT 一起使用，以确定要检索的选定行子集，即使它不一定影响最终结果中这些行的顺序。如果 ORDER BY 在查询块中没有 LIMIT，则会对其进行优化，因为它在任何情况下都没有效果。

要使用 ORDER BY 或 LIMIT 子句对集合运算的整个结果进行排序或限制，请将 ORDER BY 或 LIMIT 放在最后一个语句之后

SELECT a FROM t1
EXCEPT
SELECT a FROM t2 WHERE a=11 AND b=2
ORDER BY a LIMIT 10;

TABLE t1
UNION 
TABLE t2
ORDER BY a LIMIT 10;

如果一个或多个单独的语句使用 ORDER BY、LIMIT 或两者，并且您还希望将 ORDER BY、LIMIT 或两者应用于整个结果，那么每个这样的单独语句都必须用括号括起来。

(SELECT a FROM t1 WHERE a=10 AND b=1)
EXCEPT
(SELECT a FROM t2 WHERE a=11 AND b=2)
ORDER BY a LIMIT 10;

(TABLE t1 ORDER BY a LIMIT 10) 
UNION 
TABLE t2 
ORDER BY a LIMIT 10;

没有 ORDER BY 或 LIMIT 子句的语句不需要用括号括起来；在两个刚刚显示的语句的第二个语句中用 (TABLE t2) 替换 TABLE t2 不会改变 UNION 的结果。

您还可以使用 ORDER BY 和 LIMIT 与 VALUES 语句在集合运算中，如使用 mysql 客户端的此示例所示

mysql> VALUES ROW(4,-2), ROW(5,9), ROW(-1,3) 
    -> UNION 
    -> VALUES ROW(1,2), ROW(3,4), ROW(-1,3) 
    -> ORDER BY column_0 DESC LIMIT 3;
+----------+----------+
| column_0 | column_1 |
+----------+----------+
|        5 |        9 |
|        4 |       -2 |
|        3 |        4 |
+----------+----------+
3 rows in set (0.00 sec)

(您应该记住，TABLE 语句和 VALUES 语句都不接受 WHERE 子句。)

这种类型的 ORDER BY 无法使用包含表名的列引用（即 tbl_name.col_name 格式的名称）。而是，在第一个查询块中提供一个列别名，并在 ORDER BY 子句中引用该别名。（您也可以使用其列位置在 ORDER BY 子句中引用该列，但这种列位置的使用已过时，因此可能在未来的 MySQL 版本中被删除。）

如果要排序的列有别名，则 ORDER BY 子句必须引用别名，而不是列名。以下语句中的第一个是允许的，但第二个会失败，并出现 Unknown column 'a' in 'order clause' 错误

(SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY b;
(SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY a;

要使 UNION 结果中的行由每个查询块检索到的行集一个接一个地组成，请在每个查询块中选择一个额外的列用作排序列，并在最后一个查询块后添加一个对该列进行排序的 ORDER BY 子句

(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1)
UNION
(SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col;

要保持单个结果中的排序顺序，请在 ORDER BY 子句中添加一个辅助列

(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1)
UNION
(SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col, col1a;

使用额外的列还可以让您确定每行来自哪个查询块。额外的列还可以提供其他识别信息，例如指示表名的字符串。

集合运算的限制

MySQL 中的集合运算存在一些限制，这些限制将在接下来的几段中描述。

包括 SELECT 语句的集合运算具有以下限制

第一个 SELECT 中的 HIGH_PRIORITY 没有任何效果。任何后续 SELECT 中的 HIGH_PRIORITY 会产生语法错误。
只有最后一个 SELECT 语句可以使用 INTO 子句。但是，整个 UNION 结果将被写入 INTO 输出目标。

这两个包含 INTO 的 UNION 变体已过时；您应该预期在未来的 MySQL 版本中对它们的支

在查询表达式的尾随查询块中，在 FROM 之前使用 INTO 会产生警告。示例
```
... UNION SELECT * INTO OUTFILE 'file_name' FROM table_name;
```
在查询表达式的括号括起来的尾随块中，使用 INTO（无论其相对于 FROM 的位置）都会产生警告。示例
```
... UNION (SELECT * INTO OUTFILE 'file_name' FROM table_name);
```
这些变体已过时，因为它们会造成混淆，就像它们从命名表而不是整个查询表达式（UNION）中收集信息一样。

在 ORDER BY 子句中使用聚合函数的集合运算将被 ER_AGGREGATE_ORDER_FOR_UNION 拒绝。虽然错误名称可能表明这仅限于 UNION 查询，但前面提到的情况也适用于 EXCEPT 和 INTERSECT 查询，如下所示

mysql> TABLE t1 INTERSECT TABLE t2 ORDER BY MAX(x);
ERROR 3028 (HY000): Expression #1 of ORDER BY contains aggregate function and applies to a UNION, EXCEPT or INTERSECT

锁定子句（例如 FOR UPDATE 或 LOCK IN SHARE MODE）适用于它后面的查询块。这意味着，在与集合运算一起使用的 SELECT 语句中，只有在查询块和锁定子句都用括号括起来时，才能使用锁定子句。