1.前言

子查询分为嵌套子查询和相关子查询两类

嵌套子查询：子查询总共执行一次，执行完毕将值返回主查询

相关子查询：主查询执行一行，子查询执行一次，如此往复至主查询执行完毕

2.嵌套子查询

2.1.标量子查询

SELECT * 
FROM student
WHERE
	score=(SELECT MAX(score) FROM student);

SELECT * 
FROM student
WHERE
	score>(SELECT AVG(score) FROM student);

2.2.列子查询

# SOME等价于ANY，IN等价于=ANY或=SOME，NOT IN等价于<>ALL
SELECT * 
FROM student
WHERE
	score>=ALL (SELECT score FROM student);
SELECT * 
FROM student
WHERE
	score>=ANY (SELECT AVG(score) FROM student GROUP BY class);

SELECT *
FROM student
WHERE
	score IN (SELECT score FROM student WHERE date='2024-06-01');
# 等价于
SELECT *
FROM student
WHERE
	score=ANY (SELECT score FROM student WHERE date='2024-06-01');

SELECT *
FROM student
WHERE
	score NOT IN (SELECT score FROM student WHERE date='2024-06-01');
# 等价于
SELECT *
FROM student
WHERE
	score<>ALL (SELECT score FROM student WHERE date='2024-06-01');

2.3.行子查询

SELECT * 
FROM student1
WHERE
	(name,score)=(SELECT name,score FROM student2 WHERE name='Bob');

2.4.表子查询

SELECT * 
FROM student
WHERE
	(class,score) IN (SELECT class,MAX(score) FROM student GROUP BY class);

3.相关子查询

SELECT * 
FROM student AS S1
WHERE
	score=(SELECT MAX(score) FROM student WHERE class=S1.class);

4.EXISTS

# EXISTS常用于相关子查询

# 查找在student1中但不在student2的学生信息
SELECT * 
FROM student1 AS S1
WHERE
	NOT EXISTS (SELECT * FROM student2 WHERE name=S1.name);

# 如果不存在id为5的记录则插入
INSERT INTO student(id,name,date,score) 
VALUES(5,'Alice','2023-01-02',89)
WHERE
	NOT EXISTS (SELECT * FROM student WHERE id=5);

5.派生表

# 派生表必须指定别名
SELECT AVG(max_score)
FROM
	(SELECT class,MAX(score) AS max_score FROM student GROUP BY class) AS T;

# 大多数时候派生表都可用CTE表达式实现

6.相关拓展

最新版MySQL的派生表同样支持相关子查询，详情见官方文档

部分操作可能不被支持，例如同时使用IN/SOME/ANY和LIMIT

子查询按位置划分可以是SELECT,WHERE,HAVING,FROM等

多数时候采用子查询的实现方式是多样的，并且可以与联接互相转换

7.相关题目

EXISTS的使用：SQL251 使用含有关键字exists查找未分配具体部门的员工的所有信息。

EXISTS的使用：SQL229 批量插入数据，不使用replace操作

涉及联接：SQL206 获取每个部门中当前员工薪水最高的相关信息

更多知识见专栏