子查询指一个查询语句嵌套在另一个查询语句内部的查询,这个特性从 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 列的任意一个数即为符合条件的结果。

  1. 带 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。

  1. 带 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 的结果只取决于是否会返回行,而不取决于这些行的内容,所 以这个子查询输入列表通常是无关紧要的。

  1. 带 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 代码更容易阅读和编 写。

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

在前面介绍的带 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');

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