MySQL :: MySQL 8.0 Reference Manual :: 15.2.11 Parenthesized Query Expressions (original) (raw)

15.2.11 Parenthesized Query Expressions

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 for SELECT

limit_clause:
    LIMIT as for SELECT

into_clause:
    INTO as for SELECT

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 singleSELECT 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 andVALUES 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 asUNION, 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, theINTERSECT 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 andLIMIT clauses, which are applied before the outer set operation, ORDER BY, andLIMIT.

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 anotherINTO clause inside parentheses.
Prior to MySQL 8.0.31, when ORDER BY orLIMIT 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 orLIMIT operations, and statements containing these were rejected withER_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.