子查询

子查询(Subquery)是把一个 SELECT 嵌套到另一个 SELECT 里的技巧。它让 SQL 能表达"基于查询结果的查询"——比如"查询比平均年龄大的学生"、"查询下过单的用户"。掌握子查询,你写的 SQL 会强上一个台阶。

1. 标量子查询(返回单个值)

标量子查询返回单个值,可以用在任何表达式能出现的地方:

-- 标量子查询:返回【单个值】的子查询
-- 可以用在任何表达式能出现的地方(SELECT、WHERE、计算列)

-- 查询比"全校平均年龄"大的学生
SELECT name, age
FROM students
WHERE age > (SELECT AVG(age) FROM students);

-- 在 SELECT 里用作计算列
SELECT
    name,
    age,
    age - (SELECT AVG(age) FROM students) AS age_diff
FROM students;

-- 在 UPDATE 里也常用
UPDATE students
SET age = (SELECT MAX(age) FROM students)
WHERE name = '小明';

这种用法很灵活,因为标量子查询的结果就是个"动态算出来的常量"。但要注意:子查询如果返回多行或多列会报错——必须是真正的"单个值"。

2. IN 子查询

IN 子查询判断一个值是否在子查询的结果集里,比手工列 IN (1, 2, 3) 更灵活——列表是动态算出来的:

-- IN 子查询:判断一个值是否在子查询的结果集里
-- 比手工列 IN 列表更灵活(列表是动态算出来的)

-- 查询"一班"的所有学生
SELECT name FROM students
WHERE class_id IN (
    SELECT id FROM classes WHERE class_name = '一班'
);

-- 查询"有下过订单"的用户
SELECT name FROM users
WHERE id IN (SELECT DISTINCT user_id FROM orders);

-- NOT IN:反过来,不在子查询结果里
SELECT name FROM users
WHERE id NOT IN (SELECT DISTINCT user_id FROM orders);
-- 含义:【从未下过单】的用户

-- ⚠️ NOT IN 的坑:如果子查询结果里有 NULL,一行都不会返回
-- 改用 NOT EXISTS 更安全

NOT IN 的坑:如果子查询结果里包含 NULL,整条查询会一行都不返回(因为 x <> NULL 的结果是 NULL,不是 true)。遇到这种情况,要么先 WHERE col IS NOT NULL 过滤掉,要么改用 NOT EXISTS。

3. ANY / ALL 运算符

ANY 和 ALL 配合比较运算符使用,语义容易混:

-- ANY / ALL:配合比较运算符
-- > ANY  大于【任意一个】(即大于最小值)
-- > ALL  大于【所有】(即大于最大值)
-- < ANY  小于任意一个(即小于最大值)
-- < ALL  小于所有(即小于最小值)

-- age > ANY (1, 2, 3)  等价于 age > 1
-- age > ALL (1, 2, 3)  等价于 age > 3

-- 比一班任意一个学生年龄都大的学生
SELECT name, age FROM students
WHERE age > ANY (
    SELECT age FROM students WHERE class_id = 1
);

-- 比一班【所有】学生年龄都大的学生
SELECT name, age FROM students
WHERE age > ALL (
    SELECT age FROM students WHERE class_id = 1
);

-- 实际用得不多,但理解语义很重要

记忆窍门:ANY = 任一 = 最低标准;ALL = 所有 = 最高标准。> ANY (1,2,3) 等价于 > 1(只要大于最小值即可),> ALL (1,2,3) 等价于 > 3(必须大于最大值)。实际项目用得不多,但面试常考。

4. EXISTS 子查询

EXISTS 判断子查询是否返回任何行,返回 true/false。通常配合相关子查询使用:

-- EXISTS:判断子查询是否【返回任何行】,返回 true/false
-- 通常和相关子查询配合(子查询里引用外层的列)

-- 查询"有下过订单"的用户
SELECT u.name
FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id
);

-- NOT EXISTS:反过来,从没下过单的用户
SELECT u.name
FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id
);

-- IN vs EXISTS
-- 1. 语义等价,但性能可能不同
-- 2. 子查询结果集大 → 用 EXISTS(短路,找到一行就停)
-- 3. 子查询结果集小 → 用 IN
-- 4. NOT IN 有 NULL 坑 → 改用 NOT EXISTS

IN vs EXISTS:语义等价,但性能可能不同。子查询结果集大时用 EXISTS(短路,找到一行就停);结果集小时用 IN。NOT IN 有 NULL 坑,所以所有 NOT IN 场景建议改用 NOT EXISTS。

5. 相关子查询

相关子查询(Correlated Subquery)是子查询里引用了外层查询的列——每扫描外层一行,子查询都会重新执行一次:

-- 相关子查询:子查询里引用了外层查询的列
-- 每扫描外层一行,子查询都会重新执行一次(可能慢)

-- 查询每个班【年龄最大】的学生
SELECT name, class_id, age
FROM students s
WHERE age = (
    SELECT MAX(age)
    FROM students
    WHERE class_id = s.class_id    -- 引用外层的 s.class_id
);

-- 每个用户的【订单数】(用相关子查询)
SELECT
    u.name,
    (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;

-- ⚠️ 相关子查询可能很慢(外层 N 行 × 子查询 1 次)
-- 大数据量场景,常改用 JOIN + GROUP BY 性能更好

这种写法语义清晰,但可能很慢(外层 N 行,子查询就跑 N 次)。大数据量场景,常改用 JOIN + GROUP BY,性能可能好几个数量级。

6. 派生表(FROM 里的子查询)

子查询放在 FROM 子句里,作为临时表使用,必须起别名:

-- 派生表(derived table):子查询放在 FROM 里,作为临时表
-- 必须起别名

-- 先算每个用户的订单数,再筛出"下单 ≥ 5 次"的
SELECT user_id, order_count
FROM (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
) AS user_stats
WHERE order_count >= 5;

-- 多层嵌套(可读性差,慎用)
SELECT * FROM (
    SELECT * FROM (
        SELECT * FROM students WHERE age >= 18
    ) AS t1 WHERE class_id = 1
) AS t2 WHERE name LIKE '小%';

派生表让"先聚合再过滤"这种需求有了优雅写法。但多层嵌套可读性很差——三层以上的派生表,后期维护是噩梦。这种场景请用 CTE。

7. CTE(WITH 子句,推荐)

CTE(Common Table Expression) 用 WITH 把子查询定义为"临时视图",扁平化、可读性大幅提升。现代 SQL 优先用 CTE 替代派生表:

-- CTE(Common Table Expression,公共表表达式)
-- 用 WITH 把子查询定义为"临时视图",可读性大幅提升

-- 语法:WITH 名字 AS (子查询) 主查询
WITH user_orders AS (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
)
SELECT u.name, uo.order_count
FROM users u
JOIN user_orders uo ON u.id = uo.user_id
WHERE uo.order_count >= 5;

-- 多个 CTE 一起定义,逻辑清晰
WITH
active_users AS (SELECT id, name FROM users WHERE status = 'active'),
user_stats AS (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    GROUP BY user_id
)
SELECT au.name, us.total
FROM active_users au
JOIN user_stats us ON au.id = us.user_id
ORDER BY us.total DESC;

-- CTE vs 派生表:
-- 1. CTE 可读性更好(扁平,不嵌套)
-- 2. CTE 可以多次引用(派生表只能用一次)
-- 3. 现代 SQL 推荐 CTE 优先

CTE 相比派生表有三大优势:(1) 扁平结构,不嵌套,逻辑步骤一目了然;(2) 可多次引用,派生表只能用一次;(3) 支持递归,处理树形数据的利器。

8. 递归 CTE(处理层级数据)

递归 CTE 是 CTE 的进阶用法,处理树形/层级数据(组织架构、评论嵌套、文件目录)的利器。MySQL 8.0+、PostgreSQL、SQLite 都支持:

-- 递归 CTE:处理树形 / 层级数据
-- MySQL 8.0+ / PostgreSQL / SQLite 支持

-- 场景:组织架构,每个员工有 manager_id,要查某人的所有下属
WITH RECURSIVE subordinates AS (
    -- 锚点:起始行
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE id = 1                       -- 从 id=1 的人开始

    UNION ALL

    -- 递归:每一轮找上一轮的下属
    SELECT e.id, e.name, e.manager_id, s.level + 1
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates ORDER BY level;

-- 没有 RECURSIVE 之前,要存路径字段或多次自连接
-- RECURSIVE CTE 是处理层级数据的现代标准方案

递归 CTE 的结构是锚点查询 + UNION ALL + 递归查询。锚点是起始行(比如顶层老板),递归查询在每一轮基于上一轮结果往下找一层。在没有 RECURSIVE 之前,处理层级要存"路径字段"或写 N 次自连接,非常痛苦。

子查询 vs JOIN:什么时候用哪个

子查询和 JOIN 经常能解决同样问题,选哪个看场景:

用 JOIN:需要从多张表取多个列(行级数据合并)。
用子查询:只需要做"存在性判断"(EXISTS)或拿个聚合值。
用 CTE:逻辑复杂、需要分步表达、可读性优先。
避免:多层嵌套的派生表、相关子查询在大表上跑。

经验法则:JOIN 性能通常更好,但子查询表达更直观。两者混用是日常。

小结

子查询让 SQL 有了"分层思考"的能力。这一篇你学了标量子查询、IN、EXISTS、ANY/ALL、相关子查询、派生表、CTE、递归 CTE——这套组合拳基本能解决任意复杂的查询需求。下一篇我们看 SQL 的内置函数库。

← 上一篇 GROUP BY 分组

下一篇 SQL 函数 →

✈️💬