第 7 章:子查询与 CTE
有些查询没法一步写出来:"买过书的客户有哪些""比平均价还贵的书""每个月的营收趋势"。子查询让你把一条查询的结果,当成另一条查询的输入。
学习目标
- 会用
IN、EXISTS、标量子查询三种形式 - 说清
NOT IN遇到 NULL 为什么会返回空集 - 会用
WITH(CTE)把复杂查询拆成有名字的几步 - 知道递归 CTE 能解决什么问题,什么时候该用
7.1 子查询是什么
子查询就是写在另一个查询里的查询。根据它返回的形状,分三种用法:
| 形状 | 用在哪 | 典型写法 |
|---|---|---|
| 返回一列值 | IN / NOT IN | WHERE id IN (SELECT ...) |
| 返回真/假 | WHERE 里的 EXISTS | WHERE EXISTS (SELECT ...) |
| 返回单个值 | 参与比较或计算 | WHERE price > (SELECT AVG(price) ...) |
一个直观的例子——"买过书的客户":
SELECT name FROM customers
WHERE id IN (SELECT DISTINCT customer_id FROM orders);IN 后面括号里就是子查询,它先算出所有出现过的 customer_id,外层再拿每个客户的 id 去比对。
7.2 IN 与 NOT IN:简单,但有个陷阱
IN 上面已经用过。取反是 NOT IN:
-- 没下过单的客户(这个写法有问题,见下)
SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);在小书店数据上,这条恰好能返回孙七。但只要子查询的结果里出现 NULL,它就会永远返回空集:
SELECT 1 NOT IN (2, NULL); -- NULL(不是 1)
SELECT 1 NOT IN (2, 3); -- 1(正常)原因还是三值逻辑:NOT IN 展开成 1 <> 2 OR 1 <> NULL。第一部分是 TRUE,第二部分是 NULL,TRUE OR NULL 结果是 TRUE——这条能过。但当被比较的值也在列表里找不到时,FALSE OR NULL 就是 NULL,被 WHERE 丢弃。
所以结论是:NOT IN 的子查询必须保证不返回 NULL,加个 WHERE customer_id IS NOT NULL 就行。但更稳的做法是用 NOT EXISTS。
7.3 EXISTS:只问"有没有"
SELECT c.name
FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id
);EXISTS 只关心子查询有没有返回行,不关心返回什么。所以子查询里写 SELECT 1 就够了,不需要列名。
取反:
SELECT c.name
FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id
);┌──────┐
│ name │
├──────┤
│ 孙七 │
└──────┘NOT EXISTS 不受 NULL 影响:它判断的是"这一行有没有匹配",不是"值在不在列表里",三值逻辑的坑绕过去了。
相关子查询
注意上面子查询里用了外层的 c.id——这叫相关子查询(correlated subquery)。它对每一行外层数据都要执行一次,逻辑上像嵌套循环。
听起来很慢,但数据库通常会把 EXISTS 改写成半连接(semi-join),实际性能和 JOIN 差不多。先写对,再看执行计划,不要凭感觉优化。
IN 和 EXISTS 怎么选
| 场景 | 推荐 |
|---|---|
| 子查询结果集小、去重后干净 | IN,语义直白 |
| 子查询可能含 NULL | EXISTS,不受影响 |
| 子查询要用到外层的列 | EXISTS,IN 做不到 |
7.4 标量子查询:把结果当值用
子查询只返回一个值时(一行一列),可以直接当普通值参与比较:
SELECT title, price
FROM books
WHERE price > (SELECT AVG(price) FROM books)
ORDER BY price DESC;┌──────────────────────┬───────┐
│ title │ price │
├──────────────────────┼───────┤
│ 深入理解计算机系统 │ 128.0 │
│ 数据库系统概念 │ 99.0 │
│ 平凡的世界 │ 98.0 │
└──────────────────────┴───────┘平均价约 70.6(565 / 8),高于它的只有三本。
标量子查询也可以出现在 SELECT 列表里:
SELECT
title,
price,
(SELECT ROUND(AVG(price), 2) FROM books) AS 全店均价,
ROUND(price - (SELECT AVG(price) FROM books), 2) AS 高于均价
FROM books
ORDER BY price DESC
LIMIT 3;这样每行都能带上"全店均价"这个参照值。
注意:标量子查询必须真的只返回一行。如果返回多行,SQLite 会取第一行不报错,PostgreSQL 会直接报 more than one row returned by a subquery。别依赖 SQLite 的宽容。
7.5 WITH:给中间结果起个名字
查询一长,嵌套的子查询就没法读了:
SELECT ...
FROM (SELECT ... FROM (SELECT ... FROM orders) ...) ...WITH(也叫 CTE,Common Table Expression)允许你把中间结果命名,然后在主查询里当表用:
WITH monthly AS (
SELECT
substr(o.ordered_at, 1, 7) AS 月份,
SUM(i.quantity * i.unit_price) AS 营收
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status <> 'refunded'
GROUP BY 月份
)
SELECT 月份, ROUND(营收, 2) AS 营收
FROM monthly
ORDER BY 月份;┌─────────┬────────┐
│ 月份 │ 营收 │
├─────────┼────────┤
│ 2025-06 │ 423.0 │
│ 2025-07 │ 378.0 │
│ 2025-08 │ 215.0 │
│ 2025-09 │ 434.0 │
│ 2025-10 │ 147.0 │
└─────────┴────────┘monthly 就像一个临时表,只在当前这条查询里存在。写起来的好处是从内到外一层层读,而不是从外往里剥洋葱。
多个 CTE 可以串联
WITH
paid_orders AS (
SELECT id, customer_id FROM orders WHERE status <> 'refunded'
),
customer_total AS (
SELECT o.customer_id, SUM(i.quantity * i.unit_price) AS amount
FROM paid_orders o
JOIN order_items i ON i.order_id = o.id
GROUP BY o.customer_id
)
SELECT c.name, ROUND(t.amount, 2) AS 消费额
FROM customer_total t
JOIN customers c ON c.id = t.customer_id
ORDER BY 消费额 DESC;后一个 CTE 可以引用前一个,读起来就是一条流水线:先筛订单 → 再算每人金额 → 最后拼客户名。
作用域提醒:CTE 的名字只在紧跟其后的那条语句里有效。写完
WITH ... SELECT一个语句结束,名字就没了。
7.6 递归 CTE:处理"自己包含自己"的结构
有些数据是树:分类的父分类、员工的上级、评论的回复。普通的 JOIN 只能连一层,要连到叶子就得反复写。递归 CTE 用一条 SQL 走完整棵树。
先看一个最小的形状——生成 1 到 5:
WITH RECURSIVE seq(n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM seq WHERE n < 5
)
SELECT n FROM seq;┌───┐
│ n │
├───┤
│ 1 │
│ 2 │
│ 3 │
│ 4 │
│ 5 │
└───┘拆开看三段:
SELECT 1:起点,也叫锚点;UNION ALL SELECT n + 1 FROM seq WHERE n < 5:递归步,用上一轮的结果生成下一轮,WHERE是终止条件;WITH RECURSIVE:声明这是递归 CTE(SQLite 和 PostgreSQL 都要求这个关键字)。
真实场景里,把 n 换成"父分类 id",把 n + 1 换成"查子分类",就是一棵分类树。再配一个 depth 列记录层级,就能做"面包屑导航"或"部门层级报表"。
递归 CTE 有两个实用限制要知道:
- 递归必须能终止,否则会一直算到资源耗尽;
- 深度大的树(几千层)性能会明显下降,实务中建议限制
depth。
这一节只要求你"看得懂、会写简单递归",复杂的树查询不必在这里死磕。
7.7 子查询、CTE、JOIN 怎么选
同一个需求往往有三种写法,选择标准不是"哪个高级",而是哪个读起来最不容易错:
| 需求 | 推荐写法 | 理由 |
|---|---|---|
| 按另一张表的条件筛行 | IN / EXISTS | 语义直接,"存在/不存在" |
| 需要另一张表的字段 | JOIN | 子查询拿不到列 |
| 多步计算、中间结果要复用 | WITH | 每步有名字,好排查 |
| 树形/层级 | 递归 CTE | 其他写法要写很多层 |
| 只要一个统计值当参照 | 标量子查询 | 不必为此 JOIN |
一个反例:为了用 EXISTS 而放弃 JOIN,结果主查询里还要再取一次书名——那就该直接 JOIN。子查询擅长"判断",JOIN 擅长"取数",别混着用。
本章小结
| 要点 | 说明 |
|---|---|
| 子查询三种形状 | 列、布尔、单值 |
NOT IN 遇 NULL 必空 | 子查询要过滤 NULL,或改用 NOT EXISTS |
EXISTS 只问有没有 | 不受三值逻辑影响,能引用外层列 |
| 标量子查询返回多行会出错 | SQLite 容忍,PostgreSQL 报错 |
WITH 给中间结果命名 | 从内到外读,比嵌套清晰 |
| 递归 CTE 三段式 | 锚点 + UNION ALL 递归步 + 终止条件 |
| 子查询判断,JOIN 取数 | 按语义选,不按"高级"选 |
下一章
查询部分到这里结束。从下一章开始换视角:不再问"怎么查",而是问"表该怎么设计"。主键选什么、外键要不要加、字段能不能为空、什么时候该故意冗余——这些决定一旦落库,改起来比改 SQL 贵得多。