UNION 子句
SELECT ...UNION [ALL | DISTINCT] SELECT ...[UNION [ALL | DISTINCT] SELECT ...]
UNION 将来自多个 SELECT语句的结果组合到一个结果集中。例子:mysql> SELECT 1, 2;+---+---+| 1 | 2 |+---+---+| 1 | 2 |+---+---+mysql> SELECT 'a', 'b';+---+---+| a | b |+---+---+| a | b |+---+---+mysql> SELECT 1, 2 UNION SELECT 'a', 'b';+---+---+| 1 | 2 |+---+---+| 1 | 2 || a | b |+---+---+
结果集列名和数据类型UNION 结果集的列名取自第一个 SELECT语句的列名。在每个 SELECT 语句的相应位置列出的选定列应具有相同的数据类型。例如,第一条语句选择的第一列应该与其他语句选择的第一列具有相同的类型。如果相应的 SELECT列的数据类型不匹配,则 UNION结果中列的类型和长度将考虑所有 SELECT语句检索到的值。例如,考虑以下情况,其中列长度不受第一个 SELECT语句的值的长度限制:mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',20);+----------------------+| REPEAT('a',1) |+----------------------+| a || bbbbbbbbbbbbbbbbbbbb |+----------------------+
联合中的 TABLE 语句从 MySQL 8.0.19 开始,在 UNION中只要可以使用 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);
要强制列名相同,请将 VALUES 放在 SELECT语句中的左侧并使用别名,如下所示:SELECT * FROM (VALUES ROW(4,-2), ROW(5,9)) AS t(x,y) UNION TABLE t2;SELECT * FROM (VALUES ROW(4,-2), ROW(5,9)) AS t(x,y) UNION VALUES ROW(4,-2),ROW(5,9);
UNION DISTINCT 和UNION ALL默认情况下,将从 UNION 结果中删除重复行。可选的 DISTINCT关键字具有相同的效果,但是看起来更加明确。使用可选的 ALL关键字,不会删除重复行,结果包括所有 SELECT语句中的所有匹配行。可以在同一个查询中混用 UNION ALL 和 UNION DISTINCT。混用 UNION类型的处理方式是,DISTINCT联合会覆盖其左侧的所有 ALL联合。可以通过使用 UNION DISTINCT语句生成 DISTINCT联合,也可以使用 UNION,后面不带 DISTINCT或 ALL关键字来达到同样的 DISTINCT联合效果。在 MySQL 8.0.19 及更高版本中,当联合语句中使用一个或多个 TABLE语句时,UNION ALL和 UNION DISTINCT的工作方式相同。联合语句中的 ORDER BY 和LIMIT要将 ORDER BY 或 LIMIT子句应用于单个 SELECT,请用圆括号括住SELECT并将该子句放在括号内:(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);
对单个 SELECT 语句使用 ORDER BY并不意味着行在最终结果中出现的顺序,因为 UNION默认情况下会生成一组无序的行。因此,此上下文中的ORDER BY通常与 LIMIT一起使用,以确定要为 SELECT检索的选定行的子集,即使它不一定会影响这些行在最终 UNION结果中的顺序。如果ORDER BY在 SELECT中没有 LIMIT,那么它将被优化掉,因为它无论如何都不会有任何效果。要使用 ORDER BY 或 LIMIT子句对整个联合结果进行排序或限制,请将各个 SELECT语句括起来,并将 ORDER BY或 LIMIT放在最后一个语句之后:(SELECT a FROM t1 WHERE a=10 AND B=1)UNION(SELECT a FROM t2 WHERE a=11 AND B=2)ORDER BY a LIMIT 10;
从 MySQL 8.0.19 开始,可以在联合语句中将 ORDER BY和 LIMIT与TABLE一起使用,与前面讲的相同,记住 TABLE不支持 WHERE子句。这种 ORDER BY 不能使用包含表名的列引用(即tbl_name.col_name)。相反,请在第一个 SELECT 语句中提供列别名,并在ORDER BY中引用该别名。(或者,在ORDER BY中使用列位置引用列。但是,不推荐这种用法。)另外,如果要排序的列是别名,则 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 结果中的行一个接一个的由每个 SELECT检索的行组成,请在每个 SELECT中选择一个附加列作为排序列,并在最后一个 SELECT之后对该列使用 ORDER BY进行排序:(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1)UNION(SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col;
要在单个 SELECT 结果中维护排序顺序,请向 ORDER BY子句添加一个辅助列:(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1)UNION(SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col, col1a;
使用附加列还可以确定每行来自哪个 SELECT 语句。额外的列也可以提供其他标识信息,例如指出表名。UNION 的限制在 UNION 语句中,SELECT语句是普通的选择语句,但有以下限制:● 第一个 SELECT 语句中的HIGH_PRIORITY没有效果。任何后续SELECT中的HIGH_PRIORITY都会产生语法错误。● 只有最后一条 SELECT 语句才能使用INTO子句。但是,整个UNION结果被写入到INTO输出目标。从 MySQL 8.0.20 开始,这两个包含 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 子句中使用聚合函数的 UNION查询,会引发ER_AGGREGATE_ORDER_FOR_UNION错误。例子:SELECT 1 AS foo UNION SELECT 2 ORDER BY MAX(1);
MySQL 8.0 与MySQL 5.7中的UNION处理比较在 MySQL 8.0 中,SELECT和 UNION的解析器规则被重构得更加统一(每个这样的上下文应用相同的 SELECT语法)并减少重复。与 MySQL 5.7相比,这项工作产生了几个用户可见的效果,可能需要重写某些语句:● NATURAL JOIN 允许使用一个可选的INNER关键字(NATURAL INNER JOIN),符合标准SQL。● 允许不带括号的右深连接(right-deep join)(例如... JOIN ... JOIN ... ON ... ON),符合标准SQL。● STRAIGHT_JOIN 现在允许使用USING子句,类似于其他内部连接。● 解析器接受查询表达式周围的括号。例如,允许使用 (SELECT ... UNION SELECT ...)。● 解析器更好地遵守规范的 SQL_CACHE 和SQL_NO_CACHE查询修饰符的位置。● 以前只允许在子查询中使用左侧联合嵌套语句,现在允许在顶层语句中使用。例如,现在允许以下语句:(SELECT 1 UNION SELECT 1) UNION SELECT 1;
● 只有在非 UNION 查询中才允许锁定子句(FOR UPDATE、LOCK IN SHARE MODE)。这意味着包含锁定子句的SELECT语句必须使用括号。此语句不再被视为有效:SELECT 1 FOR UPDATE UNION SELECT 1 FOR UPDATE;
相反,请这样写:(SELECT 1 FOR UPDATE) UNION (SELECT 1 FOR UPDATE);