MySQL :: MySQL 5.7 Reference Manual :: 13.2.9.3 UNION Clause (original) (raw)

13.2.9.3 UNION Clause

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

UNION combines the result from multiple SELECT statements into a single result set. 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 |
+---+---+
Result Set Column Names and Data Types

The column names for a UNION result set are taken from the column names of the firstSELECT statement.

Selected columns listed in corresponding positions of eachSELECT statement 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 SELECT columns do not match, the types and lengths of the columns in theUNION result take into account the values retrieved by all theSELECT statements. For example, consider the following, where the column length is not constrained to the length of the value from the firstSELECT:

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

By default, duplicate rows are removed fromUNION results. The optionalDISTINCT 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 theSELECT statements.

You can mix UNION ALL and UNION DISTINCT in the same query. MixedUNION types are treated such that a DISTINCT union overrides anyALL union to its left. ADISTINCT union can be produced explicitly by using UNION DISTINCT or implicitly by usingUNION with no followingDISTINCT or ALL keyword.

ORDER BY and LIMIT in Unions

To apply an ORDER BY orLIMIT clause to an individualSELECT, parenthesize theSELECT and place the clause inside the parentheses:

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

Note

Previous versions of MySQL may permit such statements without parentheses. In MySQL 5.7, the requirement for parentheses is enforced.

Use of ORDER BY for individualSELECT 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. Therefore, ORDER BY in this context typically is used in conjunction withLIMIT, to determine the subset of the selected rows to retrieve for theSELECT, even though it does not necessarily affect the order of those rows in the finalUNION result. If ORDER BY appears without LIMIT in aSELECT, it is optimized away because it has no effect.

To use an ORDER BY orLIMIT clause to sort or limit the entireUNION result, parenthesize the individual SELECT statements and place the ORDER BY orLIMIT after the last one:

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

This kind of ORDER BY cannot use column references that include a table name (that is, names in_tblname.colname_ format). Instead, provide a column alias in the firstSELECT statement and refer to the alias in the 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. The first of the following statements is permitted, but the second fails with anUnknown 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 eachSELECT one after the other, select an additional column in eachSELECT 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 individualSELECT 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 whichSELECT 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 anER_AGGREGATE_ORDER_FOR_UNION error. Example:

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

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