子查询

子查询的用途

在前文提到的SQL中，WHERE字句的筛选条件都有一个共同特点，就是其筛选条件在SQL语句执行之前，就已经被我们写死了。例如：

1
2
SELECT * FROM scores 
WHERE name = '屈屈';

其筛选条件学生姓名为“屈屈”，在执行SQL之前，我们就知道了筛选条件。
然后在一些情况下，我们并不能提前知道筛选条件。例如现在有2张表，一张是学生表，一张是成绩表。
学生表（表名students）：

id name gender class
1 屈屈 女 1班
2 慧心 男 2班
3 达琳 女 1班
4 森贝 女 2班

成绩表（表名scores）：

student_id subject score
1 创意写作 100
1 时尚美学 100
1 高能物理 29
2 创意写作 81
2 电子竞技 91
2 高能物理 85
3 创意写作 93
3 时尚美学 65
3 电子竞技 59
4 时尚美学 75
4 电子竞技 83
4 高能物理 90

PS：可以观察到成绩表与前文提到的成绩表的列不同，这里的列数更少，这样做是为了避免信息存储的冗余。因为根据学生的ID就可以唯一确定一名学生，无需再存储学生的姓名、班级等信息。

PS：学生可以自由选择课程，不同学生的课程可能不同，“选课表“在后文会提到。

此时若想查询“屈屈”的成绩，就无法在成绩表中直接查询，因为我们不知道“屈屈”的学生ID是多少。这时就需要使用子查询来解决这个问题。

标量子查询

标量子查询是指返回单一值的子查询。例如对于前文提出问题，我们想要查询“屈屈”的成绩，可以使用如下SQL语句：

1
2
SELECT * FROM scores 
WHERE student_id = (SELECT id FROM students WHERE name = '屈屈');

这里的子查询SELECT id FROM students WHERE name = '屈屈'其返回的结果是一个单一的值，我们称之为标量子查询。
其执行的顺序为：

  1. 先执行子查询SELECT id FROM students WHERE name = '屈屈'，得到学生ID。
  2. 再执行主查询SELECT * FROM scores WHERE student_id = 学生ID，得到“屈屈”的成绩。
    这样就实现了根据学生姓名查询成绩的功能。

列子查询

列子查询是指返回一列值的子查询。例如我们想要查询“1班”的学生的成绩，可以使用如下SQL语句：

1
2
SELECT * FROM scores 
WHERE student_id IN (SELECT id FROM students WHERE class = '1班');

这里的子查询SELECT id FROM students WHERE class = '1班'其返回的结果是一列值，我们称之为列子查询。
其执行的顺序为：

  1. 先执行子查询SELECT id FROM students WHERE class = '1班'，得到“1班”的学生ID列表。
  2. 再执行主查询SELECT * FROM scores WHERE student_id IN (学生ID列表)，得到“1班”的学生的成绩。

注意WHERE条件中，使用的是IN而不是=，因为子查询实际返回的是多个值（列表），使用IN判断是否在列表中。

行子查询

例如我们现在想查询屈屈同学的高能物理成绩，可以使用如下SQL语句：

1
2
SELECT * FROM scores 
WHERE (student_id, subject) = (SELECT id, '高能物理' FROM students WHERE name = '屈屈');

这里的子查询SELECT id, '高能物理' FROM students WHERE name = '屈屈'其返回的结果是一行值：

id subject
1 高能物理

然后主查询根据这一行值进行筛选，即查询学生ID为1，科目为“高能物理”的成绩，得到“屈屈”的高能物理成绩。
对于这种返回一行值的子查询，我们称之为行子查询。

上述SQL在效果上与以下SQL等价：

1
2
SELECT * FROM scores 
WHERE student_id = (SELECT id FROM students WHERE name = '屈屈') AND subject = '高能物理';

表子查询

表子查询是指返回一张表的子查询。例如我们想查询1班所有同学的创意写作成绩，可以使用如下SQL语句：

1
2
SELECT * FROM scores 
WHERE (student_id, subject) IN (SELECT id, '创意写作' FROM students WHERE class = '1班');

将返回：

student_id subject score
1 创意写作 100
3 创意写作 93

这里的子查询SELECT id, '创意写作' FROM students WHERE class = '1班'其返回的结果是一张表，我们称之为表子查询。
注意此处WHERE子句中使用的是IN而不是=，因为表子查询返回的是一张表，而不是单一行，所以需要使用IN来判断是否在子查询的结果中。

上述SQL在效果上与以下SQL等价：

1
2
SELECT * FROM scores 
WHERE student_id IN (SELECT id FROM students WHERE class='1班') AND subject = '创意写作';

多层嵌套子查询

多层嵌套子查询是指在子查询中再嵌套子查询的情况。通过多层嵌套子查询，我们可以实现更复杂的查询逻辑。

例如现在新增一张选课表，用于存储学生选课信息：
选课表（表名student_courses）：

student_id subject
1 创意写作
1 时尚美学
1 高能物理
2 创意写作
2 电子竞技
2 高能物理
3 创意写作
3 时尚美学
3 电子竞技
4 时尚美学
4 电子竞技
4 高能物理

现在想查询屈屈同学所选课程的所有成绩信息，可以使用如下SQL语句：

1
2
3
4
5
SELECT * FROM scores WHERE subject IN (
    SELECT subject FROM student_courses WHERE student_id = (
        SELECT id FROM students WHERE name = '屈屈'
    )
);

从内到外，其执行过程为：

  1. 最内层的子查询SELECT id FROM students WHERE name = '屈屈'获取屈屈同学的学号。
  2. 中间的子查询`SELECT subject FROM student_courses WHERE student_id = (…) 利用获取到的学号查找屈屈同学所选的所有课程。
  3. 主查询SELECT * FROM scores WHERE subject IN (...)则利用中间子查询返回的课程名称，查找scores表中与这些课程名称匹配的成绩记录。

对于多层嵌套子查询，嵌套层数可以非常多。但是在实际应用中应该尽量避免过多的嵌套，会让SQL语句变得复杂难懂。

相关子查询

在前面列举的子查询中，子查询与主查询之间并没有直接的关联，子查询的结果是独立的，子查询语句可以单独执行。对于这类子查询，我们称之为不相关子查询。

而相关子查询是指子查询依赖于外部查询中的某个列或值，子查询语句是不能单独执行的。例如我们想查询成绩高于90分的学生姓名，可以使用如下SQL语句：

1
2
3
4
5
6
7
SELECT name
FROM students
WHERE (
    SELECT MAX(score)
    FROM scores
    WHERE scores.student_id = students.id
) > 90;

将返回：

name
屈屈
慧心
达琳

在这个查询中，子查询 SELECT MAX(score) FROM scores WHERE scores.student_id = students.id会根据主查询中的students.id过滤scores表，查找该学生的最高成绩。然后主查询会根据子查询的结果，判断是否大于90分，从而得到成绩高于90分的学生姓名。这就意味着子查询依赖于外部查询中的students.id，因此称之为相关子查询。

我们可以这样理解相关子查询的执行过程，外部查询的每一行都会触发子查询的执行，并且子查询会根据当前行的students.id动态计算结果，即子查询会针对主查询的每一行进行一次匹配。

带有EXISTS关键字的子查询

在子查询中，还有一种特殊的用法，就是带有EXISTS关键字的子查询。
EXISTS关键字用于检查子查询是否返回任何行。如果子查询返回至少一行，则条件成立，否则条件不成立。
例如我们想查询对于创意写作科目有成绩的学生姓名，可以使用如下SQL语句：

1
2
3
SELECT name FROM students WHERE EXISTS (
    SELECT * FROM scores WHERE student_id = students.id AND subject = '创意写作'
);

将返回：

name
屈屈
慧心
达琳

类似的，如果我们想查询对于创意写作科目没有成绩的学生姓名，可以使用如下SQL语句：

1
2
3
SELECT name FROM students WHERE NOT EXISTS (
    SELECT * FROM scores WHERE student_id = students.id AND subject = '创意写作'
);

将返回：

name
森贝

总结一下，使用EXISTS来检查子查询返回了任何行，使用NOT EXISTS来检查子查询没有返回任何行。

同一个表的子查询

前文所述子查询中，子查询与主查询操作的表是不同的。但是在实际应用中，有时候我们需要在同一个表中进行子查询。例如我们想查询对于创意写作科目，成绩高于平均分的学生id，可以使用如下SQL语句：

1
2
3
SELECT student_id FROM scores WHERE subject = '创意写作' AND score > (
    SELECT AVG(score) FROM scores WHERE subject = '创意写作'
);

在这个例子中，主查询和子查询都操作了scores表。子查询计算了创意写作科目的平均分，然后主查询根据这个平均分筛选出成绩高于平均分的学生ID。

写在最后：不用去记忆什么是标量子查询、列子查询、行子查询、表子查询等名词，只要理解其用途，能根据实际情况灵活运用即可。