SELECT ...
UNION [ALL | DISTINCT] SELECT ...
[UNION [ALL | DISTINCT] SELECT ...]
UNION is used to combine the result from
multiple SELECT statements into a single
result set.
The column names from the first SELECT
statement are used as the column names for the results
returned. Selected columns listed in corresponding positions
of each SELECT 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
the UNION result take into account the
values retrieved by all of the SELECT
statements. For example, consider the following:
mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);
+---------------+
| REPEAT('a',1) |
+---------------+
| a |
| bbbbbbbbbb |
+---------------+
(In some earlier versions of MySQL, only the type and length
from the first SELECT would have been used
and the second row would have been truncated to a length of
1.)
The SELECT statements are normal select
statements, but with the following restrictions:
Only the last SELECT statement can use
INTO OUTFILE.
HIGH_PRIORITY cannot be used with
SELECT statements that are part of a
UNION. If you specify it for the first
SELECT, it has no effect. If you
specify it for any subsequent SELECT
statements, a syntax error results.
The default behavior for UNION is that
duplicate rows are removed from the result. The optional
DISTINCT keyword has no effect other than
the default because it also specifies duplicate-row removal.
With the optional ALL keyword,
duplicate-row removal does not occur and the result includes
all matching rows from all the SELECT
statements.
You can mix UNION ALL and UNION
DISTINCT in the same query. Mixed
UNION types are treated such that a
DISTINCT union overrides any
ALL union to its left. A
DISTINCT union can be produced explicitly
by using UNION DISTINCT or implicitly by
using UNION with no following
DISTINCT or ALL keyword.
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. The following example uses both clauses:
(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;
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
SELECT statement and refer to the alias in
the ORDER BY, or else refer to the column
in the ORDER BY using its column position.
(An alias is preferable because 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 will work, but the second will fail 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 apply ORDER BY or
LIMIT to an individual
SELECT, place the clause inside the
parentheses that enclose the 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. If ORDER BY appears with
LIMIT, it is used to determine the subset
of the selected rows to retrieve for the
SELECT, but does not necessarily affect the
order of those rows in the final UNION
result. If ORDER BY appears without
LIMIT in a SELECT, it is
optimized away because it will have no effect anyway.
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 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;
