Rechercher dans le manuel MySQL
13.2.10.3 UNION Syntax
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:
- +---------------+
- +---------------+
- | a |
- | bbbbbbbbbb |
- +---------------+
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 apply ORDER BY
or LIMIT
to an individual SELECT
, place
the clause inside the parentheses that enclose the
SELECT
:
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 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:
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
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:
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
:
To additionally maintain sort order within individual
SELECT
results, add a secondary
column to the ORDER BY
clause:
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.
UNION
queries with an aggregate
function in an ORDER BY
clause are rejected
with an
ER_AGGREGATE_ORDER_FOR_UNION
error. Example:
Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-union.html
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.