MySQL :: MySQL 8.4 Reference Manual :: 15.2.11 Parenthesized Query Expressions

version 8.4

8.0 current

8.0 Japanese

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

15.2.11 括号查询表达式

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.4 支持根据前述语法的括号查询表达式。它的最简单形式是一个包含单个SELECT或其他返回结果集的语句以及没有后续可选子句:

(SELECT 1);
(SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'mysql');

TABLE t;

VALUES ROW(2, 3, 4), ROW(1, -2, 3);

一个括号查询表达式也可以包含通过一个或多个集合操作（如UNION）链接的查询，并以任何或所有可选子句结束:

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 |
+------+

INTERSECT 在 UNION 和 EXCEPT 之前执行，因此以下两个语句是等价的:

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

括号查询表达式也用于查询表达式，所以一个查询表达式，通常由查询块组成，也可能包含括号查询表达式:

(TABLE t1 ORDER BY a) UNION (TABLE t2 ORDER BY b) ORDER BY z;

查询块可以有尾随 ORDER BY 和 LIMIT 子句，这些子句在外部集合操作、ORDER BY 和 LIMIT 之前应用:

你不能有一个没有用括号包围的查询块带有尾随 ORDER BY 或 LIMIT 子句，但可以在多种方式中使用括号以强制执行:

为了对每个查询块应用 LIMIT:

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

(VALUES ROW(1), ROW(2) LIMIT 2) EXCEPT (SELECT 2 LIMIT 1);

为了对所有查询块和整个查询表达式应用 LIMIT:
```
(SELECT 1 LIMIT 1) UNION (SELECT 2 LIMIT 1) LIMIT 1;
```
为了在没有括号的情况下对整个查询表达式应用 LIMIT:
```
VALUES ROW(1), ROW(2) INTERSECT VALUES ROW(2), ROW(1) LIMIT 1;
```
混合强制执行：LIMIT 对第一个查询块和整个查询表达式有效:
```
(SELECT 1 LIMIT 1) UNION SELECT 2 LIMIT 1;
```

本节描述的语法受一定限制:

如果有另一个 INTO 子句在括号内，则不允许在查询表达式中使用尾随 INTO 子句。
ORDER BY 或 LIMIT 在括号内的查询表达式，也会按照 SQL 标准处理，在外部查询中应用。

允许嵌套的括号查询表达式。支持的最大嵌套级别为 63；这是在解析器对任何简化或合并操作之后执行的。

以下是一个这样的语句的例子:
```
mysql> (SELECT 'a' UNION SELECT 'b' LIMIT 2) LIMIT 3;
+---+
| a |
+---+
| a |
| b |
+---+
2 rows in set (0.00 sec)
```
你应该知道，当折叠括号表达式时，MySQL遵循 SQL 标准的语义，因此一个外部更高的限制不能覆盖内部较低的一个。例如，(SELECT ... LIMIT 5) LIMIT 10 可以返回不多于五行。

PREV HOME UP NEXT