MySQL 5.1参考手册 :: 13. SQL语句语法(4)

13.2.8. Subquery语法

13.2.8.1. 子查询作为标量操作数

13.2.8.2. 使用子查询进行比较

13.2.8.3. 使用ANY, IN和SOME进行子查询

13.2.8.4. 使用ALL进行子查询

13.2.8.5. 行子查询

13.2.8.6. EXISTS和NOT EXISTS

13.2.8.7. 关联子查询

13.2.8.8. FROM子句中的子查询

13.2.8.9. 子查询错误

13.2.8.10. 优化子查询

13.2.8.11. 把子查询作为用于早期MySQL版本的联合进行改写

子查询是另一个语句中的一个SELECT语句。

MySQL支持SQL标准要求的所有子查询格式和操作，也支持MySQL特有的几种特性。

以下是一个子查询的例子：

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

在本例中，SELECT * FROM t1...是外部查询（或外部语句）,（SELECT column1 FROM t2）是子查询。我们可以说子查询嵌套在外部查询中。实际上，子查询也可以嵌套在其它子查询中，嵌套程度可以很深。子查询必须要位于圆括号中。

子查询的主要优势为：

·         子查询允许结构化的查询，这样就可以把一个语句的每个部分隔离开。

·         有些操作需要复杂的联合和关联。子查询提供了其它的方法来执行这些操作。

·         在许多人看来，子查询是可读的。实际上，子查询给予人们调用早期SQL“结构化查询语言”的原本的想法，这是子查询的创新。

以下是一个示例语句。该语句显示了有关子查询语法的要点。子查询语法由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)));

一个子查询会返回一个标量（单一值）、一个行、一个列或一个表（一行或多行及一列或多列）。这些子查询被称为标量、列、行和表子查询。可返回一个特定种类结果的子查询经常只被用于特定的语境中，在后面各节中有说明。

有些语句可以使用子查询。对这些语句的类型基本没有限定。子查询可以包括普通SELECT可以包括的任何关键词或子句：DISTINCT, GROUP BY, ORDER BY, LIMIT, 联合, 索引提示, UNION结构化, 评注和函数等。

有一个限定是，一个子查询的外部语句必须是以下语句之一：SELECT, INSERT, UPDATE, DELETE, SET或DO。还有一个限定是，目前，您不能在一个子查询中修改一个表，又在同一个表中选择。这适用于DELETE, INSERT, REPLACE和UPDATE语句。在附录I：特性限制中给出了对子查询使用的更综合的讨论。

13.2.8.1. 子查询作为标量操作数

子查询最简单的形式是返回单一值的标量子查询。标量子查询是一个单一操作数。只要单一列值或文字是合法的，并且您希望子查询具有所有操作数都具有的特性，则您就可以使用子查询。操作数具有的特性包括：一个数据类型、一个长度、一个指示是否可以为NULL的标志等。举例说明：

CREATE TABLE t1 (s1 INT, s2 CHAR(5) NOT NULL);

INSERT INTO t1 VALUES(100, 'abcde');

SELECT (SELECT s2 FROM t1);

在本SELECT中的子查询返回一个单一值('abcde')。该单一值的数据类型为CHAR，长度为5，字符集和整序与在CREATE TABLE时有效的默认值相同，并有一个指示符号，指示列中的值可以为NULL。实际上，基本上所有的子查询都为NULL。如果在本例中使用的表为空表，则子查询的值应为NULL。

在有些情况下，标量子查询不能使用。如果一个语句只允许一个文字值，您不能使用子查询。例如，LIMIT要求文字整数自变量，LOAD DATA要求一个文字字符串文件名。您不能使用子查询来提供这些值。

后面各节包括更简练的结构（SELECT column1 FROM t1）。当您在这些章节中观看例子时，请设想一下您自己的代码包含更多样、更复杂的结构。

举例说明，假设我们制作两个表：

CREATE TABLE t1 (s1 INT);

INSERT INTO t1 VALUES (1);

CREATE TABLE t2 (s1 INT);

INSERT INTO t2 VALUES (2);

然后执行一个SELECT：

SELECT (SELECT s1 FROM t2) FROM t1;

结果为2，因为t2中有一行包含s1，s1有一个值为2。

一个标量子查询可以为一个表达式的一部分。不要忘记圆括号。即使是子查询是一个为函数提供自变量的操作数时，也不要忘记圆括号。举例说明：

SELECT UPPER((SELECT s1 FROM t1)) FROM t2;

13.2.8.2. 使用子查询进行比较

子查询最常见的一种使用方式如下：

non_subquery_operand comparison_operator (subquery)

当comparison_operator是以下 操作符之一时：

=  >  <  >=  <=  <>

例如：

  ... 'a' = (SELECT column1 FROM t1)

有时，子查询的合法位置只能在比较式的右侧，您可以发现，在有些旧的DBMSs中仍保持这一点。

以下是一个常见格式的子查询比较的例子。您不能使用联合进行此类比较。表t1中有些值与表t2中的最大值相同。该比较可以查找出所有这类值：

SELECT column1 FROM t1

WHERE column1 = (SELECT MAX(column2) FROM t2);

下面还有另一个例子，该例子也不可能使用联合，因为该例子涉及对其中一个表进行总计。表t1中的有些行含有的值会在给定的列中出现两次。该例子可以查找出所有这些行：

SELECT * FROM t1 AS t

WHERE 2 = (SELECT COUNT(*) FROM t1 WHERE t1.id = t.id);

对于采用这些操作符之一进行的比较，子查询必须返回一个标量。有一个例外，那就是＝可以和行子查询同时使用。请参见13.2.8.5节，“行子查询”。

13.2.8.3. 使用ANY, IN和SOME进行子查询

语法：

operand comparison_operator ANY (subquery)

operand IN (subquery)

operand comparison_operator SOME (subquery)

ANY关键词必须后面接一个比较操作符。ANY关键词的意思是“对于在子查询返回的列中的任一数值，如果比较结果为TRUE的话，则返回TRUE”。例如：

SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);

假设表t1中有一行包含（10）。如果表t2包含（21，14，7），则表达式为TRUE，因为t2中有一个值为7，该值小于10。如果表t2包含（20，10），或者如果表t2为空表，则表达式为FALSE。如果表t2包含（NULL, NULL, NULL），则表达式为UNKNOWN。

词语IN是＝ANY的别名。因此，这两个语句是一样的：

SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);

SELECT s1 FROM t1 WHERE s1 IN    (SELECT s1 FROM t2);

不过，NOT IN不是<> ANY的别名，但是是<> ALL的别名。请参见13.2.8.4节，“使用ALL进行子查询”。

词语SOME是ANY的别名。因此，这两个语句是一样的：

SELECT s1 FROM t1 WHERE s1 <> ANY  (SELECT s1 FROM t2);

SELECT s1 FROM t1 WHERE s1 <> SOME (SELECT s1 FROM t2);

使用词语SOME的机会很少，但是本例显示了为什么SOME是有用的。对于多数人来说，英语短语“a is not equal to any b”的意思是“没有一个b与a相等”，但是在SQL语法中不是这个意思。该语法的意思是“有部分b与a不相等”。使用<> SOME有助于确认每个人都理解该查询的真正含义。

13.2.8.4. 使用ALL进行子查询

语法：

operand comparison_operator ALL (subquery)

词语ALL必须接在一个比较操作符的后面。ALL的意思是“对于子查询返回的列中的所有值，如果比较结果为TRUE，则返回TRUE。”例如：

SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s1 FROM t2);

假设表1中有一行包含（10）。如果表t2包含（-5，0，＋5），则表达式为TRUE，因为10比t2中的所有三个值都大。如果表t2包含（12，6，NULL，－100），则表达式为FALSE，因为表t2中有一个值12大于10。如果表t2包含（0，NULL，1），则表达式为unknown。

最后，如果表t2为空表，则结果为TRUE。因此，当表t2为空表时，以下语句为TRUE：

SELECT * FROM t1 WHERE 1 > ALL (SELECT s1 FROM t2);

但是，当表t2为空表时，本语句为NULL：

SELECT * FROM t1 WHERE 1 > (SELECT s1 FROM t2);

另外，当表t2为空表时，以下语句为NULL：

SELECT * FROM t1 WHERE 1 > ALL (SELECT MAX(s1) FROM t2);

通常，包含NULL值的表和空表为“边缘情况”。当编写子查询代码时，都要考虑您是否把这两种可能性计算在内。

NOT IN是<> ALL的别名。因此，以下两个语句是相同的：

SELECT s1 FROM t1 WHERE s1 <> ALL (SELECT s1 FROM t2);

SELECT s1 FROM t1 WHERE s1 NOT IN (SELECT s1 FROM t2);

13.2.8.5. 行子查询

对于本点的讨论属于标量或列子查询，即返回一个单一值或一列值的子查询。行子查询是一个能返回一个单一行的子查询变量，因此可以返回一个以上的列值。以下是两个例子：

SELECT * FROM t1 WHERE (1,2) = (SELECT column1, column2 FROM t2);

SELECT * FROM t1 WHERE ROW(1,2) = (SELECT column1, column2 FROM t2);

如果在表t2的一个行中，column1=1并且column2=2，则查询结果均为TRUE。

表达式（1，2）和ROW（1，2）有时被称为行构造符。两者是等同的，在其它的语境中，也是合法的。例如，以下两个语句在语义上是等同的（但是目前只有第二个语句可以被优化）：

  SELECT * FROM t1 WHERE (column1,column2) = (1,1);

  SELECT * FROM t1 WHERE column1 = 1 AND column2 = 1;

行构造符通常用于与对能返回两个或两个以上列的子查询进行比较。例如，以下查询可以答复请求，“在表t1中查找同时也存在于表t2中的所有的行”：

SELECT column1,column2,column3

FROM t1

WHERE (column1,column2,column3) IN

(SELECT column1,column2,column3 FROM t2);

13.2.8.6. EXISTS和NOT EXISTS

如果一个子查询返回任何的行，则EXISTS subquery为FALSE。例如：

SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);

过去，EXISTS子查询以SELECT *为开始，但是可以以SELECT 5或SELECT column1或其它的为开始。MySQL在这类子查询中忽略了SELECT清单，因此没有区别。

对于前面的例子，如果t2包含任何行，即使是只含有NULL值的行，EXISTS条件也为TRUE。这实际上是一个不可能的例子，因为基本上所有的[NOT] EXISTS子查询均包含关联。以下是一些更现实的例子：

·         哪些种类的商店出现在一个或多个城市里？

·                SELECT DISTINCT store_type FROM stores

·                  WHERE EXISTS (SELECT * FROM cities_stores

·                                WHERE cities_stores.store_type = stores.store_type);

·         哪些种类的商店没有出现在任何城市里？

·                SELECT DISTINCT store_type FROM stores

·                  WHERE NOT EXISTS (SELECT * FROM cities_stores

·                                    WHERE cities_stores.store_type = stores.store_type);

·         哪些种类的商店出现在所有城市里？

·                SELECT DISTINCT store_type FROM stores s1

·                  WHERE NOT EXISTS (

·                    SELECT * FROM cities WHERE NOT EXISTS (

·                      SELECT * FROM cities_stores

·                       WHERE cities_stores.city = cities.city

·                       AND cities_stores.store_type = stores.store_type));

最后一个例子是一个双嵌套NOT EXISTS查询。也就是，该查询包含一个NOT EXISTS子句，该子句又包含在一个NOT EXISTS子句中。该查询正式地回答了这个问题，“是否有某个城市拥有没有列在Stores中的商店？”。可以比较容易的说，一个带嵌套的NOT EXISTS可以回答这样的问题，“是否对于所有的y，x都为TRUE？”

13.2.8.7. 关联子查询

相关联的子查询是一个包含对表的引用的子查询。该表也显示在外部查询中。例如：

SELECT * FROM t1 WHERE column1 = ANY

(SELECT column1 FROM t2 WHERE t2.column2 = t1.column2);

注意，即使子查询的FROM子句不提及表t1，该子查询也会包含一个对t1中一列的引用。所以，MySQL看上去位于子查询的外部，并在外部查询中查找t1。

假设表t1包含一行，在此行中column1=5并且column2=6；同时，表t2包含一行，在此行中column1=5并且column2=7。简单的表达式... WHERE column1 = ANY (SELECT column1 FROM t2)会为TRUE。但是在本例中，在子查询中的WHERE子句为FALSE（因为（5，6）不等于（5，7）），所以子查询总体上为FALSE。

范围划分规则：MySQL从内到外进行评估。例如：

SELECT column1 FROM t1 AS x

WHERE x.column1 = (SELECT column1 FROM t2 AS x

WHERE x.column1 = (SELECT column1 FROM t3

WHERE x.column2 = t3.column1));

在本语句中，x.column2必须是表t2中的列，因为SELECT column1 FROM t2 AS x ...对t2进行了重命名。它不是表t1中的列，因为SELECT column1 FROM t1 ...是一个更靠外的外部查询。

对于HAVING或ORDER BY子句中的子查询，MySQL也会在外部选择清单中寻找列名称。

对于特定的情况，相关联的子查询被优化。例如：

val IN (SELECT key_val FROM tbl_name WHERE correlated_condition)

否则，这些子查询效率不高，可能速度会慢。把查询作为联合进行改写可能会改进效率。

相关联的子查询不能从外部查询中引用总计函数的结果。

13.2.8.8. FROM子句中的子查询

在SELECT语句的FROM子句中，子查询是合法的。实际的语法是：

SELECT ... FROM (subquery) [AS] name ...

[AS] name子句是强制性的，因为FROM子句中的每个表必须有一个名称。在子查询选择列表中的任何列都必须有唯一的名称。您可以在本手册中的其它地方找到对本语法的说明。在该处，所用的词语是“导出表”。

为了进行详细说明，假设您有如下一个表：

CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT);

下面使用了示例表，解释了在FROM子句中如何使用子查询：

INSERT INTO t1 VALUES (1,'1',1.0);

INSERT INTO t1 VALUES (2,'2',2.0);

SELECT sb1,sb2,sb3

FROM (SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sb

WHERE sb1 > 1;

结果：2, '2', 4.0。

下面是另一个例子：假设您想了解一个分类后的表的一组和的平均值。采用如下操作：

SELECT AVG(SUM(column1)) FROM t1 GROUP BY column1;

不过，本查询提供所需的信息：

SELECT AVG(sum_column1)

FROM (SELECT SUM(column1) AS sum_column1

FROM t1 GROUP BY column1) AS t1;

注意，在子查询中使用的列名称（sum_column1）被整理到外部查询中。

FROM子句中的子查询可以返回标量、列、行或表。FROM子句中的子查询不能为有关联的子查询。

即使对EXPLAIN语句（即建立临时导出表），FROM子句中的子查询也会被执行。这是因为在优化过程中，上一级的查询需要有关所有表的信息。

13.2.8.9. 子查询错误

以下错误只适用于子查询。本节把这些错误归在一起。

·         来自子查询的列的数目不正确

·                ERROR 1241 (ER_OPERAND_COL)

·                SQLSTATE = 21000

·                Message = "Operand should contain 1 column(s)"

在出现以下情况时，发生此错误：

SELECT (SELECT column1, column2 FROM t2) FROM t1;

如果您的目的是进行比较，您可以使用能返回多个列的子查询。请参见13.2.8.5节，“行子查询”。不过，在其它的语境下，子查询必须为标量操作数。

·         来自子查询的行的数目不正确：

·                ERROR 1242 (ER_SUBSELECT_NO_1_ROW)

·                SQLSTATE = 21000

·                Message = "Subquery returns more than 1 row"

如果在语句中，子查询返回的行多于一个，则发生此错误。请考虑以下例子：

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

如果SELECT column1 FROM t2只返回一行，则将执行以前的查询。如果子查询返回的行多于一个，则将出现错误1242。在这种情况下，该查询将被改写为：

SELECT * FROM t1 WHERE column1 = ANY (SELECT column1 FROM t2);

·         在子查询中表格使用不正确：

·                Error 1093 (ER_UPDATE_TABLE_USED)

·                SQLSTATE = HY000

·                Message = "You can't specify target table 'x'

·                for update in FROM clause"

在如下情况下，发生该错误：

UPDATE t1 SET column2 = (SELECT MAX(column1) FROM t1);

和SELECT语句一样，在UPDATE和DELETE语句中，子查询是合法的。所以您可以在UPDATE语句中使用子查询进行赋值。不过，您不能把同一个表（在本例中为表t1）既用于子查询的FROM子句，又用于更新目标。

对于事务存储引擎，子查询的错误会导致整个语句失效。对于非事务存储引擎，在遇到错误之前进行的数据修订会被保留。

13.2.8.10. 优化子查询

开发过程不断进展，所以从长远来看，没有一个可靠的优化技巧。有些技巧您可能会感兴趣，并原意采用：

·         有些子句会影响在子查询中的行的数量和顺序。使用这类子句。例如：

·                SELECT * FROM t1 WHERE t1.column1 IN

·                (SELECT column1 FROM t2 ORDER BY column1);

·                SELECT * FROM t1 WHERE t1.column1 IN

·                (SELECT DISTINCT column1 FROM t2);

·                SELECT * FROM t1 WHERE EXISTS

·                (SELECT * FROM t2 LIMIT 1);

·         用子查询替换联合。例如，试进行如下操作：

·                SELECT DISTINCT column1 FROM t1 WHERE t1.column1 IN (

·                SELECT column1 FROM t2);

代替如下操作：

SELECT DISTINCT t1.column1 FROM t1, t2

WHERE t1.column1 = t2.column1;

·         部分子查询可以被转换为联合，以便与不支持子查询的旧版本的MySQL相兼容。不过，在有些情况下，把子查询转化为联合可以提高效果。请参见13.2.8.11节，“把子查询作为用于早期MySQL版本的联合进行改写”。

·         把子句从子查询的外部转移到内部。例如，使用此查询：

·                SELECT * FROM t1

·                WHERE s1 IN (SELECT s1 FROM t1 UNION ALL SELECT s1 FROM t2);

代替此查询：

SELECT * FROM t1

WHERE s1 IN (SELECT s1 FROM t1) OR s1 IN (SELECT s1 FROM t2);

另一个例子是，使用此查询：

SELECT (SELECT column1 + 5 FROM t1) FROM t2;

代替此查询：

SELECT (SELECT column1 FROM t1) + 5 FROM t2;

·         使用行子查询，代替关联子查询。举例说明，使用此查询：

·                SELECT * FROM t1

·                WHERE (column1,column2) IN (SELECT column1,column2 FROM t2);

代替此查询：

SELECT * FROM t1

WHERE EXISTS (SELECT * FROM t2 WHERE t2.column1=t1.column1

AND t2.column2=t1.column2);

·         Use NOT (a = ANY (...)) rather than a <> ALL (...).

·         Use x = ANY (table containing (1,2)) rather than x=1 OR x=2.

·         Use = ANY rather than EXISTS.

·         对于只返回一行的无关联子查询，IN的速度慢于＝。举例说明，使用此查询：

·                SELECT * FROM t1 WHERE t1.col_name

·                = (SELECT a FROM t2 WHERE b = some_const);

代替此查询：

SELECT * FROM t1 WHERE t1.col_name

IN (SELECT a FROM t2 WHERE b = some_const);

使用这些技巧可以使程序更快或更慢。使用BENCHMARK()函数等MySQL工具，您可以了解到在您所处的情况下，哪些技巧会有帮助。

MySQL本身进行的部分优化包括：

·         MySQL只执行一次无关联子查询。使用EXPLAIN确认给定的子查询确实是无关联的。

·         MySQL改写IN, ALL, ANY和SOME子查询，目的是如果子查询中的select-list列已编制索引，则能发挥出此优势。

·         MySQL使用index-lookup函数代替以下格式的子查询。EXPLAIN把此函数描述为特殊的联合类型（unique_subquery或index_subquery）：

·                ... IN (SELECT indexed_column FROM single_table ...)

·         当表达式中不包含NULL值或空集时，MySQL使用一个包含MIN()或MAX()的表达式，对以下格式的表达式进行扩展：

·                value {ALL|ANY|SOME} {> | < | >= | <=} (non-correlated subquery)

例如，本WHERE子句：

WHERE 5 > ALL (SELECT x FROM t)

可以用优化符进行如下处理：

WHERE 5 > (SELECT MAX(x) FROM t)

在MySQL内部手册中有一章名为“MySQL如何转换子查询”，可以从http://dev.mysql.com/doc/获取。