MySQL8 子查询

子查询是一个嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句或其他子查询中的查询。

任何允许使用表达式的地方都可以使用子查询。

基于比较运算符的子查询

常用的比较运算符有=、>、<、>=、<=、!=。比较运算符两边除了可以使用表达式外，还可以使用子查询。

示例：

所有分数在80分及以上的学生：

select * from school.student
where student_id = (select student_id from school.student_score where score >= 80);

基于IN/NOT IN关键字的查询

IN：值存在于子查询中；

NOT IN：值不存在于子查询中；

示例：

分数在80分及以上的学生：

select * from school.student
where student_id in (select student_id from school.student_score where score >= 80);

没有分数在80分及以上的学生：

select * from school.student
where student_id not in (select student_id from school.student_score where score >= 80);

基于EXISTS/NOT EXISTS关键字的查询

如果EXISTS后的子查询返回至少一行记录，则EXISTS的结果为TRUE；否则为FALSE；

示例：

如果有任意一个学生分数在80分及以上，则返回所有学生信息：

select * from school.student
where exists (select student_id from school.student_score where score >= 80);

这种写法，子查询与外层的SELECT层查询之间没有产生联系，仅仅作为是否存在某类数据的判断条件；

有任意一门课程的分数在80分及以上的学生：

select * from school.student a
where exists (select b.student_id 
              from school.student_score b 
              where b.score >= 80 
              and a.student_id = b.student_id);

所有课程的分数没有在80分及以上的学生：

select * from school.student a
where not exists (select b.student_id 
                  from school.student_score b 
                  where b.score >= 80 
                  and a.student_id = b.student_id);

基于ALL关键字的子查询

ALL关键字要求对于子查询结果集中的所有记录，都满足条件。

示例：

查询比所有姓王的同学的年龄都大的学生：

select * from school.student
where age > all (select age from school.student where name like '王%');

基于ANY/SOME关键字的查询

any/some关键字要求对于子查询结果集中的任意一个记录满足条件即可。

示例：

查询比任意一个姓王的同学的年龄大的学生：

select * from school.student
where age > any (select age from school.student where name like '王%');
等价于：
select * from school.student
where age > some (select age from school.student where name like '王%');

作为结果值返回的子查询

子查询的结果还可以作为SELECT后的表达式进行返回。只不过同一行数据，子查询的结果只能有一个，否则会报错。

示例：

查询所有学生信息并返回每个学生所有课程获得的最大分数：

select
    a.student_id,
    a.student_name,
    (select max(b.score) from school.student_score b where a.student_id = b.student_id) as max_score
from school.student a;

上面的查询，只返回每个学生所有课程的最大分数。而所有课程的最大分数只会有一个，因此是合法的。

但下面的SQL要求返回每个学生的每门课程的分数，则会报错：Error Code: 1242. Subquery returns more than 1 row

select
    a.student_id,
    a.student_name,
    (select b.score from school.student_score b where a.student_id = b.student_id) as score
from school.student a;