MySQL :: MySQL 8.3 Reference Manual :: 15.2.14 Set Operations with UNION, INTERSECT, and EXCEPT

version 8.3

8.0 current

MySQL 8.3 Reference Manual / ... / Set Operations with UNION, INTERSECT, and EXCEPT

15.2.14 集合操作与 UNION、INTERSECT 和 EXCEPT

SQL 集合操作将多个查询块的结果合并到单个结果集中。一个 查询块，有时也称为 简单表，是任何返回结果集的 SQL 语句，例如 SELECT。MySQL 8.3 也支持 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 in Unions。
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 语句的任何地方使用它们。假设表 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 显式地生成集合，例如使用 DISTINCT 与 UNION、INTERSECT 或 EXCEPT，或隐式地使用集合操作而不带 DISTINCT 或 ALL 关键字。

集合操作在使用一个或多个 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;

不需要括号的语句不需要括号；在第二个语句中将 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)

(请注意， neither TABLE 语句 nor 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 语句在内的集合操作存在以下限制：

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

以下两个 UNION 变体包含 INTO 已弃用；您应该期望在未来 MySQL 版本中删除对它们的支持：

在查询表达式的尾随查询块中，使用 INTO 前 FROM 将产生警告。示例：
```
... 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