MySQL 8.4 Release Notes
- 15.2.15.1 子查询作为标量操作数15.2.15.1 The Subquery as Scalar Operand
- 15.2.15.2 使用子查询15.2.15.2 Comparisons Using Subqueries
- 比较15.2.15.3 带有 ANY、IN 或 SOME15.2.15.3 Subqueries with ANY, IN, or SOME
- 15.2.15.4 使用 ALL 的子查询15.2.15.4 Subqueries with ALL
- 15.2.15.5 行子查询15.2.15.5 Row Subqueries
- 15.2.15.6 带有 EXISTS 或 NOT EXISTS15.2.15.6 Subqueries with EXISTS or NOT EXISTS
- 15.2.15.7 相关子查询15.2.15.7 Correlated Subqueries
- 15.2.15.8 派生表15.2.15.8 Derived Tables
- 15.2.15.9 横向派生表15.2.15.9 Lateral Derived Tables
- 15.2.15.10 子查询错误<15.2.15.10 Subquery Errors
- 15.2.15.11 优化子查询15.2.15.11 Optimizing Subqueries
- 15.2.15.12 子查询限制15.2.15.12 Restrictions on Subqueries
子查询是 SELECT 语句。A subquery is a SELECT statement
within another statement.
支持 SQL 标准要求的所有子查询形式和操作，以及一些 MySQL 特有的功能。All subquery forms and operations that the SQL standard requires are supported, as well as a few features that are MySQL-specific.
下面是一个子查询的示例：Here is an example of a subquery:
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
在这个例子中，SELECT * FROM t1 ...是外部查询(或外部语句)，而(SELECT column1 FROM
t2)是子查询。我们说该子查询是嵌套的在外部查询中，实际上，可以在其他子查询中嵌套子查询，嵌套深度相当大。子查询必须始终出现在括号内。In this example, SELECT * FROM t1 ... is the
outer query (or outer
statement), and (SELECT column1 FROM
t2) is the subquery. We say that
the subquery is nested within the outer
query, and in fact it is possible to nest subqueries within other
subqueries, to a considerable depth. A subquery must always appear
within parentheses.
子查询的主要优点是：The main advantages of subqueries are:
它们允许查询被 结构化，从而可以隔离语句的每个部分。They allow queries that are structured so that it is possible to isolate each part of a statement.
它们提供了执行某些操作的替代方法，否则这些操作可能需要复杂的连接和联合。They provide alternative ways to perform operations that would otherwise require complex joins and unions.
许多人认为子查询比复杂的连接或联合更可读。事实上，正是子查询的创新使人们最初将早期的 SQL 称为“结构化查询语言”Many people find subqueries more readable than complex joins or unions. Indeed, it was the innovation of subqueries that gave people the original idea of calling the early SQL “Structured Query Language.”
下面是一个示例语句，显示了 SQL 标准规定的子查询语法的主要要点，并且在 MySQL 中得到支持：Here is an example statement that shows the major points about subquery syntax as specified by the SQL standard and supported in MySQL:
DELETE FROM t1
WHERE s11 > ANY
(SELECT COUNT(*) /* no hint */ FROM t2
WHERE NOT EXISTS
(SELECT * FROM t3
WHERE ROW(5*t2.s1,77)=
(SELECT 50,11*s1 FROM t4 UNION SELECT 50,77 FROM
(SELECT * FROM t5) AS t5)));
子查询可以返回标量（单个值）、单行、单列或表（一个或多行的一个或多个列）。这些分别称为标量、列、行和表子查询。返回特定类型结果的子查询通常只能在某些上下文中使用，如下节所述。A subquery can return a scalar (a single value), a single row, a single column, or a table (one or more rows of one or more columns). These are called scalar, column, row, and table subqueries. Subqueries that return a particular kind of result often can be used only in certain contexts, as described in the following sections.
对可以使用子查询的语句类型的限制很少。子查询可以包含普通 SELECT 可以包含的许多关键字或子句：DISTINCT、GROUP BY、ORDER BY、LIMIT、连接、索引提示、UNION 构造、注释、函数等等。There are few restrictions on the type of statements in which
subqueries can be used. A subquery can contain many of the
keywords or clauses that an ordinary
SELECT can contain:
DISTINCT, GROUP BY,
ORDER BY, LIMIT, joins,
index hints, UNION constructs,
comments, functions, and so on.
TABLE 和 VALUES 语句可以在子查询中使用。使用 VALUES 的子查询通常是可以用集合表示法或使用 SELECT 或 TABLE 语法更简洁地重写的更冗长版本；假设表 ts 是通过语句 CREATE TABLE
ts VALUES ROW(2), ROW(4), ROW(6) 创建的，这里显示的语句都是等效的：TABLE and
VALUES statements can be used in
subqueries. Subqueries using VALUES are
generally more verbose versions of subqueries that can be
rewritten more compactly using set notation, or with
SELECT or TABLE
syntax; assuming that table ts is created using
the statement
CREATE TABLE
ts VALUES ROW(2), ROW(4), ROW(6), the statements shown
here are all equivalent:
SELECT * FROM tt
WHERE b > ANY (VALUES ROW(2), ROW(4), ROW(6));
SELECT * FROM tt
WHERE b > ANY (SELECT * FROM ts);
SELECT * FROM tt
WHERE b > ANY (TABLE ts);
下面各节显示了 TABLE 子查询的示例。Examples of TABLE subqueries are
shown in the sections that follow.
子查询的外部语句可以是以下任意一种：SELECT、INSERT、UPDATE、DELETE、SET 或 DO。A subquery's outer statement can be any one of:
SELECT,
INSERT,
UPDATE,
DELETE,
SET, or
DO.
有关优化器如何处理子查询的信息，请参阅 第 10.2.2 节，“优化子查询、派生表、视图引用和公共表表达式”。有关子查询使用限制的讨论，包括某些形式的子查询语法的性能问题，请参阅 第 15.2.15.12 节，“子查询限制”。For information about how the optimizer handles subqueries, see Section 10.2.2, “Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions”. For a discussion of restrictions on subquery use, including performance issues for certain forms of subquery syntax, see Section 15.2.15.12, “Restrictions on Subqueries”.