parenthesized_query_expression:
( query_expression [order_by_clause] [limit_clause] )
[order_by_clause]
[limit_clause]
[into_clause]
query_expression:
query_block [UNION query_block [UNION query_block ...]]
[order_by_clause]
[limit_clause]
[into_clause]
query_block:
SELECT ... (see Section 13.2.10, “SELECT Statement”)
order_by_clause:
ORDER BY as for SELECT (see Section 13.2.10, “SELECT Statement”)
limit_clause:
LIMIT as for SELECT (see Section 13.2.10, “SELECT Statement”)
into_clause:
INTO as for SELECT (see Section 13.2.10, “SELECT Statement”)
MySQL 8.0.22 and higher supports parenthesized query expressions
according to the preceding syntax. At its simplest, a
parenthesized query expression contains a single
SELECT
and no following optional
clauses:
(SELECT 1);
(SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'mysql');
A parenthesized query expression can also contain a
UNION
comprising multiple
SELECT
statements, and end with
any or all of the optional clauses:
mysql> (SELECT 1 AS result UNION SELECT 2);
+--------+
| result |
+--------+
| 1 |
| 2 |
+--------+
mysql> (SELECT 1 AS result UNION SELECT 2) LIMIT 1;
+--------+
| result |
+--------+
| 1 |
+--------+
mysql> (SELECT 1 AS result UNION SELECT 2) LIMIT 1 OFFSET 1;
+--------+
| result |
+--------+
| 2 |
+--------+
mysql> (SELECT 1 AS result UNION SELECT 2)
ORDER BY result DESC LIMIT 1;
+--------+
| result |
+--------+
| 2 |
+--------+
mysql> (SELECT 1 AS result UNION SELECT 2)
ORDER BY result DESC LIMIT 1 OFFSET 1;
+--------+
| result |
+--------+
| 1 |
+--------+
mysql> (SELECT 1 AS result UNION SELECT 3 UNION SELECT 2)
ORDER BY result LIMIT 1 OFFSET 1 INTO @var;
mysql> SELECT @var;
+------+
| @var |
+------+
| 2 |
+------+
Parenthesized query expressions are also used as query expressions, so a query expression, usually composed of query blocks, may also consist of parenthesized query expressions:
(SELECT * FROM t1 ORDER BY a) UNION (SELECT * FROM t2 ORDER BY b) ORDER BY z;
Query blocks may have trailing ORDER BY
and
LIMIT
clauses, which are applied before the
outer UNION
and ORDER BY
and LIMIT
.
You cannot have a query block with a trailing ORDER
BY
or LIMIT
, without wrapping it in
parentheses, but parentheses may be used for enforcement in
various ways:
To enforce
LIMIT
on each query block:(SELECT 1 LIMIT 1) UNION (SELECT 2 LIMIT 1);
To enforce
LIMIT
on both query blocks and the entire query expression:(SELECT 1 LIMIT 1) UNION (SELECT 2 LIMIT 1) LIMIT 1;
To enforce
LIMIT
on the entire query expression (with no parentheses):SELECT 1 UNION SELECT 2 LIMIT 1;
Hybrid enforcement:
LIMIT
on the first query block and on the entire query expression:(SELECT 1 LIMIT 1) UNION SELECT 2 LIMIT 1;
The syntax described in this section is subject to certain restrictions:
If
ORDER BY
occurs within a parenthesized query expression and also is applied in the outer query, the results are undefined and may change in a future version of MySQL. The same is true ifLIMIT
occurs within a parenthesized query expression and also is applied in the outer query.A trailing
INTO
clause for a query expression is not permitted if there is anotherINTO
clause inside parentheses.Parenthesized query expressions do not permit multiple levels of
ORDER BY
orLIMIT
operations. For example:mysql> (SELECT 'a' UNION SELECT 'b' LIMIT 1) LIMIT 2; ERROR 1235 (42000): This version of MySQL doesn't yet support 'parenthesized query expression with more than one external level of ORDER/LIMIT operations'