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 useINTO OUTFILE
. (However, the entireUNION
result is written to the file.)HIGH_PRIORITY
cannot be used withSELECT
statements that are part of aUNION
. If you specify it for the firstSELECT
, it has no effect. If you specify it for any subsequentSELECT
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
. (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 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);
However, 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. Therefore, the use of ORDER
BY
in this context is typically in conjunction with
LIMIT
, so that it is used 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. 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;
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.