MySQL SELECT语法（四）UNION语法详解

　　源自MySQL 5.7 官方手册：13.2.9.3 UNION Syntax

 

一、UNION语法

　　UNION用于将多个SELECT语句的结果合并到一个结果集中。

SELECT ...
UNION [ALL | DISTINCT] SELECT ...
[UNION [ALL | DISTINCT] SELECT ...]

　　将会使用第一个SELECT语句中的列名称作为返回结果的列名称。而且在每个SELECT语句的相应位置中列出的选定列应具有相同的数据类型。

　　如果相应SELECT列的数据类型不匹配，则UNION结果中列的类型和长度会考虑所有SELECT语句检索的值。示例如下：

mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);

/*

+---------------+
| REPEAT('a',1) |
+---------------+
| a             |
| bbbbbbbbbb    |
+---------------+

*/

　　UNION涉及到的SELECT语句有如下限制：

• 只有最后一个SELECT语句可以使用INTO OUTFILE，当然，全部UNION的结果会写到文件中去。
• HIGH_PRIORITY在UNION语句中使用没有意义。因为对第一个SELECT使用，不产生什么影响。在其它的SELECT中使用，会报错。

 

　　UNION的默认会从结果中删除重复的行，所以DINSTINCT关键字用不用都一样。而ALL关键字就可以让结果包含所有符合条件的重复行。

　　可以在同一查询中混合使用UNION ALL和UNION DISTINCT。MySQL对此的处理策略是：一个DISTINCT UNION句覆盖它其左侧的任何ALL UINON句。

　　

　　要将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);

　　在5.7中，括号是必须的。

 

　　对UNION中的单个SELECT语句使用ORDER BY并不影响行在最终结果中出现的顺序，因为UNION默认生成一组无序行。因此，在此上下文中使用ORDER BY通常与LIMIT结合使用来获取一个子集。

　　如果在SELECT中没有LIMIT出现ORDER BY，它会被优化掉，因为它无论如何都不会产生任何影响。

　　若要对整个UNION的结果使用ORDER BY 或 LIMIT，使用括号把SELECT子句括起来，然后在最后一个SELECT子句后使用他们。如下：

(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;

　　这里如果不使用括号，他们估计会被应用在最后一个SELECT子句上。

　　对UNION结果使用的ORDER BY，不能使用tb1_name.col_name形式的列引用。而是在第一个SELECT语句中提供列别名，并引用ORDER BY中的别名

　　此外，如果要进行排序的列拥有别名，则ORDER BY子句必须引用别名，而不是列名。下面的第一个条SQL会正常运行，但是第二条SQL会失败报错：Unknow 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;

　　然后可以继续在ORDER BY后添加辅助排序列了，这意味着来自同一个SELECT语句的数据行按辅助排序行进行排序：

(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1)
UNION
(SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col, col1a;

　　附加列也可以提供其他标识信息，例如表名的字符串。

 

　　在ORDER BY子句中使用聚合函数的UNION查询将被拒绝，并报出ER_AGGREGATE_ORDER_FOR_UNION错误。