Skip to content
Lv1SQL 与数据库进度 7 / 12

第 7 章:子查询与 CTE ​

有些查询没法一步写出来:"买过书的客户有哪些""比平均价还贵的书""每个月的营收趋势"。子查询让你把一条查询的结果,当成另一条查询的输入。


学习目标 ​

  • 会用 IN、EXISTS、标量子查询三种形式
  • 说清 NOT IN 遇到 NULL 为什么会返回空集
  • 会用 WITH(CTE)把复杂查询拆成有名字的几步
  • 知道递归 CTE 能解决什么问题,什么时候该用

7.1 子查询是什么 ​

子查询就是写在另一个查询里的查询。根据它返回的形状,分三种用法:

形状用在哪典型写法
返回一列值IN / NOT INWHERE id IN (SELECT ...)
返回真/假WHERE 里的 EXISTSWHERE EXISTS (SELECT ...)
返回单个值参与比较或计算WHERE price > (SELECT AVG(price) ...)

一个直观的例子——"买过书的客户":

sql
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:

sql
-- 没下过单的客户(这个写法有问题,见下)
SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

在小书店数据上,这条恰好能返回孙七。但只要子查询的结果里出现 NULL,它就会永远返回空集:

sql
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:只问"有没有" ​

sql
SELECT c.name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

EXISTS 只关心子查询有没有返回行,不关心返回什么。所以子查询里写 SELECT 1 就够了,不需要列名。

取反:

sql
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,语义直白
子查询可能含 NULLEXISTS,不受影响
子查询要用到外层的列EXISTS,IN 做不到

7.4 标量子查询:把结果当值用 ​

子查询只返回一个值时(一行一列),可以直接当普通值参与比较:

sql
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 列表里:

sql
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:给中间结果起个名字 ​

查询一长,嵌套的子查询就没法读了:

sql
SELECT ...
FROM (SELECT ... FROM (SELECT ... FROM orders) ...) ...

WITH(也叫 CTE,Common Table Expression)允许你把中间结果命名,然后在主查询里当表用:

sql
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 可以串联 ​

sql
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:

sql
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 │
└───┘

拆开看三段:

  1. SELECT 1:起点,也叫锚点;
  2. UNION ALL SELECT n + 1 FROM seq WHERE n < 5:递归步,用上一轮的结果生成下一轮,WHERE 是终止条件;
  3. 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 贵得多。

👉 第 8 章:表设计与约束

本文阅读--次|全站访问--次|访客--人