Documentation Home
MySQL 8.4 Reference Manual
Related Documentation
MySQL 8.4 Release Notes
Download this Manual
PDF (US Ltr) - 39.8Mb
PDF (A4) - 39.9Mb
Man Pages (TGZ) - 257.9Kb
Man Pages (Zip) - 364.9Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb


MySQL 8.4 Reference Manual  /  ...  /  Set Operations with UNION, INTERSECT, and 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 Clause”，了解更多信息。

此外，您应该记住，UNION 和 INTERSECT 集运算符是可交换的（顺序无关紧要），而 EXCEPT 不是（操作数顺序影响结果）。换言之，以下所有语句都是真的：

  • TABLE x UNION TABLE y 和 TABLE y UNION TABLE x 产生相同的结果，尽管行的顺序可能不同。您可以使用 ORDER BY 强制它们相同；请参阅Set Operations with ORDER BY and 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 Clause”，了解示例。

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

Result Set Column Names and 数据类型

结果集的列名来自于第一个查询块的列名。示例：

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 |
+----------------------+

Set Operations with TABLE and VALUES 语句s

您也可以使用 TABLE 语句或 VALUES 语句 wherever 可以使用等效的 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关键字，不会删除重复行，并且结果将包括所有匹配行来自所有查询的union。

您可以将ALL和DISTINCT混合在同一个查询中。混合类型将被处理，以便DISTINCT操作override任何使用ALL的操作。使用DISTINCT可以明确地生产一个集合操作，可以使用UNION、INTERSECT或EXCEPT语句，或者隐式地使用set操作没有DISTINCT或ALL关键字。

set操作在使用一个或多个TABLE语句、VALUES语句或两者都使用时工作相同。

要将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对单个查询块或语句进行排序或限制通常是为了确定要检索的行子集，即使它不一定会影响结果中的行顺序。因此，在set操作中，ORDER BY通常与LIMIT一起使用，以确定要检索的行子集，即使它不一定会影响结果中的行顺序。如果ORDER BY在查询块中没有LIMIT，它将被优化，因为它在任何情况下都没有影响。

要使用ORDER BY或LIMIT子句对set操作的整个结果进行排序或限制，请将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 语句的集合操作有以下限制：

  • HIGH_PRIORITY 在第一个 SELECT 中无效。任何后续 SELECT 中的 HIGH_PRIORITY 将产生语法错误。

  • 只有最后一个 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
Related Documentation
MySQL 8.4 Release Notes
Download this Manual
PDF (US Ltr) - 39.8Mb
PDF (A4) - 39.9Mb
Man Pages (TGZ) - 257.9Kb
Man Pages (Zip) - 364.9Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb