Mysql 带有 UNION、INTERSECT 和 EXCEPT 的集合运算

来自泡泡学习笔记
跳到导航 跳到搜索

SQL 集合运算将多个查询块的结果合并到单个结果中。查询块（有时也称为简单表）是任何返回结果集的 SQL 语句，例如 SELECT。MySQL 8.4 还支持 TABLE 和 VALUES 语句。有关这些语句的更多信息，请查看本章中其他位置的各自说明。

MySQL 5.7只支持UNION。


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)。


另外，您应该记住，虽然 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 不会产生相同的结果。

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


结果集列名和数据类型

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

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 的任何此类运算。通过将 DISTINCT 与 UNION、INTERSECT 或 EXCEPT 一起使用，可以显式地生成一个 DISTINCT 集合，或者隐式地通过在集合运算后不跟随 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;


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


您还可以在集合运算中对 VALUES 语句使用 ORDER BY 和 LIMIT，如下例所示，使用 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 子句必须引用该别名，而不是列名。以下第一个语句是允许的，但第二个语句会因“order 子句中未知的列'a'”错误而失败： 

(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 语句的集合运算具有以下限制：

1. 第一个 SELECT 中的 HIGH_PRIORITY 没有效果。在任何后续的 SELECT 中使用 HIGH_PRIORITY 会产生语法错误。


2. 只有最后一个 SELECT 语句可以使用 INTO 子句。但是，整个 UNION 结果会被写入 INTO 输出目标。


3. 这两个包含 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）收集信息。


4. 在 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`语句中，只有将查询块和锁定子句括在括号中时才能使用锁定子句。

取自“http://bubblestudy.info/wiki/index.php?title=Mysql_带有_UNION、INTERSECT_和_EXCEPT_的集合运算&oldid=3256”