首页 › MySQL › 第 05 章 › 讲义 › 9. 子查询（嵌套查询）

9. 子查询（嵌套查询）

# MySQL 子查询（Subquery）全解析
/*
子查询（也叫嵌套查询）是指在一个 SQL 语句（主查询 / 外部查询）中嵌套另一个 SQL 语句（子查询 / 内部查询），子查询的结果会作为主查询的条件、数据源、计算依据 等。子查询的核心优势是逻辑清晰、易读，适合将复杂问题拆解为简单的嵌套逻辑；但需注意性能优化，避免大数据量下的效率问题。
*/
# 一、子查询的核心特性
/*
语法要求：子查询必须用 () 包裹，这是 MySQL 的强制语法；
执行逻辑：
非关联子查询：先执行子查询，将结果传给主查询（仅执行一次）；
关联子查询：主查询每遍历一行，子查询执行一次（依赖主查询的列）；
返回结果：子查询可返回标量（单个值）、列（单列多行）、行（单行多列）、表（多行多列），不同结果对应不同的使用场景。
*/
# 二、子查询的分类（按返回结果类型）

# 1. 标量子查询（Scalar Subquery）
-- 定义：返回单行单列的单个值（如数字、字符串）；
-- 适用场景：可直接用在 WHERE、SELECT、HAVING 等子句中，搭配 =、>、<、>=、<= 等标量运算符；



-- 1. WHERE子句：查询数学成绩高于 钱电 的学生名单

-- 1步：先查询出钱电的数学成绩
sql
select score from sc join student 
on sc.sno=student.sno 
join course on course.cno=sc.cno 
where student.sname='钱电' and course.cname='数学'; 
-- 2步： 查询数学成绩>60的学生名单
sql
SELECT sname,score 
from sc join student 
on sc.sno=student.sno 
join course on course.cno=sc.cno
WHERE  score > 60 and course.cname='数学' ; 
-- 3步： 将以上两个语句合并在一起执行
sql
SELECT sname,score 
from sc join student 
on sc.sno=student.sno 
join course on course.cno=sc.cno
WHERE  score > (select score from sc join student 
on sc.sno=student.sno 
join course on course.cno=sc.cno 
where student.sname='钱电' and course.cname='数学') and course.cname='数学' ;
-- 2. SELECT子句：查询每个学生的姓名和数学成绩（无成绩则返回NULL）
sql
SELECT s.sno,
  s.sname,
  (SELECT sc.score FROM sc join course on sc.cno=course.Cno WHERE sc.sno = s.sno AND course.cname = '数学' ) AS 数学成绩 -- 先查询所有学生的数学成绩
FROM student s
ORDER BY s.sno;
# 2. 列子查询（Column Subquery）
/*
定义：返回单列多行的结果集（集合）；
适用场景：搭配 IN、NOT IN、ANY/SOME、ALL 等集合运算符；
核心运算符说明：
IN：匹配集合中任意一个值；
ANY/SOME：满足集合中任意一个条件（如 score > ANY(...)）；
ALL：满足集合中所有条件（如 score > ALL(...)）。
*/
-- 1. IN：查询班级 01 所有学生的成绩
sql
SELECT * FROM sc 
WHERE sno IN (SELECT sno FROM student WHERE class = '01');
-- 2. ANY：查询数学成绩高于班级 01 任意一个学生数学成绩的学生姓名，班级和数学成绩
-- 1步：查询班级 01 所有学生的数学成绩
sql
SELECT score FROM sc join course on sc.Cno=course.cno 
WHERE sno IN (SELECT sno FROM student WHERE class = '01') and course.Cname='数学';
-- 2步：查询所有学生数学成绩大于30的学生姓名，班级和数学成绩
sql
SELECT sname,class,score 
from sc join course on sc.Cno=course.Cno
join student on sc.Sno=student.Sno 
WHERE course.Cname='数学' and score>30; 
-- 3步：将以上两步合并
sql
SELECT sname,class,score 
from sc join course on sc.Cno=course.Cno
join student on sc.Sno=student.Sno 
WHERE course.Cname='数学' and score>
any(SELECT score FROM sc join course on sc.Cno=course.cno 
WHERE sno IN (SELECT sno FROM student WHERE class = '01') and course.Cname='数学'); 
-- 3. ALL：查询数学成绩高于班级01所有学生数学成绩的记录
sql
SELECT sname,class,score 
from sc join course on sc.Cno=course.Cno
join student on sc.Sno=student.Sno 
WHERE course.Cname='数学' and score>
all(SELECT score FROM sc join course on sc.Cno=course.cno 
WHERE sno IN (SELECT sno FROM student WHERE class = '01') and course.Cname='数学');
# 3. 行子查询（Row Subquery）
/*
定义：返回单行多列（或多行多列）的结果集，需用行构造器 (col1, col2) 匹配；
适用场景：多列条件同时匹配（如 (subject, score) = (子查询)）。
*/
-- 1.查询01班16岁的学生
sql
SELECT * FROM student 
WHERE (class, sage) = (('01',16));
-- 2. 查询和赵雷语文成绩相同的学生姓名和语文成绩（cname+score）
sql
SELECT sname, score 
FROM student  JOIN sc ON student.sno = sc.sno
join course on sc.Cno=course.Cno  
WHERE (course.Cname, sc.score) = (
  SELECT cname, score 
  FROM sc join course on sc.Cno=course.Cno
  WHERE sno = (SELECT sno FROM student WHERE sname = '赵雷') 
    AND cname = '语文');
-- 结果：赵雷（80）、孙风（80）

-- 2. 行集合匹配：

-- 查询01班武汉的男生或02班北京的女生
sql
SELECT * FROM student 
WHERE (class, sbirthplace,ssex) IN (('01','武汉','男'),('02','北京','女'));
# 4. 表子查询（Table Subquery）
/*
定义：返回多行多列的结果集（相当于临时表）；
适用场景：用在 FROM 子句中（必须给临时表加别名），或 JOIN 中；
注意：FROM 子句中的子查询必须指定别名，否则 MySQL 报错。
*/
-- 1. 统计每个学生总分>160的学生
-- 1步：算出每个学生的总分
sql
SELECT student.sno,sname,sum(score) 总分
from student join sc on student.Sno=sc.Sno 
GROUP BY student.Sno;
-- 2步：将上面的结果作为一个表来处理
sql
SELECT sname, 总分 
FROM (
  SELECT student.sno,sname,sum(score) 总分 
from student join sc on student.Sno=sc.Sno 
GROUP BY student.Sno) AS t -- 临时表别名t
WHERE 总分 > 160; 
# 三、子查询的分类（按关联方式）
/*
1. 非关联子查询（Non-Correlated Subquery）
定义：子查询不依赖主查询的列，可独立执行，仅执行一次；
特点：性能较好，优先使用；
示例：前面的标量子查询、列子查询（如 IN 示例）均为非关联子查询。
2. 关联子查询（Correlated Subquery）
定义：子查询依赖主查询的列（如 sc.stu_id = s.id），主查询每遍历一行，子查询执行一次；
核心运算符：EXISTS/NOT EXISTS（判断子查询是否有结果，返回布尔值）；
特点：逻辑灵活，但大数据量下性能较差（需优化）。
*/
-- 1. EXISTS：查询有数学成绩的学生学号，姓名
sql
SELECT sno,sname FROM student
WHERE EXISTS (       -- exists 判断是否存在某结果
  SELECT * 
  FROM sc join course on sc.Cno=course.Cno
  WHERE sc.sno = student.sno AND course.cname = '数学')
ORDER BY sno;
-- 2. NOT EXISTS：查询没有数学成绩的学生姓名
sql
SELECT sno,sname FROM student
WHERE not EXISTS (    -- 和exists相反，是否不存在某结果
  SELECT 1 -- 无需返回具体列，1仅占位，效率更高
  FROM sc join course on sc.Cno=course.Cno
  WHERE sc.sno = student.sno AND course.cname = '数学')
	ORDER BY sno;
-- 3. HAVING中使用关联子查询：查询班级数学平均分高于全校数学平均分的班级
sql
SELECT class, AVG(sc.score) AS class_avg 
FROM student  JOIN sc ON student.sno = sc.sno 
join course on sc.Cno=course.Cno
WHERE course.Cname = '数学' 
GROUP BY class 
HAVING AVG(sc.score) > (SELECT AVG(score) FROM sc join course on sc.Cno=course.Cno WHERE cname = '数学');
四、子查询的常见坑与优化技巧

1. 避免 NOT IN 陷阱（NULL 值影响）
NOT IN 遇到子查询结果包含 NULL 时，会返回空集（因为 NULL 的比较结果是 UNKNOWN），建议用 NOT EXISTS 替代：
sql
-- 错误示例（若score.stu_id有NULL，结果为空）
sql
SELECT name FROM student WHERE id NOT IN (SELECT stu_id FROM score WHERE subject = '语文');
-- 正确示例（不受NULL影响，且性能更好）
sql
SELECT name FROM student s 
WHERE NOT EXISTS (
  SELECT 1 FROM score sc WHERE sc.stu_id = s.id AND sc.subject = '语文'
);
2. 关联子查询优化为 JOIN
关联子查询（尤其是 EXISTS）可替换为 JOIN，通常效率更高（JOIN 利用索引，避免逐行执行子查询）：
sql
-- 原EXISTS查询
sql
SELECT name FROM student s 
WHERE EXISTS (SELECT 1 FROM score sc WHERE sc.stu_id = s.id AND sc.subject = '数学');
-- 优化为JOIN（DISTINCT去重）
sql
SELECT DISTINCT s.name 
FROM student s JOIN score sc ON s.id = sc.stu_id 
WHERE sc.subject = '数学';
/*
3. 表子查询加索引 / 简化嵌套
子查询的关联列（如 sno）加索引，减少扫描；
多层嵌套子查询拆分为临时表或 JOIN，降低执行复杂度；
子查询结果集过大时，用 LIMIT 限制行数（仅适用于业务允许的场景）。
4. 优先用标量子查询替代列子查询
标量子查询执行效率更高，若业务逻辑允许，优先选择标量结果。
五、子查询的适用场景总结
  场景	      推荐子查询类型	     替代方案
单值条件过滤	标量子查询	         直接 JOIN
多值条件过滤	列子查询（IN/ANY）	  JOIN + IN
多列条件匹配	行子查询	多条件      AND
临时表统计	  表子查询（FROM）	    临时表 / CTE
存在性判断	  关联子查询（EXISTS）	JOIN + DISTINCT
六、核心总结
子查询是 “嵌套查询”，逻辑清晰但需注意性能；
按返回结果分：标量、列、行、表子查询；按关联方式分：关联 / 非关联子查询；
EXISTS 比 NOT IN 更可靠（避免 NULL 陷阱），JOIN 比关联子查询更高效；
子查询必须用 () 包裹，FROM 子句中的表子查询必须加别名。
实际开发中，优先用 JOIN 替代复杂关联子查询，仅在逻辑极复杂、JOIN 难以实现时使用子查询，兼顾可读性和性能。
*/
←
上一节 8. 多表查询（连接）
下一节 10. 流程控制函数（IF / CASE）
→