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

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

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

SELECT (SELECT s2 FROM t1);

CREATE TABLE t1 (s1 INT);

INSERT INTO t1 VALUES (1);

CREATE TABLE t2 (s1 INT);

INSERT INTO t2 VALUES (2);

SELECT (SELECT s1 FROM t2) FROM t1;

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

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

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节，“行子查询”。

语法：

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有助于确认每个人都理解该查询的真正含义。

SELECT * FROM t1 WHERE column1 = ANY

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

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

val IN (SELECT key_val FROM tbl_name WHERE correlated_condition)

在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子句中的子查询也会被执行。这是因为在优化过程中，上一级的查询需要有关所有表的信息。

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

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

·                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子句，又用于更新目标。

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

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

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

·                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/获取。