MySQL子查询（嵌套查询）

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从 MySQL 4.1 开始引入。 在SELECT 子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一 个表或者多个表。子查询中常用的操作符有 ANY（SOME）、ALL、IN、EXISTS。子查询可以添 加到 SELECT、UPDATE 和 DELETE 语句中，而且可以进行多层嵌套。子查询中也可以使用比较 运算符，如“<”“<=”“>”“>=”和“!=”等。下面将介绍如何在 SELECT 语句中嵌套子查询。

1. ANY、SOME 关键字的子查询

ANY 和 SOME 关键字是同义词，表示满足其中任一条件，它们允许创建一个表达式对子查询 的返回值列表进行比较，只要满足内层子查询中的任何一个比较条件，就返回一个结果作为外层查 询的条件。 下面定义两个表 tbl1 和 tbl2：

CREATE table tbl1 ( num1 INT NOT NULL);
CREATE table tbl2 ( num2 INT NOT NULL);

分别向两个表中插入数据：

INSERT INTO tbl1 values(1), (5), (13), (27);
INSERT INTO tbl2 values(6), (14), (11), (20);

ANY 关键字接在一个比较操作符的后面，表示若与子查询返回的任何值比较为 TRUE，则返 回 TRUE。 返回 tbl2 表的所有 num2 列，然后将 tbl1 中的 num1 的值与之进行比较，只要大于 num2 的任何 1 个值，即为符合查询条件的结果。

SELECT num1 FROM tbl1 WHERE num1 > ANY (SELECT num2 FROM tbl2);

在子查询中，返回的是 tbl2 表的所有 num2 列结果（6,14,11,20），然后将 tbl1 中的 num1 列 的值与之进行比较，只要大于 num2 列的任意一个数即为符合条件的结果。

2. 带 ALL 关键字的子查询

ALL 关键字与 ANY 和 SOME 不同，使用 ALL 时需要同时满足所有内层查询的条件。例如， 修改前面的例子，用 ALL 关键字替换 ANY。 ALL 关键字接在一个比较操作符的后面，表示与子查询返回的所有值比较为 TRUE，则返回 TRUE。 返回 tbl1 表中比 tbl2 表 num2 列所有值都大的值，SQL 语句如下：

 SELECT num1 FROM tbl1 WHERE num1 > ALL (SELECT num2 FROM tbl2);

在子查询中，返回的是 tbl2 的所有 num2 列结果（6,14,11,20），然后将 tbl1 中的 num1 列 的值与之进行比较，大于所有 num2 列值的 num1 值只有 27，因此返回结果为 27。

3. 带 EXISTS 关键字的子查询

EXISTS 关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回 行，如果至少返回一行，那么 EXISTS 的结果为 true，此时外层查询语句将进行查询；如果子查询 没有返回任何行，那么 EXISTS 返回的结果是 false，此时外层语句将不进行查询。 查询 suppliers 表中是否存在 s_id=107 的供应商，如果存在，则查询 fruits 表中的 记录，SQL 语句如下：

SELECT * FROM fruits WHERE EXISTS (SELECT s_name FROM suppliers WHERE s_id = 107);

由结果可以看到，内层查询结果表明 suppliers 表中存在 s_id=107 的记录，因此 EXISTS 表达 式返回 true；外层查询语句接收 true 之后对表 fruits 进行查询，返回所有的记录。 EXISTS 关键字可以和条件表达式一起使用。 查询 suppliers 表中是否存在 s_id=107 的供应商，如果存在，则查询 fruits 表中的 f_price 大于 10.20 的记录，SQL 语句如下：

SELECT * FROM fruits WHERE f_price>10.20 AND EXISTS (SELECT s_name FROM suppliers WHERE s_id = 107); 

由结果可以看到，内层查询结果表明 suppliers 表中存在 s_id=107 的记录，因此 EXISTS 表达 式返回 true；外层查询语句接收 true 之后根据查询条件 f_price > 10.20 对 fruits 表进行查询，返回 结果为 4 条 f_price 大于 10.20 的记录。 NOT EXISTS 与 EXISTS 使用方法相同，返回的结果相反。子查询如果至少返回一行，那么 NOT EXISTS 的结果为 false，此时外层查询语句将不进行查询；如果子查询没有返回任何行，那 么 NOT EXISTS 返回的结果是 true，此时外层语句将进行查询。 查询 suppliers 表中是否存在 s_id=107 的供应商，如果不存在则查询 fruits 表中的 记录，SQL 语句如下：

 SELECT * FROM fruits WHERE NOT EXISTS (SELECT s_name FROM suppliers WHERE s_id = 107);

查询语句 SELECT s_name FROM suppliers WHERE s_id = 107，对 suppliers 表进行查询返回了 一条记录，NOT EXISTS 表达式返回 false，外层表达式接收 false，将不再查询 fruits 表中的记录。 提 示 ：EXISTS 和 NOT EXISTS 的结果只取决于是否会返回行，而不取决于这些行的内容，所 以这个子查询输入列表通常是无关紧要的。

4. 带 IN 关键字的子查询

IN 关键字进行子查询时，内层查询语句仅仅返回一个数据列，这个数据列里的值将提供给外 层查询语句进行比较操作。 在 orderitems 表中查询 f_id 为 c0 的订单号，并根据订单号查询具有订单号的客户 c_id，SQL 语句如下：

 SELECT c_id FROM orders WHERE o_num IN (SELECT o_num FROM orderitems WHERE f_id = 'c0');

查询结果的 c_id 有两个值，分别为 10001 和 10004。上述查询过程可以分步执行，首先内层 子查询查出 orderitems 表中符合条件的订单号，单独执行内查询，查询结果如下：

SELECT o_num FROM orderitems WHERE f_id = 'c0';

可以看到，符合条件的 o_num 列的值有两个：30003 和 30005，然后执行外层查询，在 orders 表中查询订单号等于 30003 或 30005 的客户 c_id。嵌套子查询语句还可以写为如下形式，实现相同 的效果：

 SELECT c_id FROM orders WHERE o_num IN (30003, 30005);

这个例子说明在处理 SELECT 语句的时候，MySQL 实际上执行了两个操作过程，即先执行内 层子查询，再执行外层查询，内层子查询的结果作为外部查询的比较条件。 SELECT 语句中可以使用 NOT IN 关键字，其作用与 IN 正好相反。 与前一个例子类似，但是在 SELECT 语句中使用 NOT IN 关键字，SQL 语句如下：

SELECT c_id FROM orders WHERE o_num NOT IN (SELECT o_num FROM orderitems WHERE f_id = 'c0');

这里返回的结果有 3 条记录，由前面可以看到，子查询返回的订单值有两个，即 30003 和 30005， 但为什么这里还有值为 10001 的 c_id 呢？这是因为 c_id 等于 10001 的客户的订单不只一个，可以 查看订单表 orders 中的记录。

SELECT * FROM orders;

可以看到，虽然排除了订单号为 30003 和 30005 的客户 c_id，但是 o_num 为 30001 的订单与 30005 都是 10001 号客户的订单。所以结果中只是排除了订单号，但是仍然有可能选择同一个客户。

提 示 ：子查询的功能也可以通过连接查询完成，但是子查询使得 MySQL 代码更容易阅读和编 写。

5. 带比较运算符的子查询

在前面介绍的带 ANY、ALL 关键字的子查询时使用了“>”比较运算符，子查询时还可以使 用其他的比较运算符，如“<”“<=”“=”“>=”和“!=”等。 在 suppliers 表中查询 s_city 等于“Tianjin”的供应商 s_id，然后在 fruits 表中查询 所有该供应商提供的水果的种类，SQL 语句如下：

SELECT s_id, f_name FROM fruits WHERE s_id =(SELECT s1.s_id FROM suppliers AS s1 WHERE s1.s_city = 'Tianjin'); 

该嵌套查询首先在 suppliers 表中查找 s_city 等于 Tianjin 的供应商的 s_id，单独执行子查询查 看 s_id 的值，执行下面的操作过程：

 SELECT s1.s_id FROM suppliers AS s1 WHERE s1.s_city = 'Tianjin';

然后在外层查询时，在 fruits 表中查找 s_id 等于 101 的供应商提供的水果的种类，查询结果如 下：

SELECT s_id, f_name FROM fruits WHERE s_id = (SELECT s1.s_id FROM suppliers AS s1 WHERE s1.s_city = 'Tianjin');

结果表明，“Tianjin”地区的供应商提供的水果种类有 3 种，分别为“apple”“blackberry” “cherry”。 在 suppliers 表中查询 s_city 等于“Tianjin”的供应商 s_id，然后在 fruits 表中查询 所有非该供应商提供的水果的种类，SQL 语句如下：

SELECT s_id, f_name FROM fruits WHERE s_id <> (SELECT s1.s_id FROM suppliers AS s1 WHERE s1.s_city = 'Tianjin');

该嵌套查询执行过程与前面相同，在这里使用了不等于“<>”运算符，因此返回的结果和前 面正好相反。