集合操作结果的列名取自第一个查询块的列名。例如：The column names for the result of a set operation are taken from the column names of the first query block. Example:

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 查询都是如此。This is true for UNION, EXCEPT, and INTERSECT queries.

每个查询块中相应位置列出的选定列应具有相同的数据类型。例如，第一个语句选择的第一列应与其他语句选择的第一列类型相同。如果相应结果列的数据类型不匹配，则结果中的列类型和长度会考虑所有查询块检索的值。例如，结果集中的列长度不受第一个语句值长度的限制，如下所示：Selected columns listed in corresponding positions of each query block should have the same data type. 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 result columns do not match, the types and lengths of the columns in the result take into account the values retrieved by all of the query blocks. For example, the column length in the result set is not constrained to the length of the value from the first statement, as shown here:

mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',20);
+----------------------+
| REPEAT('a',1)        |
+----------------------+
| a                    |
| bbbbbbbbbbbbbbbbbbbb |
+----------------------+

你也可以使用 TABLE 语句，或者 VALUES 语句，任何可以使用等价的 SELECT 语句。假设表 t1 和 t2 都已创建并填充，如下所示：You can also use a TABLE statement or VALUES statement wherever you can employ the equivalent SELECT statement. Assume that tables t1 and t2 are created and populated as shown here:

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 查询均得相同结果：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:

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 语句，并使用别名，如：To force the column names to be the same, wrap the query block on the left-hand side in a SELECT statement, and use aliases, like this:

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 关键字，不会发生重复行的移除，结果包括并中所有查询中匹配的行。By default, duplicate rows are removed from results of set operations. The optional DISTINCT keyword has the same effect but makes it explicit. With the optional ALL keyword, duplicate-row removal does not occur and the result includes all matching rows from all queries in the union.

You can mix ALL以及DISTINCT in the same query. Mixed types are treated such that a set operation using DISTINCT overrides any such operation using ALL to its left. A DISTINCT set can be produced explicitly by using DISTINCT设置为UNION, INTERSECT，或EXCEPT, or implicitly by using the set operations with no following DISTINCT或者ALL keyword.You can mix ALL and DISTINCT in the same query. Mixed types are treated such that a set operation using DISTINCT overrides any such operation using ALL to its left. A DISTINCT set can be produced explicitly by using DISTINCT with UNION, INTERSECT, or EXCEPT, or implicitly by using the set operations with no following DISTINCT or ALL keyword.

当一个或多个 TABLE 语句， VALUES 语句，或两者同时生成集合时，集合操作的原理相同。Set operations work the same way when one or more TABLE statements, VALUES statements, or both, are used to generate the set.

。要对用于并集、交集或其他集合操作的单个查询块应用ORDER BY 或 LIMIT 子句，请在查询块内括号内，将子句置于括号内，如下：To apply an ORDER BY or LIMIT clause to an individual query block used as part of a union, intersection, or other set operation, parenthesize the query block, placing the clause inside the parentheses, like this:

(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，则该选项被优化去除，因为它无论如何都没有影响。Use of ORDER BY for individual query blocks or statements implies nothing about the order in which the rows appear in the final result because the rows produced by a set operation are by default unordered. Therefore, ORDER BY in this context typically is used in conjunction with LIMIT, to determine the subset of the selected rows to retrieve, even though it does not necessarily affect the order of those rows in the final result. If ORDER BY appears without LIMIT within a query block, it is optimized away because it has no effect in any case.

要使用 ORDER BY 或 LIMIT 子句对集合操作的整个结果进行排序或限制，请将 ORDER BY 或 LIMIT 放在最后一个语句之后：To use an ORDER BY or LIMIT clause to sort or limit the entire result of a set operation, place the ORDER BY or LIMIT after the last statement:

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 或两者，那么每个这样的独立语句必须用括号括起来。If one or more individual statements make use of ORDER BY, LIMIT, or both, and, in addition, you wish to apply an ORDER BY, LIMIT, or both to the entire result, then each such individual statement must be enclosed in parentheses.

(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 的结果。A statement with no ORDER BY or LIMIT clause does need to be parenthesized; replacing TABLE t2 with (TABLE t2) in the second statement of the two just shown does not alter the result of the UNION.

您还可以在集合操作中使用 ORDER BY 和 LIMIT 来对 VALUES 语句进行操作，如下面使用 mysql 客户端的示例所示：You can also use ORDER BY and LIMIT with VALUES statements in set operations, as shown in this example using the mysql client:

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 子句。）(You should keep in mind that neither TABLE statements nor VALUES statements accept a WHERE clause.)

这种类型的 ORDER BY 不能使用包含表名的列引用（即 tbl_name.col_name 格式的名称）。 取而代之的是，在第一个查询块中提供列别名，并在 ORDER BY 子句中引用该别名。（您也可以通过列的位置在 ORDER BY 子句中引用该列，但使用列位置的方法已被弃用，因此在未来的 MySQL 版本中可能会被移除。）This kind of ORDER BY cannot use column references that include a table name (that is, names in tbl_name.col_name format). Instead, provide a column alias in the first query block, and refer to the alias in the ORDER BY clause. (You can also refer to the column in the ORDER BY clause using its column position, but such use of column positions is deprecated, and thus subject to eventual removal in a future MySQL release.)

如果要排序的列有别名，则 ORDER BY 子句 必须 引用该别名，而不是列名。以下语句中，第一个是允许的，但第二个会报错 Unknown column 'a' in 'order clause'：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;

若要使 UNION 结果中的行按照每个查询块检索的行集合依次排列，请在每个查询块中选择一个额外的列作为排序列，并在最后一个查询块之后添加对该列进行排序的 ORDER BY 子句：To cause rows in a UNION result to consist of the sets of rows retrieved by each query block one after the other, select an additional column in each query block to use as a sort column and add an ORDER BY clause that sorts on that column following the last query block:

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

若要保持单个结果中的排序顺序，请在 ORDER BY 子句中添加第二列：To maintain sort order within individual 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 query block each row comes from. Extra columns can provide other identifying information as well, such as a string that indicates a table name.

MySQL 中的集合操作存在一些限制，下面几段将对此进行描述。Set operations in MySQL are subject to some limitations, which are described in the next few paragraphs.

包括 SELECT 语句的集合操作具有以下限制：Set operations including SELECT statements have the following limitations:

这两个包含 UNIONThese two UNION variants containing INTO are deprecated; you should expect support for them to be removed in a future version of MySQL:

在 ORDER BY被拒绝 ER_AGGREGATE_ORDER_FOR_UNION。虽然错误名称可能暗示这只限于UNION 查询，但上述内容同样适用于 EXCEPT 和 INTERSECT，如此处所示：Set operations with an aggregate function in an ORDER BY clause are rejected with ER_AGGREGATE_ORDER_FOR_UNION. Although the error name might suggest that this is exclusive to UNION queries, the preceding is also true for EXCEPT and INTERSECT queries, as shown here:

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 语句中，只有当查询块和锁定子句被括号包住时，锁定子句才可以使用。A locking clause (such as FOR UPDATE or LOCK IN SHARE MODE) applies to the query block it follows. This means that, in a SELECT statement used with set operations, a locking clause can be used only if the query block and locking clause are enclosed in parentheses.