MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0
parenthesized_query_expression
: (query_expression
[order_by_clause
] [limit_clause
] ) [order_by_clause
] [limit_clause
] [into_clause
]query_expression
:query_block
[set_op
query_block
[set_op
query_block
...]] [order_by_clause
] [limit_clause
] [into_clause
]query_block
:SELECT
... |TABLE
|VALUES
order_by_clause
: ORDER BY as forSELECT
limit_clause
: LIMIT as forSELECT
into_clause
: INTO as forSELECT
set_op
:UNION
|INTERSECT
|EXCEPT
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
or other statement returning
a result set and no following optional clauses:
(SELECT 1); (SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'mysql'); TABLE t; VALUES ROW(2, 3, 4), ROW(1, -2, 3);
(Support for the TABLE
and
VALUES
statements is available
beginning with MySQL 8.0.19.)
A parenthesized query expression can also contain queries linked
by one or more set operations such as
UNION
, 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 | +------+
In addition to UNION
, the
INTERSECT
and EXCEPT
set
operators are available beginning with MySQL 8.0.31.
INTERSECT
acts before UNION
and EXCEPT
, so that the following two
statements are equivalent:
SELECT a FROM t1 EXCEPT SELECT b FROM t2 INTERSECT SELECT c FROM t3; SELECT a FROM t1 EXCEPT (SELECT b FROM t2 INTERSECT SELECT c FROM t3);
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:
(TABLE t1 ORDER BY a) UNION (TABLE t2 ORDER BY b) ORDER BY z;
Query blocks may have trailing ORDER BY
and
LIMIT
clauses, which are applied before the
outer set operation, 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 (VALUES ROW(2) LIMIT 1); (VALUES ROW(1), ROW(2) LIMIT 2) EXCEPT (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):
VALUES ROW(1), ROW(2) INTERSECT VALUES ROW(2), ROW(1) 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:
A trailing INTO
clause for a query
expression is not permitted if there is another
INTO
clause inside parentheses.
Prior to MySQL 8.0.31, when ORDER BY
or
LIMIT
occurred within a parenthesized query
expression and was also applied in the outer query, the result
was undefined. This is not an issue in MySQL 8.0.31 and later,
where this is handled in accordance with the SQL standard.
Prior to MySQL 8.0.31, parenthesized query expressions did not
permit multiple levels of ORDER BY
or
LIMIT
operations, and statements containing
these were rejected with
ER_NOT_SUPPORTED_YET
. In MySQL
8.0.31 and later, this restriction is lifted, and nested
parenthesized query expressions are permitted. The maximum
level of nesting supported is 63; this is after any
simplifications or merges have been performed by the parser.
An example of such a statement is shown here:
mysql> (SELECT 'a' UNION SELECT 'b' LIMIT 2) LIMIT 3;
+---+
| a |
+---+
| a |
| b |
+---+
2 rows in set (0.00 sec)
You should be aware that, in MySQL 8.0.31 and later, when
collapsing parenthesized expression bodies, MySQL follows SQL
standard semantics, so that a higher outer limit cannot
override an inner lower one. For example, (SELECT ...
LIMIT 5) LIMIT 10
can return no more than five rows.