14-子查询

8.8 子查询

本节视频教学录像：11分钟

子查询是将一个查询语句嵌套在另一个查询语句中。例如可以为SELECT FROM t1 WHERE column1 = (SELECT column1 FROM t2)这样的一种写法。其中，SELECT column1 FROM t2为子查询，必须要位于圆括号中。SELECT FROM t1…为外部查询。子查询还可嵌套在其他的子查询中。通过子查询，可以实现多表之间的查询。子查询中常用的操作符有ANY(SOME)、ALL、IN、EXISTS。

1.带 ANY、SOME、ALL操作符的子查询

ALL和ANY操作符的常见用法是结合一个比较操作符对一个数据列子查询的结果进行比较。它们检查比较值是否与子查询所返回的全部或一部分值匹配。例如，如果比较值小于或等于子查询所返回的每一个值，<= ALL将是true；只要比较值小于或等于子查询所返回的任何一个值，<= ANY将是true。SOME是ANY的一个同义词。

【范例8-39】

查询成绩表中最低的分数。

MySQL> select sno,grade -> from sc -> where grade<=all(select grade from sc);

结果如下。

+------+-------+ |sno |grade| +------+-------+ | 1| 67| +------+-------+ 1 row in set (0.00 sec)

该查询中判断sc 表中每一行的grade 是否小于等于所有的成绩，即查询最低的成绩。该查询也可以使用统计函数实现。

MySQL> select sno,grade -> from sc -> where grade<=(select min(grade) from sc);

该实现方法，在子查询中查询出最低分，然后在外层查询查询小于等于最小值的成绩，得到的就是最低分对应的成绩信息。

【范例8-40】

查询其他系比计算机系中某一位学生年龄小的学生信息。

MySQL> select * -> from student -> where sage <any(select sage from student where sdept='计算机') ->and sdept<>'计算机';

结果如下。

+-----+-------+------+------+-------+ | sno | sname | ssex | sage | sdept | +-----+-------+------+------+-------+ | 3|张明 |男 | 20|经贸 | +-----+-------+------+------+-------+ 1 row in set (0.04 sec)

该嵌套中的子查询返回值为集合[19，21]，处理外查询时，查找所有不是计算机系的年龄大于19小于21的学生信息。该查询也可以使用统计函数实现。

MySQL> select * -> from student -> where sage< -> (select max(sage) -> from student -> where sdept='计算机')and sdept<>'计算机';

结果与之前相同。

+-----+-------+------+------+-------+ | sno | sname | ssex | sage | sdept | +-----+-------+------+------+-------+ | 3|张明 |男 | 20|经贸 | +-----+-------+------+------+-------+ row in set (0.00 sec)

2.IN和NOT IN子查询

带IN关键字进行子查询时，内层查询仅返回一个数据列集合。该集合将提供给外层查询进行比较操作。

【范例8-41】

查询选修了1号课程的学生编号和姓名。

MySQL> select sno,sname -> from student -> where sno in(select sno from sc where cno=1);

结果如下。

+-----+-------+ | sno | sname | +-----+-------+ | 1|刘敏 | | 2|周松 | +-----+-------+ rows in set (0.04 sec)

该嵌套中的子查询首先执行处理，得到结果为集合[1，2]，返回外层查询作为条件，只要学生编号为集合中的某个值就为true。使用IN关键字的子查询可以用多表连接来实现，范例8-40可以用以下代码实现。

MySQL> select s.sno,sname -> from student s,sc -> where s.sno=sc.sno and cno=1；

结果如下。

+-----+-------+ | sno | sname | +-----+-------+ | 1|刘敏 | | 2|周松 | +-----+-------+ rows in set (0.06 sec)

使用多表连接的查询结果与使用IN关键字的查询结果相同。

提示 使用IN关键字的查询可以使用多表连接查询来实现，但使用多表连接的查询不一定可以转化为使用IN关键字的嵌套实现。因为使用IN 关键字的嵌套最后执行的是最外层的查询，因此查询结果只能是最外层表的信息，不能出现内层查询中表的信息。

【范例8-42】

查询没有选修1号课程的学生编号和姓名。

MySQL> select * -> from student -> where sno not in(select sno -> from sc -> where cno=1);

判断外查询中某列值不在某个范围，使用NOT IN关键字。

使用NOT IN关键字的嵌套不能使用多表连接查询实现。如果确定子查询只返回一个单一值时，IN关键字可以换成=操作符。

假如规定一个学生只在一个系学习，完成下面的查询。

【范例8-43】

查询与刘敏同学在同一系学习的学生信息。

MySQL> select * -> from student -> where sdept=(select sdept from student where sname='刘敏') -> and sname<>'刘敏';

结果如下。

+-----+-------+------+------+--------+ |sno|sname|ssex|sage|sdept | +-----+-------+------+------+--------+ | 2|周松 |男 | 21|计算机| +-----+-------+------+------+--------+ 1 row in set (0.00 sec)

该子查询返回刘敏所在的系“计算机”，该值唯一，返回外层查询作为查询条件，外层查询还加入了过滤条件sname<>‘刘敏’，如果不加入该条件，刘敏的信息也会出现在查询结果中。

3.EXISTS和NOT EXISTS子查询

EXISTS和NOT EXISTS操作符只测试某个子查询是否返回了数据行：如果是，EXISTS将是true，NOT EXISTS将是false。在使用EXISTS和NOT EXISTS操作符时，子查询中的SELECT子句通常使用“*”。这两个操作符是根据子查询是否返回了数据行来判断真假的，不关心数据行所包含的内容是什么，所以没必要明确地列出数据列的名字。

【范例8-44】

查询选修了1号课程的学生信息。

MySQL> select -> from student -> where exists(select from sc where sno=student.sno and cno=1);

结果如下。

+-----+-------+------+------+--------+ |sno|sname|ssex|sage|sdept | +-----+-------+------+------+--------+ | 1|刘敏 |女 | 19|计算机| | 2|周松 |男 | 21|计算机| +-----+-------+------+------+--------+ rows in set (0.00 桥sec)

该子查询是外查询相关的嵌套查询。执行过程是由外向内的方式，遍历外层查询中student表，将每一行的sno传递到子查询中，看它们是否满足在子查询里给出的条件，满足条件的外查询中的行出现在结果中，否则相反。

NOT EXISTS操作符用来寻找不匹配的值（在一个数据表里有但在其他数据表里没有的值）。使用NOT EXISTS与EXISTS方法相同，返回的结果相反。子查询如果返回至少一行，那么NOT EXISTS结果为false，外查询中对应的行不满足要求。如果子查询没有返回任何行，则NOT EXISTS返回的结果为true, 外查询中对应的行满足要求。

【范例8-45】

查询没有选修1号课程的学生信息。

MySQL> select -> from student -> where not exists(select from sc where student.sno=sc.sno);

结果如下。

+-----+-------+------+------+-------+ | sno | sname | ssex | sage | sdept | +-----+-------+------+------+-------+ | 3|张明 |男 | 20|经贸 | | 4|孟欣 |女 | 21|信管 | +-----+-------+------+------+-------+ 2 rows in set (0.28 sec)