Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 48.2Mb
PDF (A4) - 48.2Mb
PDF (RPM) - 43.8Mb
HTML Download (TGZ) - 11.0Mb
HTML Download (Zip) - 11.1Mb
HTML Download (RPM) - 9.5Mb
Man Pages (TGZ) - 239.8Kb
Man Pages (Zip) - 343.5Kb
Info (Gzip) - 4.4Mb
Info (Zip) - 4.4Mb
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:

  • 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 MySQL version. The same is true if LIMIT 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 another INTO clause inside parentheses.

  • Parenthesized query expressions do not permit multiple levels of ORDER BY or LIMIT 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'