13.2.10.12 对子查询的限制_MySQL 5.7 参考手册

MySQL 8.0 参考手册 / 第 13 章 SQL 语句 / 13.2 数据操作语句 / 13.2.11 子查询 / 13.2.10.12 对子查询的限制

13.2.10.12 对子查询的限制

通常，您不能修改表并从子查询中的同一个表中进行选择。例如，此限制适用于以下形式的语句：
```
DELETE FROM t WHERE ... (SELECT ... FROM t ...);
UPDATE t ... WHERE col = (SELECT ... FROM t ...);
{INSERT|REPLACE} INTO t (SELECT ... FROM t ...);
```
例外：如果您正在使用派生表修改表并且该派生表是物化的而不是合并到外部查询中，则上述禁令不适用。（请参阅第 8.2.2.4 节，“使用合并或实现优化派生表和视图引用”。）示例：
```
UPDATE t ... WHERE col = (SELECT * FROM (SELECT ... FROM t...) AS dt ...);
```
这里派生表的结果具体化为临时表，因此在更新发生 t时已经选择了相关行。t
仅部分支持行比较操作：
- 对于, 可以是一个元组（使用行构造函数语法指定）并且子查询可以返回元组的行。因此，允许的语法更具体地表示为 expr [NOT] IN subqueryexprnnrow_constructor [NOT] IN table_subquery
- 对于, 必须是标量值且子查询必须是列子查询；它不能返回多列行。 expr op {ALL|ANY|SOME} subqueryexpr
换句话说，对于返回元组行的子查询 n，这是受支持的：
```
(expr_1, ..., expr_n) [NOT] IN table_subquery
```
但这不受支持：
```
(expr_1, ..., expr_n) op {ALL|ANY|SOME} subquery
```
支持行比较 IN但不支持其他行比较的原因IN是通过将其重写为一系列= 比较和AND操作来实现的。此方法不能用于ALL、 ANY或SOME。
Subqueries in the FROM clause cannot be correlated subqueries. They are materialized in whole (evaluated to produce a result set) during query execution, so they cannot be evaluated per row of the outer query. The optimizer delays materialization until the result is needed, which may permit materialization to be avoided. See Section 8.2.2.4, “Optimizing Derived Tables and View References with Merging or Materialization”.

MySQL does not support LIMIT in subqueries for certain subquery operators:

mysql> SELECT * FROM t1
       WHERE s1 IN (SELECT s2 FROM t2 ORDER BY s1 LIMIT 1);
ERROR 1235 (42000): This version of MySQL doesn't yet support
 'LIMIT & IN/ALL/ANY/SOME subquery'

MySQL permits a subquery to refer to a stored function that has data-modifying side effects such as inserting rows into a table. For example, if f() inserts rows, the following query can modify data:
```
SELECT ... WHERE x IN (SELECT f() ...);
```
This behavior is an extension to the SQL standard. In MySQL, it can produce nondeterministic results because f() might be executed a different number of times for different executions of a given query depending on how the optimizer chooses to handle it.
For statement-based or mixed-format replication, one implication of this indeterminism is that such a query can produce different results on the source and its replicas.