学习目标

学完本章后，你将能够：

• 理解子查询的概念及其在 SQL 中的作用
• 掌握不同类型的子查询，包括标量子查询、行子查询、表子查询
• 应用相关子查询解决复杂的数据查询问题
• 使用 EXISTS 和 NOT EXISTS 子查询检查数据存在性
• 了解子查询的性能优化技巧

子查询可以在一个查询语句内部嵌套另一个查询语句。通过子查询，可以实现更复杂的数据筛选和处理逻辑，解决许多单一查询难以完成的任务。

7.1 子查询基础

子查询（Subquery）是嵌套在另一个 SQL 查询中的 SELECT 语句。子查询可以出现在主查询的多个位置，如 SELECT、FROM、WHERE 或 HAVING 子句中。

子查询的类型

根据返回结果的不同，子查询可以分为以下几种类型：

1. 标量子查询（Scalar Subquery）：返回单个值（一行一列）
2. 行子查询（Row Subquery）：返回一行多列
3. 表子查询（Table Subquery）：返回多行多列，相当于一个临时表

根据与外部查询的关系，子查询可以分为：

1. 非相关子查询：子查询独立执行，不引用外部查询的任何值
2. 相关子查询：子查询引用外部查询中的值

WHERE 子句中的子查询

子查询最常见的用途之一是在 WHERE 子句中作为过滤条件。

标量子查询示例

假设我们有两个表：orders（订单表）和 users（用户表）。

orders 表：

order_id	user_id	order_date	total_amount
1	101	2025-06-01	299
2	102	2025-06-02	99
3	101	2025-06-05	199
4	103	2025-06-10	399
5	102	2025-06-15	149

users 表：

user_id	username	email	registration_date
101	鱼皮	yupi@example.com	2025-01-15
102	代码鸭	duck@example.com	2025-02-20
103	算法达人	algo@example.com	2025-03-25
104	面试鸭	interview@example.com	2025-04-30

查找订单金额大于平均订单金额的所有订单：

▼
sql
复制代码
SELECT order_id, user_id, total_amount
FROM orders
WHERE total_amount > (SELECT AVG(total_amount) FROM orders);

这里的子查询 SELECT AVG(total_amount) FROM orders 计算所有订单的平均金额，并将结果（一个单一的值）用作比较标准。

结果：

order_id	user_id	total_amount
1	101	299
4	103	399

IN 运算符与子查询

当子查询返回多个值时，我们可以使用 IN 运算符：

查找鱼皮下的所有订单：

▼
sql
复制代码
SELECT order_id, order_date, total_amount
FROM orders
WHERE user_id IN (SELECT user_id FROM users WHERE username = '鱼皮');

结果：

order_id	order_date	total_amount
1	2025-06-01	299
3	2025-06-05	199

FROM 子句中的子查询

当在 FROM 子句中使用子查询时，子查询结果作为一个临时表（也称为派生表）使用。

计算每个用户的订单总金额：

▼
sql
复制代码
SELECT u.username, o.total_spent
FROM users u
JOIN (
    SELECT user_id, SUM(total_amount) AS total_spent
    FROM orders
    GROUP BY user_id
) o ON u.user_id = o.user_id;

在这个例子中，内部子查询创建了一个包含每个用户订单总金额的临时表，然后主查询将这个临时表与 users 表关联。

结果：

username	total_spent
鱼皮	498
代码鸭	248
算法达人	399

SELECT 子句中的子查询

子查询也可以在 SELECT 子句中使用，为每一行生成一个值：

查询每个用户及其最近一次订单的金额：

▼
sql
复制代码
SELECT u.username,
       (SELECT MAX(order_date) FROM orders WHERE user_id = u.user_id) AS last_order_date,
       (SELECT total_amount FROM orders WHERE user_id = u.user_id ORDER BY order_date DESC LIMIT 1) AS last_order_amount
FROM users u;

这个查询为每个用户找出最近订单的日期和金额。注意，这里使用的是相关子查询，因为内部查询引用了外部查询的 u.user_id。

结果：

username	last_order_date	last_order_amount
鱼皮	2025-06-05	199
代码鸭	2025-06-15	149
算法达人	2025-06-10	399
面试鸭	NULL	NULL

7.2 相关子查询

相关子查询是指子查询引用了外部查询的值的查询。与非相关子查询不同，相关子查询不能独立执行，必须为外部查询的每一行重新执行一次。

基本语法与概念

在相关子查询中，内部查询包含对外部查询的引用，通常采用以下形式：

▼
sql
复制代码
SELECT column1, column2, ...
FROM table1 outer
WHERE column1 operator (SELECT column1, column2, ... 
                        FROM table2
                        WHERE table2.column = outer.column);

相关子查询示例

查找每个用户消费金额高于该用户平均消费的订单：

▼
sql
复制代码
SELECT o.order_id, o.user_id, o.order_date, o.total_amount
FROM orders o
WHERE o.total_amount > (
    SELECT AVG(total_amount)
    FROM orders
    WHERE user_id = o.user_id
);

在这个查询中，内部子查询计算每个用户的平均订单金额，并将其与该用户的每个订单金额进行比较。这个内部查询会为外部查询的每一行执行一次。

结果：

order_id	user_id	order_date	total_amount
1	101	2025-06-01	299
5	102	2025-06-15	149

查找每个用户最近的订单：

▼
sql
复制代码
SELECT o.order_id, o.user_id, o.order_date, o.total_amount
FROM orders o
WHERE o.order_date = (
    SELECT MAX(order_date)
    FROM orders
    WHERE user_id = o.user_id
);

这个查询查找每个用户的最新订单。在内部子查询中确定每个用户的最新订单日期。

结果：

order_id	user_id	order_date	total_amount
3	101	2025-06-05	199
5	102	2025-06-15	149
4	103	2025-06-10	399

相关子查询的应用

相关子查询特别适用于以下场景：

1. 查找组内最大/最小/最新等记录：如上例中查找每个用户的最新订单。
2. 复杂的比较操作：如比较某个值与分组平均值的关系。
3. 数据验证和清洗：检测不符合特定规则的数据。

但由于子查询需要为外部查询的每一行执行一次，性能上可能比非相关子查询更差，尤其是在处理大量数据时。

7.3 EXISTS 子查询

EXISTS 运算符用于检查子查询是否返回任何行。它不关心子查询返回什么值，只关心是否有结果。如果子查询至少返回一行，则 EXISTS 运算符返回 TRUE，否则返回 FALSE。

基本语法

▼
sql
复制代码
SELECT column1, column2, ...
FROM table1
WHERE EXISTS (SELECT column1 FROM table2 WHERE condition);

EXISTS 子查询通常是相关的，即内部查询引用外部查询的列。

EXISTS 子查询示例

查找有订单记录的用户：

▼
sql
复制代码
SELECT u.user_id, u.username
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM orders
    WHERE user_id = u.user_id
);

在这个查询中，我们使用 EXISTS 来检查用户是否有任何订单。注意子查询中的 SELECT 1 — 我们可以选择任何值，因为 EXISTS 只关心是否有结果，而不关心结果的具体内容。

结果：

user_id	username
101	鱼皮
102	代码鸭
103	算法达人

查找没有订单记录的用户（使用 NOT EXISTS）：

▼
sql
复制代码
SELECT u.user_id, u.username
FROM users u
WHERE NOT EXISTS (
    SELECT 1
    FROM orders
    WHERE user_id = u.user_id
);

结果：

user_id	username
104	面试鸭

IN 与 EXISTS 的比较

虽然 IN 和 EXISTS 都可以用于检查某个值是否在一组值中。但是它们的执行方式不同：

• IN 检查某个值是否等于子查询返回的某个值。
• EXISTS 检查子查询是否返回任何行。

一般来说：

• 当子查询结果集较小时，IN 可能更快。
• 当子查询结果集较大时，EXISTS 可能更快，因为它可以在找到第一个匹配项后就停止搜索。

例如，以下两个查询在功能上相同，但性能在不同数据量的情况下不同：

▼
sql
复制代码
-- 使用 IN
SELECT u.user_id, u.username
FROM users u
WHERE u.user_id IN (SELECT user_id FROM orders);

-- 使用 EXISTS
SELECT u.user_id, u.username
FROM users u
WHERE EXISTS (SELECT 1 FROM orders WHERE user_id = u.user_id);

EXISTS 的应用场景

EXISTS 子查询在以下场景特别有用：

1. 数据完整性检查：查找没有相关记录的数据（如没有订单的用户）。
2. 复杂关联条件：当关联条件不仅仅是简单的相等条件时。
3. 避免重复：当只需要知道是否存在匹配，而不需要具体值时。
4. 处理NULL值：EXISTS 不受 NULL 值影响，而 IN 在处理 NULL 值时可能有问题。

7.4 子查询的性能优化

虽然子查询功能非常强大，但如果使用不当，可能会导致性能问题。以下是一些优化子查询性能的技巧：

1. 尽量使用 JOIN 替代子查询

在许多情况下，JOIN 操作比子查询更高效。例如：

▼
sql
复制代码
-- 使用子查询
SELECT u.username, (SELECT COUNT(*) FROM orders WHERE user_id = u.user_id) AS order_count
FROM users u;

-- 使用 JOIN（通常更高效）
SELECT u.username, COUNT(o.order_id) AS order_count
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.username;

2. 避免相关子查询

相关子查询通常需要为外部查询的每一行执行一次，可能导致性能问题。尽可能使用非相关子查询或 JOIN 操作。

3. 在子查询中限制返回的列和行

不要在子查询中使用 SELECT *，只选择必要的列。同时，如果可能，使用 LIMIT 或其他条件限制子查询返回的行数。

▼
sql
复制代码
-- 不良做法
WHERE id IN (SELECT id FROM large_table);

-- 更好的做法
WHERE id IN (SELECT id FROM large_table WHERE condition LIMIT 1000);

4. 使用索引

在子查询中使用的列建立适当的索引，特别是用于连接和过滤的列。

5. 考虑查询执行顺序

理解 SQL 执行的顺序，调整查询结构有时可以提高性能：

▼
sql
复制代码
-- 可能较慢，因为子查询需要为每行执行
SELECT *
FROM users
WHERE user_id IN (SELECT user_id FROM orders WHERE total_amount > 100);

-- 可能更快，过滤在子查询中进行
SELECT *
FROM users
WHERE user_id IN (
    SELECT DISTINCT user_id 
    FROM orders 
    WHERE total_amount > 100
);

6. 使用临时表或公共表表达式(CTE)

对于复杂的嵌套子查询，可以使用临时表或 CTE（WITH 子句）来提高可读性和性能：

▼
sql
复制代码
-- 使用 WITH 子句（CTE）
WITH high_value_orders AS (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    WHERE total_amount > 200
    GROUP BY user_id
)
SELECT u.username, hvo.order_count
FROM users u
JOIN high_value_orders hvo ON u.user_id = hvo.user_id;

7. 分析和测试

数据库提供了执行计划工具（如 EXPLAIN），专门用于分析查询性能，可以尝试不同的查询策略，比较它们的执行效率。

▼
sql
复制代码
EXPLAIN SELECT * FROM users WHERE user_id IN (SELECT user_id FROM orders);

但优化策略可能因数据库系统、表结构、数据分布和索引而异。在实际应用中，建议结合具体情况和测试结果来选择最优的查询方式。

练习题

练习 1：基本子查询应用

假设有一个 course_ratings 表，记录了课程的用户评分，包含 rating_id, course_id, user_id, rating（评分，1-5分）, comment 等字段。编写一个查询，找出评分高于平均评分的所有课程评价。

答案：

▼
sql
复制代码
SELECT rating_id, course_id, user_id, rating, comment
FROM course_ratings
WHERE rating > (SELECT AVG(rating) FROM course_ratings);

练习 2：相关子查询应用

使用 course_ratings 表和一个 courses 表（包含 course_id, title, instructor_id 等字段），编写一个查询，找出每个讲师的课程中评分最高的那条评价记录。

答案：

▼
sql
复制代码
SELECT cr.rating_id, c.instructor_id, c.course_id, c.title, cr.rating, cr.comment
FROM courses c
JOIN course_ratings cr ON c.course_id = cr.course_id
WHERE cr.rating = (
    SELECT MAX(rating)
    FROM course_ratings
    WHERE course_id = c.course_id
)
ORDER BY c.instructor_id, c.course_id;

练习 3：EXISTS 子查询应用

假设有一个 course_prerequisites 表，记录了课程的前置课程关系，包含 course_id, prerequisite_id 等字段。编写一个查询，找出没有任何前置课程要求的所有课程。

答案：

▼
sql
复制代码
SELECT c.course_id, c.title
FROM courses c
WHERE NOT EXISTS (
    SELECT 1
    FROM course_prerequisites cp
    WHERE cp.course_id = c.course_id
);