SELECT ... UNION [ALL | DISTINCT] SELECT ... [UNION [ALL | DISTINCT] SELECT ...]
UNION combines the result from multiple SELECT statements into a single result set. UNION将多个SELECT语句的结果合并到一个结果集中。Example:例如：
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 | +---+---+
The column names for a UNION result set are taken from the column names of the first SELECT statement.UNION结果集的列名取自第一条SELECT语句的列名。
Selected columns listed in corresponding positions of each 在每个SELECT statement should have the same data type. SELECT语句的相应位置列出的选定列应具有相同的数据类型。For example, the first column selected by the first statement should have the same type as the first column selected by the other statements. 例如，第一条语句选择的第一列应该与其他语句选择的第一列具有相同的类型。If the data types of corresponding 如果相应SELECT columns do not match, the types and lengths of the columns in the UNION result take into account the values retrieved by all the SELECT statements. SELECT列的数据类型不匹配，UNION结果中列的类型和长度将考虑所有SELECT语句检索到的值。For example, consider the following, where the column length is not constrained to the length of the value from the first 例如，考虑以下情况，其中列长度不受来自第一个SELECT:SELECT的值的长度的约束：
mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',20);
+----------------------+
| REPEAT('a',1) |
+----------------------+
| a |
| bbbbbbbbbbbbbbbbbbbb |
+----------------------+
Beginning with MySQL 8.0.19, you can also use a 从MySQL 8.0.19开始，您还可以在TABLE statement or VALUES statement in a UNION wherever you can employ the equivalent SELECT statement. UNION中使用TABLE语句或VALUES语句，只要您可以使用等效的SELECT语句。Assume that tables 假设表t1 and t2 are created and populated as shown here: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);
The preceding being the case, and disregarding the column names in the output of the queries beginning with 在前面的情况下，忽略以VALUES, all of the following UNION queries yield the same result:VALUES开头的查询输出中的列名，以下所有联合查询都会产生相同的结果：
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);
To force the column names to be the same, wrap the 要强制列名相同，请将左侧的VALUES on the left hand side in a SELECT and use aliases, like this: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);
By default, duplicate rows are removed from 默认情况下，将从UNION results. UNION结果中删除重复的行。The optional 可选的DISTINCT keyword has the same effect but makes it explicit. DISTINCT关键字具有相同的效果，但使其显式。With the optional 使用可选的ALL keyword, duplicate-row removal does not occur and the result includes all matching rows from all the SELECT statements.ALL关键字时，不会删除重复行，并且结果包括所有SELECT语句中的所有匹配行。
You can mix 可以在同一查询中混合使用UNION ALL and UNION DISTINCT in the same query. UNION ALL和UNION DISTINCT。Mixed 混合UNION types are treated such that a DISTINCT union overrides any ALL union to its left. UNION类型的处理方式是，不同的联合会覆盖其左侧的所有联合。A 可以通过使用DISTINCT union can be produced explicitly by using UNION DISTINCT or implicitly by using UNION with no following DISTINCT or ALL keyword.UNION DISTINCT显式生成DISTINCT联合，也可以通过使用UNION（不带DISTINCT或ALL关键字）隐式生成DISTINCT联合。
In MySQL 8.0.19 and later, 在MySQL8.0.19及更高版本中，UNION ALL and UNION DISTINCT work the same way when one or more TABLE statements are used in the union.UNION ALL和UNION DISTINCT在UNION中使用一个或多个TABLE语句时的工作方式相同。
To apply an 要将ORDER BY or LIMIT clause to an individual SELECT, parenthesize the SELECT and place the clause inside the parentheses: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);
Use of 对单个ORDER BY for individual SELECT statements implies nothing about the order in which the rows appear in the final result because UNION by default produces an unordered set of rows. SELECT语句使用ORDER BY并不表示行在最终结果中的显示顺序，因为UNION在默认情况下会生成一组无序的行。Therefore, 因此，在这种情况下，ORDER BY in this context typically is used in conjunction with LIMIT, to determine the subset of the selected rows to retrieve for the SELECT, even though it does not necessarily affect the order of those rows in the final UNION result. ORDER BY通常与LIMIT一起使用，以确定要为SELECT检索的选定行的子集，即使它不一定影响这些行在最终联合结果中的顺序。If 如果ORDER BY appears without LIMIT in a SELECT, it is optimized away because it has no effect in any case.ORDER BY在SELECT中没有限制地出现，它将被优化掉，因为它在任何情况下都没有效果。
To use an 要使用ORDER BY or LIMIT clause to sort or limit the entire UNION result, parenthesize the individual SELECT statements and place the ORDER BY or LIMIT after the last one: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;
A statement without parentheses is equivalent to one parenthesized as just shown.没有括号的语句相当于刚才显示的带括号的语句。
Beginning with MySQL 8.0.19, you can use 从MySQL8.0.19开始，您可以在联合中使用ORDER BY and LIMIT with TABLE in unions in the same way as just shown, bearing in mind that TABLE does not support a WHERE clause.ORDER BY和LIMIT配合TABLE，方法与刚才所示的相同，请记住TABLE不支持WHERE子句。
This kind of 这种ORDER BY cannot use column references that include a table name (that is, names in tbl_name.col_name format). ORDER BY不能使用包含表名的列引用（即tbl_name.col_name格式的名称）。Instead, provide a column alias in the first 相反，请在第一个SELECT statement and refer to the alias in the ORDER BY. SELECT语句中提供列别名，并在ORDER BY中引用别名。(Alternatively, refer to the column in the （或者，使用列位置引用顺序中的列。ORDER BY using its column position. However, use of column positions is deprecated.)但是，不推荐使用列位置。）
Also, if a column to be sorted is aliased, the 另外，如果要排序的列具有别名，ORDER BY clause must refer to the alias, not the column name. ORDER BY子句必须引用别名，而不是列名。The first of the following statements is permitted, but the second fails with an 允许使用以下语句中的第一个语句，但第二个语句失败，出现“order子句”错误中的未知列“a”：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:UNION结果中的行由每个SELECT逐个检索的行集组成，请在每个SELECT中选择一个附加列作为排序列，并添加一个ORDER BY，在最后一个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结果中另外维护排序顺序，请向ORDER BY子句中添加第二列：
(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. SELECT。Extra columns can provide other identifying information as well, such as a string that indicates a table name.额外的列还可以提供其他标识信息，例如表示表名的字符串。
In a 在UNION, the SELECT statements are normal select statements, but with the following restrictions:UNION中，SELECT语句是正常的选择语句，但有以下限制：
HIGH_PRIORITY in the first SELECT has no effect. SELECT中的HIGH_PRIORITY没有影响。HIGH_PRIORITY in any subsequent SELECT produces a syntax error.SELECT中的HIGH_PRIORITY都会产生语法错误。
Only the last 只有最后一条SELECT statement can use an INTO clause. SELECT语句才能使用INTO子句。However, the entire 但是，整个UNION result is written to the INTO output destination.UNION结果将写入到INTO输出目标。
As of MySQL 8.0.20, these two 从MySQL 8.0.20开始，这两个包含UNION variants containing INTO are deprecated and you should expect support for them to be removed in a future version of MySQL:INTO的UNION变体已被弃用，您应该希望在将来的MySQL版本中删除对它们的支持：
In the trailing query block of a query expression, use of 在查询表达式的尾部查询块中，在INTO before FROM produces a warning. FROM之前使用INTO会产生警告。Example:例如：
... UNION SELECT * INTO OUTFILE 'file_name' FROMtable_name;
In a parenthesized trailing block of a query expression, use of 在查询表达式的带圆括号的尾部块中，使用INTO (regardless of its position relative to FROM) produces a warning. INTO（不管其相对于FROM的位置）会产生警告。Example:例如：
... UNION (SELECT * INTO OUTFILE 'file_name' FROMtable_name);
Those variants are deprecated because they are confusing, as if they collect information from the named table rather than the entire query expression (the 不推荐使用这些变体，因为它们很混乱，就像它们从命名表而不是整个查询表达式（UNION).UNION）收集信息一样。
UNION queries with an aggregate function in an ORDER BY clause are rejected with an ER_AGGREGATE_ORDER_FOR_UNION error. ORDER BY子句中包含聚合函数的UNION查询将被拒绝，并出现ER_AGGREGATE_ORDER_FOR_UNION错误。Example:例如：
SELECT 1 AS foo UNION SELECT 2 ORDER BY MAX(1);
In MySQL 8.0, the parser rules for 在MySQL 8.0中，SELECT and UNION were refactored to be more consistent (the same SELECT syntax applies uniformly in each such context) and reduce duplication. SELECT和UNION的解析器规则被重构为更加一致（相同的SELECT语法在每个这样的上下文中统一应用）并减少重复。Compared to MySQL 5.7, several user-visible effects resulted from this work, which may require rewriting of certain statements:与MySQL5.7相比，这项工作产生了一些用户可见的效果，可能需要重写某些语句：
NATURAL JOIN permits an optional INNER keyword (NATURAL INNER JOIN), in compliance with standard SQL.NATURAL JOIN允许一个可选的INNER关键字（NATURAL INNER JOIN），符合标准SQL。
Right-deep joins without parentheses are permitted (for example, 允许不带括号的右深联接（例如... JOIN ... JOIN ... ON ... ON), in compliance with standard SQL.... JOIN ... JOIN ... ON ... ON），符合标准SQL。
STRAIGHT_JOIN now permits a USING clause, similar to other inner joins.STRAIGHT_JOIN现在允许USING子句。
The parser accepts parentheses around query expressions. 解析器接受查询表达式周围的括号。For example, 例如，(SELECT ... UNION SELECT ...) is permitted. (SELECT ... UNION SELECT ...)是允许的。See also Section 13.2.10.4, “Parenthesized Query Expressions”.另请参见第13.2.10.4节，“带圆括号的查询表达式”。
The parser better conforms to the documented permitted placement of the 解析器更好地符合文档中允许放置的SQL_CACHE and SQL_NO_CACHE query modifiers.SQL_CACHE和SQL_NO_CACHE查询修饰符。
Left-hand nesting of unions, previously permitted only in subqueries, is now permitted in top-level statements. 以前只允许在子查询中左嵌套联合，现在允许在顶级语句中左嵌套联合。For example, this statement is now accepted as valid:例如，此语句现在被接受为有效：
(SELECT 1 UNION SELECT 1) UNION SELECT 1;
Locking clauses (锁定子句（FOR UPDATE, LOCK IN SHARE MODE) are allowed only in non-UNION queries. FOR UPDATE，LOCK IN SHARE MODE）只允许在非UNION查询中使用。This means that parentheses must be used for 这意味着包含锁定子句的SELECT statements containing locking clauses. SELECT语句必须使用括号。This statement is no longer accepted as valid:此声明不再被视为有效：
SELECT 1 FOR UPDATE UNION SELECT 1 FOR UPDATE;
Instead, write the statement like this:而是要这样写：
(SELECT 1 FOR UPDATE) UNION (SELECT 1 FOR UPDATE);