MySQL 8.0-13.2.11 Subqueries

13.2.11.1 The Subquery as Scalar Operand

13.2.11.2 Comparisons Using Subqueries

13.2.11.3 Subqueries with ANY, IN, or SOME

13.2.11.4 Subqueries with ALL

13.2.11.5 Row Subqueries

13.2.11.6 Subqueries with EXISTS or NOT EXISTS

13.2.11.7 Correlated Subqueries

13.2.11.8 Derived Tables

13.2.11.9 Lateral Derived Tables

13.2.11.10 Subquery Errors

13.2.11.11 Optimizing Subqueries

13.2.11.12 Restrictions on Subqueries

A subquery is a SELECT statement within another statement.

子查询是另一条语句中的SELECT语句

All subquery forms and operations that the SQL standard requires are supported, as well as a few features that are MySQL-specific.

支持SQL标准要求的所有子查询表单和操作，以及一些特定于mysql的特性。

Here is an example of a subquery:

下面是子查询的一个例子:

SELECT * FROM t1 WHERE column1 = (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.

在这个例子中，SELECT * FROM t1…是外部查询(或外部语句)，(SELECT column1 FROM t2)是子查询。我们说子查询嵌套在外部查询中，实际上，可以将子查询嵌套在其他子查询中，并达到相当深的深度。子查询必须始终出现在括号中。

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.

• 它们提供了执行操作的替代方法，否则这些操作将需要复杂的连接和联合。

• 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称为“结构化查询语言”的最初想法。

Here is an example statement that shows the major points about subquery syntax as specified by the SQL standard and supported in MySQL:

下面是一个示例语句，它显示了关于SQL标准和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.

子查询可以返回标量(单个值)、单个行、单个列或表(一个或多个列的一行或多行)。这些查询称为标量、列、行和表子查询。返回特定类型结果的子查询通常只能在某些上下文中使用，如下节所述。

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.

对于可以使用子查询的语句类型有一些限制。子查询可以包含普通SELECT可以包含的许多关键字或子句:DISTINCT、GROUP BY、ORDER BY、LIMIT、join、索引提示、UNION构造、注释、函数等等。

Beginning with MySQL 8.0.19, 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:

从MySQL 8.0.19开始，TABLE和VALUES语句可以用于子查询。使用VALUES的子查询通常是更详细的子查询版本，可以使用集合表示法或SELECT或TABLE语法更紧凑地重写;假设表ts是用CREATE table ts VALUES ROW(2)， ROW(4)， ROW(6)语句创建的，这里显示的语句都是等价的:

SELECT * FROM tt WHERE b > ANY (VALUES ROW(2), ROW(4), ROW(6)); SELECT * FROM tt WHERE b > ANY (2, 4, 6); SELECT * FROM tt WHERE b > ANY (SELECT * FROM ts); SELECT * FROM tt WHERE b > ANY (TABLE ts);

Examples of TABLE subqueries are shown in the sections that follow.

下面几节将展示TABLE子查询的示例。

A subquery's outer statement can be any one of: SELECT, INSERT, UPDATE, DELETE, SET, or DO.

子查询的外部语句可以是:SELECT、INSERT、UPDATE、DELETE、SET或DO中的任意一个。

For information about how the optimizer handles subqueries, see Section 8.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 13.2.11.12, “Restrictions on Subqueries”.

关优化器如何处理子查询的信息，请参见8.2.2节“优化子查询、派生表、视图引用和公共表表达式”。有关子查询使用限制的讨论，包括某些形式的子查询语法的性能问题，请参见13.2.11.12节“子查询的限制”。