MySQL :: MySQL 9.0 参考手册 :: 15.2.14 使用 UNION、INTERSECT 和 EXCEPT 进行集合操作

版本 9.0

8.4 当前
8.0

MySQL 9.0 参考手册 / ... / 使用 UNION、INTERSECT 和 EXCEPT 进行集合操作

15.2.14 使用 UNION、INTERSECT 和 EXCEPT 进行集合操作

SQL 集合操作将多个查询块的结果组合成一个结果。一个 查询块，有时也称为 简单表，是任何返回结果集的 SQL 语句，例如 SELECT。MySQL 9.0 还支持 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 语句的集合运算

您也可以在可以使用等效 SELECT 语句的任何地方使用 TABLE 语句或 VALUES 语句。假设表 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 的运算。可以通过使用带有 UNION、INTERSECT 或 EXCEPT 的 DISTINCT 显式地产生一个 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 语句中，只有在查询块和锁定子句用括号括起来时才能使用锁定子句。

PREV HOME UP NEXT