Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 42.2Mb
PDF (A4) - 42.2Mb
Man Pages (TGZ) - 270.2Kb
Man Pages (Zip) - 381.3Kb
Info (Gzip) - 4.2Mb
Info (Zip) - 4.2Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  /  ...  /  Parenthesized Query Expressions

13.2.10.4 Parenthesized Query Expressions

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:

  • 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.