第10章：子查询（嵌套查询，进阶）

子查询是一种强大的 SQL 技术，允许在一个查询语句中嵌套另一个查询语句。它可以简化复杂查询，提高 SQL 的灵活性和表达能力。

10.1 什么是子查询？

子查询（Subquery）是指在一个 SQL 查询语句中嵌套的另一个查询语句。子查询的结果可以被主查询使用，用于过滤、比较或作为临时表。

子查询的特点：

可以嵌套在 SELECT、FROM、WHERE、HAVING 等子句中
子查询必须用括号包围
子查询可以返回单个值、一行或多行数据

10.2 子查询的使用场景

10.2.1 WHERE 子句中的子查询

在 WHERE 子句中使用子查询，通常用于：

比较操作（=、>、<、>=、<=、!=）
集合操作（IN、NOT IN、EXISTS、NOT EXISTS）

示例：

sql

-- 查询成绩大于平均分的学生
SELECT * FROM students
WHERE score > (SELECT AVG(score) FROM students);

-- 查询与张三同班级的学生
SELECT * FROM students
WHERE class_id = (SELECT class_id FROM students WHERE name = '张三');

10.2.2 FROM 子句中的子查询

在 FROM 子句中使用子查询，将子查询的结果作为临时表：

sql

-- 将子查询结果作为临时表
SELECT * FROM (
    SELECT name, score FROM students WHERE score >= 80
) AS high_score_students;

-- 统计每个班级的平均分，并查询平均分高于85的班级
SELECT * FROM (
    SELECT class_id, AVG(score) AS avg_score 
    FROM students 
    GROUP BY class_id
) AS class_avg
WHERE avg_score > 85;

10.3 子查询关键字

10.3.1 IN 和 NOT IN

IN：检查值是否在子查询返回的集合中

sql

-- 查询在1班或2班的学生
SELECT * FROM students
WHERE class_id IN (SELECT id FROM classes WHERE name IN ('一班', '二班'));

-- 查询不在1班的学生
SELECT * FROM students
WHERE class_id NOT IN (SELECT id FROM classes WHERE name = '一班');

10.3.2 EXISTS 和 NOT EXISTS

EXISTS：检查子查询是否返回结果（只要有一行就返回 TRUE）

sql

-- 查询存在不及格学生的班级
SELECT * FROM classes c
WHERE EXISTS (
    SELECT 1 FROM students s 
    WHERE s.class_id = c.id AND s.score < 60
);

-- 查询不存在不及格学生的班级
SELECT * FROM classes c
WHERE NOT EXISTS (
    SELECT 1 FROM students s 
    WHERE s.class_id = c.id AND s.score < 60
);

10.4 子查询的类型

10.4.1 标量子查询

返回单个值的子查询：

sql

-- 查询成绩最高的学生
SELECT * FROM students
WHERE score = (SELECT MAX(score) FROM students);

-- 查询年龄最小的学生
SELECT * FROM students
WHERE age = (SELECT MIN(age) FROM students);

10.4.2 列子查询

返回单列多行的子查询：

sql

-- 查询成绩高于所有学生平均分的学生
SELECT * FROM students
WHERE score > ALL (SELECT AVG(score) FROM students GROUP BY class_id);

-- 查询成绩高于任一班级平均分的学生
SELECT * FROM students
WHERE score > ANY (SELECT AVG(score) FROM students GROUP BY class_id);

10.4.3 行子查询

返回一行多列的子查询：

sql

-- 查询与张三年龄和班级都相同的学生
SELECT * FROM students
WHERE (age, class_id) = (SELECT age, class_id FROM students WHERE name = '张三');

10.5 实操案例

案例1：子查询实现复杂条件查询

场景： 查询每个班级中成绩最高的学生

sql

-- 方法1：使用子查询
SELECT s1.* 
FROM students s1
WHERE s1.score = (
    SELECT MAX(s2.score) 
    FROM students s2 
    WHERE s2.class_id = s1.class_id
);

-- 方法2：使用关联子查询
SELECT s1.*
FROM students s1
JOIN (
    SELECT class_id, MAX(score) AS max_score
    FROM students
    GROUP BY class_id
) s2 ON s1.class_id = s2.class_id AND s1.score = s2.max_score;

案例2：子查询与表连接对比

场景： 查询有订单的商品

使用子查询：

sql

SELECT * FROM products
WHERE id IN (SELECT product_id FROM orders);

使用表连接：

sql

SELECT DISTINCT p.* 
FROM products p
JOIN orders o ON p.id = o.product_id;

使用 EXISTS：

sql

SELECT * FROM products p
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.product_id = p.id
);

案例3：多层子查询

场景： 查询成绩排名前10%的学生

sql

-- 计算总学生数
SELECT * FROM students
WHERE score >= (
    SELECT score FROM (
        SELECT score FROM students 
        ORDER BY score DESC 
        LIMIT 1 OFFSET (SELECT COUNT(*) * 0.1 FROM students)
    ) AS top_10_percent
);

10.6 子查询的性能优化

10.6.1 子查询优化技巧

使用 EXISTS 代替 IN：当子查询结果集较大时，EXISTS 通常比 IN 更高效
避免多层嵌套：过多的子查询嵌套会降低性能
合理使用索引：确保子查询中使用的字段有索引
考虑使用 JOIN：某些情况下，JOIN 比子查询更高效

10.6.2 性能对比

场景	推荐方法	原因
检查存在性	EXISTS	只要找到第一个匹配就返回
集合包含	IN	适合结果集较小的情况
多表关联	JOIN	通常比子查询更高效
复杂过滤	子查询	逻辑更清晰

10.7 新手易错点

❌ 错误1：子查询语法错误

sql

-- 错误：子查询缺少括号
SELECT * FROM students WHERE score > SELECT AVG(score) FROM students;

-- 正确：添加括号
SELECT * FROM students WHERE score > (SELECT AVG(score) FROM students);

❌ 错误2：子查询结果与主查询不匹配

sql

-- 错误：子查询返回多行，主查询使用 = 比较
SELECT * FROM students WHERE class_id = (SELECT id FROM classes);

-- 正确：使用 IN
SELECT * FROM students WHERE class_id IN (SELECT id FROM classes);

❌ 错误3：子查询中引用外部表字段

sql

-- 错误：子查询中引用外部表字段时，需要正确关联
SELECT * FROM students s1
WHERE score > (SELECT AVG(score) FROM students);

-- 正确：如果需要按班级计算平均分
SELECT * FROM students s1
WHERE score > (SELECT AVG(score) FROM students s2 WHERE s2.class_id = s1.class_id);

❌ 错误4：子查询性能问题

sql

-- 错误：在 WHERE 子句中使用复杂子查询
SELECT * FROM orders
WHERE product_id IN (SELECT id FROM products WHERE price > 1000);

-- 优化：使用 JOIN
SELECT o.* 
FROM orders o
JOIN products p ON o.product_id = p.id
WHERE p.price > 1000;

10.8 表连接与子查询的选择

对比项	表连接	子查询
可读性	复杂查询时可能更清晰	逻辑层次分明
性能	通常更高效	某些情况性能较差
适用场景	多表关联、数据整合	复杂过滤、临时结果
灵活性	适合固定关联	适合动态条件

选择建议：

简单的多表关联：优先使用 JOIN
复杂的过滤条件：使用子查询
存在性检查：使用 EXISTS
性能要求高：测试两种方法，选择更高效的

10.9 本章小结

子查询类型	语法	用途
标量子查询	返回单个值	用于比较操作
列子查询	返回单列多行	用于 IN、ANY、ALL 操作
行子查询	返回一行多列	用于多列比较
表子查询	返回多行多列	作为临时表

常用子查询关键字：

IN：检查值是否在集合中
NOT IN：检查值是否不在集合中
EXISTS：检查子查询是否有结果
NOT EXISTS：检查子查询是否无结果
ANY：与子查询结果任一比较
ALL：与子查询结果所有比较

10.10 课后练习

使用子查询查询成绩高于平均分的学生
使用子查询查询与张三同班级的学生
使用子查询查询有订单的商品
使用 EXISTS 查询存在不及格学生的班级
使用子查询和 JOIN 两种方法查询每个班级的最高分学生

参考答案：

sql

-- 练习1
SELECT * FROM students WHERE score > (SELECT AVG(score) FROM students);

-- 练习2
SELECT * FROM students WHERE class_id = (SELECT class_id FROM students WHERE name = '张三');

-- 练习3
SELECT * FROM products WHERE id IN (SELECT product_id FROM orders);

-- 练习4
SELECT * FROM classes c WHERE EXISTS (SELECT 1 FROM students s WHERE s.class_id = c.id AND s.score < 60);

-- 练习5（子查询方法）
SELECT s1.* FROM students s1 WHERE s1.score = (SELECT MAX(s2.score) FROM students s2 WHERE s2.class_id = s1.class_id);

-- 练习5（JOIN方法）
SELECT s1.* FROM students s1 JOIN (SELECT class_id, MAX(score) AS max_score FROM students GROUP BY class_id) s2 ON s1.class_id = s2.class_id AND s1.score = s2.max_score;

第10章：子查询（嵌套查询，进阶） ​

10.1 什么是子查询？ ​

10.2 子查询的使用场景 ​

10.2.1 WHERE 子句中的子查询 ​

10.2.2 FROM 子句中的子查询 ​

10.3 子查询关键字 ​

10.3.1 IN 和 NOT IN ​

10.3.2 EXISTS 和 NOT EXISTS ​

10.4 子查询的类型 ​

10.4.1 标量子查询 ​

10.4.2 列子查询 ​

10.4.3 行子查询 ​

10.5 实操案例 ​

案例1：子查询实现复杂条件查询 ​

案例2：子查询与表连接对比 ​

案例3：多层子查询 ​

10.6 子查询的性能优化 ​

10.6.1 子查询优化技巧 ​

10.6.2 性能对比 ​

10.7 新手易错点 ​

❌ 错误1：子查询语法错误 ​

❌ 错误2：子查询结果与主查询不匹配 ​

❌ 错误3：子查询中引用外部表字段 ​

❌ 错误4：子查询性能问题 ​

10.8 表连接与子查询的选择 ​

10.9 本章小结 ​

10.10 课后练习 ​