![background image](http://html1.mh-extra.com/html/oracle/5-0/5-0_reference-manual_16461961117.webp)
SELECT
Syntax
1097
The
SELECT
statements are normal select statements, but with the following restrictions:
• Only the last
SELECT
statement can use
INTO OUTFILE
. (However, the entire
UNION
result is
written to the file.)
•
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 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 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;
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:
(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 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)
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...