Syntax:
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 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:

o 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);

o To enforce LIMIT on both query blocks and the entire query
  expression:

(SELECT 1 LIMIT 1) UNION (SELECT 2 LIMIT 1) LIMIT 1;

o To enforce LIMIT on the entire query expression (with no
  parentheses):

VALUES ROW(1), ROW(2) INTERSECT VALUES ROW(2), ROW(1) LIMIT 1;

o 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:

o A trailing INTO clause for a query expression is not permitted if
  there is another INTO clause inside parentheses.

o 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
  (https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference
  .html#error_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.

URL: https://dev.mysql.com/doc/refman/8.0/en/parenthesized-query-expressions.html

