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

UNION DISTINCT 和 UNION ALL

默认情况下，重复行会从 UNION结果中删除。optional DISTINCT关键字具有相同的效果，但使其显式化。使用可选ALL 关键字，不会发生重复行删除，结果包括所有 SELECT语句中的所有匹配行。

您可以在同一个查询 中混合使用UNION ALL和。UNION DISTINCT处理混合 UNION类型时，DISTINCT联合会覆盖 ALL其左侧的任何联合。DISTINCT联合可以通过 using 显式生成，也可以通过不 带任何后续 关键字或关键字 的 usingUNION DISTINCT隐式 生成。UNIONDISTINCTALL

联合中的 ORDER BY 和 LIMIT

要将ORDER BYor LIMIT子句应用于 individual SELECT， SELECT请将 the 括起来并将子句放在括号内：

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

使用ORDER BYfor 单个 SELECT语句并不意味着行在最终结果中出现的顺序，因为UNION默认情况下会生成一组无序的行。因此，ORDER BY 在此上下文中通常与 结合使用 LIMIT来确定要为 检索的所选行的子集 SELECT，即使它不一定会影响这些行在最终 UNION结果中的顺序。如果ORDER BY没有出现LIMIT在 a 中 SELECT，它会被优化掉，因为它没有效果。

要使用ORDER BYor LIMIT子句对整个 UNION结果进行排序或限制，请将各个SELECT语句括起来并将ORDER BYor 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;

没有括号的语句等同于刚才显示的带括号的语句。

这种ORDER BY不能使用包含表名（即 tbl_name.col_name 格式的名称）的列引用。SELECT相反，在第一条语句中提供列别名 并在ORDER BY. （或者，在ORDER BY使用其列位置中引用该列。但是，不推荐使用列位置。）

Also, if a column to be sorted is aliased, the ORDER BY clause must refer to the alias, not the column name. The first of the following statements is permitted, but the second fails with an Unknown column 'a' in 'order clause' error:

(SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY b;
(SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY a;

To cause rows in a UNION result to consist of the sets of rows retrieved by each SELECT one after the other, select an additional column in each SELECT to use as a sort column and add an ORDER BY that sorts on that column following the last SELECT:

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

To additionally maintain sort order within individual SELECT results, add a secondary column to the ORDER BY clause:

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

Use of an additional column also enables you to determine which SELECT each row comes from. Extra columns can provide other identifying information as well, such as a string that indicates a table name.

UNION queries with an aggregate function in an ORDER BY clause are rejected with an ER_AGGREGATE_ORDER_FOR_UNION error. Example:

SELECT 1 AS foo UNION SELECT 2 ORDER BY MAX(1);

UNION Restrictions

In a UNION, the SELECT statements are normal select statements, but with the following restrictions: