数据库基础体系 · 第 70/139 篇。文章以各产品官方稳定版本的公开语义为准；示例会明确引擎、事务与部署边界。

SQL 子查询与 CTE：相关子查询、递归、物化和优化边界

子查询（subquery）是出现在另一条 SQL 表达式或查询中的查询。CTE（Common Table Expression，公共表表达式）则使用 WITH 为一个查询结果命名，再在后续查询中引用它。

两者都能表达“先得到一组中间结果，再继续计算”，但它们不是简单的语法别名：

• 子查询通常嵌套在 WHERE、FROM、SELECT 等位置；
• CTE 位于语句开头，拥有名字，可以被同一条语句后续部分引用；
• 相关子查询依赖外层当前行，可能表现为逐行计算，也可能被优化器改写；
• 递归 CTE 用固定点迭代表达层级树、图遍历和依赖关系；
• CTE 是否物化，决定中间结果是否实际落地、是否复用，以及优化器能否继续穿透查询边界。

这些概念都建立在几个基础语义之上：关系、三值逻辑、集合与多重集，以及查询块之间的数据依赖。

---

一、先区分查询块、结果集和执行计划

下面是一条包含子查询的语句：

SELECT c.id, c.name
FROM customers AS c
WHERE c.id IN (
    SELECT o.customer_id
    FROM orders AS o
    WHERE o.status = 'paid'
);

这里有两个查询块：

1. 外层查询块读取 customers；
2. 内层查询块读取 orders，返回一列 customer_id。

从逻辑上看，IN 要判断的是：

$$c.id \in \{o.customer\_id \mid o.status = 'paid'\}$$

这描述的是结果语义，而不是执行方式。数据库可能：

• 先执行内层查询并建立哈希集合；
• 将其改写为半连接（semi join）；
• 对外层每一行执行一次查找；
• 采用其他等价计划。

因此，“写成子查询”不等于“一定嵌套循环”，“写成 Join”也不等于“一定先连接再过滤”。SQL 负责声明结果，优化器负责选择执行计划，但优化器只能在语义允许的边界内改写。

CTE 的例子如下：

WITH paid_customers AS (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE status = 'paid'
)
SELECT c.id, c.name
FROM customers AS c
JOIN paid_customers AS p
  ON p.customer_id = c.id;

paid_customers 是一个命名查询块。它的名字在当前语句中可见，但不会自动成为永久表，也不会跨语句存在。

1. CTE 的作用域

CTE 通常只对紧随其后的一个顶层语句有效：

WITH x AS (
    SELECT 1 AS n
)
SELECT * FROM x;

下面的语句不能继续使用 x：

SELECT * FROM x; -- x 不存在

一个 CTE 可以引用前面定义的 CTE：

WITH
paid_orders AS (
    SELECT *
    FROM orders
    WHERE status = 'paid'
),
customer_totals AS (
    SELECT customer_id, SUM(amount) AS total_amount
    FROM paid_orders
    GROUP BY customer_id
)
SELECT *
FROM customer_totals;

这里存在明确的数据流：

orders
  -> paid_orders
  -> customer_totals
  -> 最终查询

在 PostgreSQL 和 MySQL 8.4 中，CTE 也可以引用基础表、视图以及前面的 CTE。是否实际生成临时结果，则是另一个问题，不能从这种书写形式直接判断。

---

二、标量子查询：必须满足单值约束

标量子查询（scalar subquery）出现在需要一个值的位置，例如 SELECT 列表、比较运算符的一侧或表达式内部：

SELECT
    c.id,
    c.name,
    (
        SELECT MAX(o.amount)
        FROM orders AS o
        WHERE o.customer_id = c.id
    ) AS max_order_amount
FROM customers AS c;

这个子查询对外层每个客户返回一个值：

• 有订单：返回最大金额；
• 没有订单：MAX 对空集合返回 NULL；
• 即使有多条订单，聚合也会把它们压缩成一个值。

标量子查询的正式要求是：结果最多一行一列。

SELECT
    c.id,
    (
        SELECT o.amount
        FROM orders AS o
        WHERE o.customer_id = c.id
    ) AS amount
FROM customers AS c;

如果某个客户有两条订单，这条语句会失败，而不是任意选择一条。PostgreSQL 和 MySQL 都会报告“子查询返回多行”一类错误。

“没有行”和“返回 NULL”不是一回事

下面两个子查询从结果上都可能得到 NULL，但原因不同：

-- 没有匹配行
SELECT (
    SELECT o.amount
    FROM orders AS o
    WHERE o.id = -1
);

-- 有一行，但该行的 amount 为 NULL
SELECT (
    SELECT o.amount
    FROM orders AS o
    WHERE o.id = 1
);

在标量上下文中，零行通常转换为 NULL；一行一列且该列为 NULL，也是 NULL。如果业务上必须区分两者，应额外查询存在性，或使用 COUNT(*)、EXISTS 等表达式。

例如，先限定订单唯一，再取值：

SELECT
    c.id,
    (
        SELECT o.amount
        FROM orders AS o
        WHERE o.customer_id = c.id
        ORDER BY o.created_at DESC, o.id DESC
        LIMIT 1
    ) AS latest_amount
FROM customers AS c;

这里 ORDER BY 和唯一的排序补充列 o.id 很重要。只写 ORDER BY created_at DESC LIMIT 1 时，如果时间相同，哪一行被选中可能不稳定。

---

三、IN、EXISTS、ANY、ALL 与 NULL

1. IN 表示集合成员关系

SELECT c.*
FROM customers AS c
WHERE c.id IN (
    SELECT o.customer_id
    FROM orders AS o
    WHERE o.status = 'paid'
);

这可以理解为：

客户 id 是否等于子查询结果中的任意一个非空值

它通常适合表达“是否属于某个结果集”。

2. EXISTS 只关心是否存在行

SELECT c.*
FROM customers AS c
WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.id
      AND o.status = 'paid'
);

SELECT 1 并不意味着数据库真的只需要构造常量 1；EXISTS 的语义是只判断是否存在至少一行。找到第一行后，执行通常可以停止继续扫描该子查询。

EXISTS 特别适合半连接语义：外层行最多保留一次，不会因为匹配到多条订单而重复。

对比普通 Join：

SELECT c.*
FROM customers AS c
JOIN orders AS o
  ON o.customer_id = c.id
WHERE o.status = 'paid';

如果一个客户有三条已支付订单，普通 Join 会产生三行客户记录。若只想保留客户，应使用 EXISTS，或：

SELECT DISTINCT c.*
FROM customers AS c
JOIN orders AS o
  ON o.customer_id = c.id
WHERE o.status = 'paid';

但 DISTINCT 是事后去重；EXISTS 直接表达“存在性”，语义更准确，也可能避免生成重复中间行。

3. NULL 会使 IN 产生 UNKNOWN

SQL 的布尔判断不是二值，而是三值：

• TRUE
• FALSE
• UNKNOWN

WHERE 只保留结果为 TRUE 的行。

考虑：

SELECT 1
WHERE 5 IN (5, NULL);

结果为 TRUE，因为已经找到相等值。

但：

SELECT 1
WHERE 6 IN (5, NULL);

结果不是 FALSE，而是 UNKNOWN：它既没有匹配 5，又无法断言它不等于 NULL。

因此，下面的反连接写法存在经典陷阱：

SELECT c.*
FROM customers AS c
WHERE c.id NOT IN (
    SELECT o.customer_id
    FROM orders AS o
);

只要子查询结果中有一个 NULL，NOT IN 可能使所有外层行都无法通过过滤。

更稳妥的反连接表达是：

SELECT c.*
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.id
);

如果确实使用 NOT IN，必须证明子查询列不可能为 NULL，或显式过滤：

WHERE c.id NOT IN (
    SELECT o.customer_id
    FROM orders AS o
    WHERE o.customer_id IS NOT NULL
);

ANY 和 ALL 是更一般的集合比较：

-- amount 大于订单集合中的至少一个金额
WHERE 100 > ANY (
    SELECT o.amount FROM orders AS o
);

-- amount 大于订单集合中的所有金额
WHERE 100 > ALL (
    SELECT o.amount FROM orders AS o
);

空集合也有语义差异：

• x = ANY (空集合) 为 FALSE；
• x = ALL (空集合) 为 TRUE，这是全称命题的真空真；
• 如果集合含 NULL，比较可能变为 UNKNOWN。

实际写查询时，应先确认“没有匹配行”应表示真、假还是未知。

---

四、相关子查询：外层行如何进入内层查询

相关子查询（correlated subquery）引用了外层查询块的列：

SELECT
    c.id,
    c.name
FROM customers AS c
WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.id
      AND o.status = 'paid'
);

c.id 不属于内层 orders，而来自当前正在判断的外层客户行。

逻辑执行可以按以下步骤理解：

假设客户表为：

id	name
1	Alice
2	Bob

订单表为：

id	customer_id	status
10	1	paid
11	1	pending

对客户 1：

内层条件：o.customer_id = 1 AND o.status = 'paid'
找到订单 10
EXISTS = TRUE

对客户 2：

内层条件：o.customer_id = 2 AND o.status = 'paid'
找不到行
EXISTS = FALSE

最终只返回 Alice。

相关不等于一定逐行执行

逻辑上可以把相关子查询理解成“外层每行传入参数”，但物理执行可能被优化器改写为：

• 半连接；
• 聚合后再连接；
• 哈希查找；
• 索引驱动的嵌套循环；
• 其他等价计划。

例如“每个客户的订单数”：

SELECT
    c.id,
    c.name,
    (
        SELECT COUNT(*)
        FROM orders AS o
        WHERE o.customer_id = c.id
    ) AS order_count
FROM customers AS c;

可以等价地写成：

SELECT
    c.id,
    c.name,
    COALESCE(x.order_count, 0) AS order_count
FROM customers AS c
LEFT JOIN (
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
) AS x
  ON x.customer_id = c.id;

两者需要注意一个语义差异：标量聚合子查询对没有订单的客户返回 0，而派生表的 LEFT JOIN 对没有匹配行返回 NULL，所以第二种写法需要 COALESCE。

优化器是否能自动完成这种去相关（decorrelation），取决于查询形状、索引、约束、版本和引擎。不能仅凭 SQL 外观断言性能。

相关聚合中的最大值陷阱

下面的查询是合法的：

SELECT
    c.id,
    (
        SELECT MAX(o.amount)
        FROM orders AS o
        WHERE o.customer_id = c.id
    ) AS max_amount
FROM customers AS c;

但如果要取“最大金额对应的订单编号”，不能分别求两个 MAX：

-- 可能得到不属于同一行的 id 和 amount
SELECT
    MAX(o.id),
    MAX(o.amount)
FROM orders AS o
WHERE o.customer_id = 1;

应使用排序限制：

SELECT
    c.id,
    (
        SELECT o.id
        FROM orders AS o
        WHERE o.customer_id = c.id
        ORDER BY o.amount DESC, o.id DESC
        LIMIT 1
    ) AS max_order_id
FROM customers AS c;

或者使用窗口函数：

WITH ranked_orders AS (
    SELECT
        o.*,
        ROW_NUMBER() OVER (
            PARTITION BY o.customer_id
            ORDER BY o.amount DESC, o.id DESC
        ) AS rn
    FROM orders AS o
)
SELECT c.id, r.id AS max_order_id
FROM customers AS c
LEFT JOIN ranked_orders AS r
  ON r.customer_id = c.id
 AND r.rn = 1;

这里窗口函数更适合一次性为所有客户排序；相关子查询更接近“对当前客户找一行”的表达。两者的性能边界应通过执行计划验证。

---

五、FROM 中的子查询、派生表和 LATERAL

放在 FROM 中的子查询称为派生表（derived table）：

SELECT x.customer_id, x.total_amount
FROM (
    SELECT customer_id, SUM(amount) AS total_amount
    FROM orders
    GROUP BY customer_id
) AS x
WHERE x.total_amount >= 1000;

它必须有别名。派生表通常不能直接引用同级查询中的外层列：

SELECT c.id, x.total_amount
FROM customers AS c
JOIN (
    SELECT SUM(amount) AS total_amount
    FROM orders
    WHERE customer_id = c.id -- 通常无权引用 c
) AS x ON TRUE;

需要显式使用 LATERAL 才能让 FROM 中的右侧子查询依赖左侧行：

SELECT
    c.id,
    x.order_id,
    x.amount
FROM customers AS c
LEFT JOIN LATERAL (
    SELECT o.id AS order_id, o.amount
    FROM orders AS o
    WHERE o.customer_id = c.id
    ORDER BY o.created_at DESC, o.id DESC
    LIMIT 1
) AS x ON TRUE;

含义是：对每个客户执行右侧查询，再把最多一行结果连接回来。

PostgreSQL 支持 LATERAL。MySQL 8.4 也支持横向派生表（lateral derived table），但具体语法和版本要求应以目标版本文档为准。若只需要判断存在性，EXISTS 通常更直接；若需要返回右侧多列，LATERAL 能避免重复书写相同的相关标量子查询。

---

六、CTE 的语义：命名查询块，不是自动临时表

最基本的 CTE：

WITH paid_orders AS (
    SELECT id, customer_id, amount
    FROM orders
    WHERE status = 'paid'
)
SELECT customer_id, SUM(amount)
FROM paid_orders
GROUP BY customer_id;

它的主要价值是：

1. 给复杂查询块命名；
2. 将一个逻辑步骤拆开；
3. 让同一中间结果在语句中被多处引用；
4. 支持递归；
5. 在 PostgreSQL 中可与数据修改语句组合。

但是，CTE 名字不代表：

• 一定创建磁盘临时表；
• 一定只执行一次；
• 一定建立索引；
• 一定比内联子查询快；
• 一定阻止优化器改写。

这些属于实现和优化行为，而不是 CTE 的普遍逻辑定义。

CTE 的列名和类型

可以显式指定列名：

WITH totals(customer_id, total_amount) AS (
    SELECT customer_id, SUM(amount)
    FROM orders
    GROUP BY customer_id
)
SELECT *
FROM totals;

递归 CTE 尤其建议显式写出列名，避免锚点项和递归项在类型、列顺序上产生歧义。

数据修改语句中的 CTE

PostgreSQL 支持数据修改 CTE，例如：

WITH moved AS (
    DELETE FROM staging_orders
    WHERE created_at < CURRENT_DATE - INTERVAL '30 days'
    RETURNING id, customer_id, amount
)
INSERT INTO archived_orders(id, customer_id, amount)
SELECT id, customer_id, amount
FROM moved;

这里 DELETE ... RETURNING 产生的行直接成为 INSERT 的输入。

这不是所有数据库的通用能力。MySQL 8.4 支持在 SELECT、UPDATE、DELETE 等语句前使用 CTE，但不应把 PostgreSQL 的“CTE 内部执行一个带 RETURNING 的修改并把结果传给另一条修改语句”语义直接移植到 MySQL。跨引擎 SQL 应分别验证语法、执行顺序和原子性。

---

七、递归 CTE：锚点、递归项与固定点

递归 CTE（recursive CTE）由两部分组成：

1. 锚点项（anchor member）：初始行；
2. 递归项（recursive member）：根据上一轮结果产生下一轮行。

两部分通常用 UNION ALL 或 UNION 连接。

1. 遍历组织树

准备数据：

CREATE TABLE org (
    id       INTEGER PRIMARY KEY,
    name     VARCHAR(100) NOT NULL,
    parent_id INTEGER NULL
);

INSERT INTO org(id, name, parent_id) VALUES
(1, '总部', NULL),
(2, '研发部', 1),
(3, '平台组', 2),
(4, '销售部', 1);

查询总部及其所有后代：

WITH RECURSIVE org_tree AS (
    SELECT
        id,
        name,
        parent_id,
        0 AS depth,
        CAST(id AS CHAR(200)) AS path
    FROM org
    WHERE id = 1

    UNION ALL

    SELECT
        child.id,
        child.name,
        child.parent_id,
        parent.depth + 1,
        CONCAT(parent.path, '/', child.id)
    FROM org AS child
    JOIN org_tree AS parent
      ON child.parent_id = parent.id
)
SELECT id, name, parent_id, depth, path
FROM org_tree
ORDER BY path;

在 PostgreSQL 中，CAST(id AS CHAR(200)) 可改为合适的文本类型；上面的 CONCAT 写法兼容 MySQL，而 PostgreSQL 可写为：

(parent.path || '/' || child.id::text)

预期逻辑结果为：

id	name	depth	path
1	总部	0	1
2	研发部	1	1/2
3	平台组	2	1/2/3
4	销售部	1	1/4

递归过程可以明确写成：

第 0 轮：锚点得到 {总部}
第 1 轮：由总部找到 {研发部, 销售部}
第 2 轮：由研发部、销售部找到 {平台组}
第 3 轮：找不到新子节点，停止

数据库不会凭空知道“树一定无环”。停止条件来自递归项不再产生行，或者由 UNION 的去重机制抑制重复行。如果数据存在环：

A.parent_id = B
B.parent_id = A

配合 UNION ALL 可能无限产生结果，最终表现为查询持续运行、资源消耗或达到递归深度限制。

2. 用路径防止访问同一节点

可以维护访问路径，并在递归项中排除已访问节点。以下是 PostgreSQL 风格：

WITH RECURSIVE graph AS (
    SELECT
        id,
        parent_id,
        ARRAY[id] AS visited
    FROM org
    WHERE id = 1

    UNION ALL

    SELECT
        child.id,
        child.parent_id,
        g.visited || child.id
    FROM org AS child
    JOIN graph AS g
      ON child.parent_id = g.id
    WHERE NOT child.id = ANY(g.visited)
)
SELECT *
FROM graph;

这里：

• visited 是当前路径已经访问的节点数组；
• g.visited || child.id 把新节点加入路径；
• child.id = ANY(g.visited) 检查节点是否已经出现；
• NOT 防止沿环继续递归。

MySQL 没有 PostgreSQL 的数组类型，可以使用字符串路径，但必须避免节点编号的前缀误判。例如不能简单地判断 1 是否出现在字符串 11/12 中。可以使用分隔符：

WITH RECURSIVE graph AS (
    SELECT
        id,
        parent_id,
        CAST(CONCAT('/', id, '/') AS CHAR(1000)) AS visited
    FROM org
    WHERE id = 1

    UNION ALL

    SELECT
        child.id,
        child.parent_id,
        CONCAT(g.visited, child.id, '/')
    FROM org AS child
    JOIN graph AS g
      ON child.parent_id = g.id
    WHERE INSTR(g.visited, CONCAT('/', child.id, '/')) = 0
)
SELECT *
FROM graph;

这类字符串路径有长度限制和编码成本；节点标识复杂时，应选择引擎适合的路径类型或在应用层维护访问状态。

3. UNION 与 UNION ALL 的递归差异

WITH RECURSIVE reachable AS (
    SELECT id, parent_id
    FROM org
    WHERE id = 1

    UNION

    SELECT child.id, child.parent_id
    FROM org AS child
    JOIN reachable AS r
      ON child.parent_id = r.id
)
SELECT *
FROM reachable;

UNION 会去重。对某些无额外路径信息的可达性查询，它可以帮助在有限图上停止重复扩展。

但若递归结果包含深度、路径或时间等每轮都会变化的列，即使节点相同，整行也可能不同，UNION 仍然无法消除这些行：

(id=2, depth=1)
(id=2, depth=3)

因此，UNION 不是普遍的环检测方案。真正需要的是定义“访问过”的状态，通常是节点集合或路径集合。

4. 递归深度和资源边界

递归 CTE 没有自动的业务深度上限。生产查询通常应加入限制：

...
WHERE parent.depth < 100

这表示最多继续展开到深度 100，但它解决的是资源边界，不等于解决了环检测。若树深度超过 100，查询会静默截断结果；若存在环但在 100 层内，仍可能重复访问。

PostgreSQL 和 MySQL 对递归 CTE 的可用语法、递归深度配置和递归项限制存在差异。尤其是 MySQL 对递归成员中的聚合、窗口函数、排序、限制等结构有更严格限制；不能把 PostgreSQL 递归查询直接认为在 MySQL 8.4 中也合法。

---

八、递归 CTE 的列类型必须稳定

递归 CTE 的锚点项决定了递归列的初始类型，递归项必须能兼容该类型。

MySQL 中，下面的写法可能因锚点字符串长度不足而在递归时发生截断或报错：

WITH RECURSIVE paths AS (
    SELECT CAST('1' AS CHAR(1)) AS path

    UNION ALL

    SELECT CONCAT(path, '/2')
    FROM paths
)
SELECT *
FROM paths;

锚点的 path 只有长度 1，后续拼接会超出类型容量。应预留明确长度：

WITH RECURSIVE paths AS (
    SELECT CAST('1' AS CHAR(100)) AS path

    UNION ALL

    SELECT CONCAT(path, '/2')
    FROM paths
    WHERE CHAR_LENGTH(path) < 95
)
SELECT *
FROM paths;

PostgreSQL 也要求递归两侧类型可匹配；文本、数组、数值等类型应显式转换，尤其不要依赖复杂表达式的隐式推断。

---

九、物化：中间结果是否真正存储

物化（materialization）指把查询结果计算出来并保存为一个中间关系，后续读取这个结果，而不是每次都把定义重新展开到更大的查询中。

需要区分三件事：

1. 逻辑名称：CTE 是否可以被引用；
2. 物理存储：中间结果是否实际落地；
3. 优化边界：过滤、连接、索引条件能否穿透 CTE 定义。

CTE 的写法本身只保证第一项，不自动保证后两项。

1. PostgreSQL 的 CTE 物化控制

PostgreSQL 支持：

WITH x AS MATERIALIZED (
    SELECT *
    FROM orders
    WHERE status = 'paid'
)
SELECT *
FROM x
WHERE customer_id = 10;

MATERIALIZED 明确要求把 CTE 作为独立计算结果使用。它可能适合：

• CTE 被多次引用；
• CTE 内部计算昂贵，且希望共享结果；
• 需要明确建立一个优化边界；
• 希望避免同一表达式被重复执行。

也支持：

WITH x AS NOT MATERIALIZED (
    SELECT *
    FROM orders
    WHERE status = 'paid'
)
SELECT *
FROM x
WHERE customer_id = 10;

NOT MATERIALIZED 允许将 CTE 视为可内联的查询表达式。它可能使外层的 customer_id = 10 下推到基础表，从而减少扫描数据。

在 PostgreSQL 的常见规则中，非递归、无数据修改、无副作用的 CTE 如果只被引用一次，通常可以折叠到外层查询；被多次引用时，通常倾向于物化。MATERIALIZED 和 NOT MATERIALIZED 可显式影响这一决策，但它们不是无条件的性能保证。

例如：

WITH expensive AS MATERIALIZED (
    SELECT
        customer_id,
        SUM(amount) AS total_amount
    FROM orders
    GROUP BY customer_id
)
SELECT *
FROM expensive
WHERE customer_id = 10

UNION ALL

SELECT *
FROM expensive
WHERE customer_id = 20;

物化后，聚合结果可供两处引用。但如果最终只需要一个客户，内联可能允许基础表更早过滤：

WITH expensive AS NOT MATERIALIZED (
    SELECT customer_id, SUM(amount) AS total_amount
    FROM orders
    GROUP BY customer_id
)
SELECT *
FROM expensive
WHERE customer_id = 10;

到底哪种更快取决于基数、聚合代价、引用次数和执行计划。

2. PostgreSQL 中物化并不等于有索引

CTE 物化结果不是用户可直接管理的普通表。不能因为它被物化，就假设存在适合 customer_id 的索引。

如果需要：

• 多次跨语句复用；
• 明确建索引；
• 分阶段检查结果；
• 控制生命周期；

应考虑临时表：

CREATE TEMPORARY TABLE paid_orders_tmp AS
SELECT *
FROM orders
WHERE status = 'paid';

CREATE INDEX ON paid_orders_tmp(customer_id);

SELECT *
FROM paid_orders_tmp
WHERE customer_id = 10;

临时表是独立对象，有自己的统计信息、索引和事务/会话生命周期；CTE 只是单条语句中的查询构造。两者不能互换。

3. MySQL 8.4 的边界

MySQL 8.4 支持 CTE，但没有 PostgreSQL 同样的 MATERIALIZED / NOT MATERIALIZED CTE 语法。MySQL 优化器可能合并某些 CTE 或派生表，也可能将其物化；具体取决于查询结构和优化器规则。

因此，在 MySQL 中不能写：

WITH x AS MATERIALIZED (...)

并期待 PostgreSQL 语义。

需要强制分阶段时，应使用临时表；需要判断 MySQL 是否合并或物化，应使用：

EXPLAIN
WITH paid_orders AS (
    SELECT *
    FROM orders
    WHERE status = 'paid'
)
SELECT *
FROM paid_orders
WHERE customer_id = 10;

必要时使用 EXPLAIN FORMAT=JSON 查看更详细的计划信息。不要仅通过 SQL 文本判断 CTE 是否落地。

---

十、优化边界：哪些改写等价，哪些不等价

1. EXISTS 与 Join 不是无条件等价

如果只关心客户是否存在订单：

WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.id
)

可在语义上对应半连接。

但改成普通 Join：

JOIN orders o ON o.customer_id = c.id

会保留每个匹配组合，改变基数。除非再去重，否则结果不等价。

2. NOT IN 与 NOT EXISTS 不是无条件等价

当子查询列保证非空时，下面两者通常可以对应：

WHERE c.id NOT IN (
    SELECT o.customer_id
    FROM orders o
    WHERE o.customer_id IS NOT NULL
)

WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.id
)

没有非空保证时，NULL 使 NOT IN 具有三值逻辑差异，不能直接替换。

3. 标量子查询与 Join 需要保持“每个外层行最多一行”

这条语句要求每个客户只得到一个值：

SELECT c.id,
       (SELECT o.amount
        FROM orders o
        WHERE o.customer_id = c.id
        LIMIT 1)
FROM customers c;

直接改成：

SELECT c.id, o.amount
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id;

会把一个客户扩展为多行，改变结果。要保持等价，必须先定义选择规则，例如窗口函数排名、聚合或 LATERAL ... LIMIT 1。

4. CTE 不是天然优化屏障

同一逻辑查询的以下写法：

WITH filtered AS (
    SELECT *
    FROM orders
    WHERE status = 'paid'
)
SELECT *
FROM filtered
WHERE customer_id = 10;

和：

SELECT *
FROM orders
WHERE status = 'paid'
  AND customer_id = 10;

在许多情况下可得到相同计划，但不能把“许多情况”当成规范保证。递归 CTE、数据修改 CTE、显式物化以及优化器不支持的改写都会形成边界。

5. 谓词下推可能改变代价，但不能改变结果

谓词下推是把外层过滤条件提前放到子查询内部：

SELECT *
FROM (
    SELECT *
    FROM orders
    WHERE status = 'paid'
) AS x
WHERE customer_id = 10;

理论上可改写为：

SELECT *
FROM orders
WHERE status = 'paid'
  AND customer_id = 10;

对于普通确定性筛选，这通常保持结果等价，并可能减少读取行数。

但包含以下因素时，改写必须谨慎：

• LIMIT；
• DISTINCT；
• 窗口函数；
• 聚合；
• ROW_NUMBER 后再过滤；
• 非确定性函数；
• 外连接；
• NULL 语义。

例如：

SELECT *
FROM (
    SELECT o.*,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id
               ORDER BY created_at DESC
           ) AS rn
    FROM orders AS o
) AS x
WHERE x.rn = 1
  AND x.customer_id = 10;

这里可以讨论把客户过滤提前，因为分区只关心客户 10；但不能把 rn = 1 随意改成基础表的 LIMIT 1，那会从“每个客户一行”变成“整个结果一行”。

---

十一、用执行计划验证，而不是猜测

一个查询至少应从三个层次检查：

1. 结果语义

构造包含以下数据的测试集：

• 没有匹配行；
• 多个匹配行；
• NULL 外键；
• 同一排序键相同的行；
• 递归环；
• 深度超过限制的路径。

只测试“正常数据”无法发现 NOT IN、标量多行和递归环问题。

2. 估算计划

PostgreSQL：

EXPLAIN
WITH paid AS (
    SELECT *
    FROM orders
    WHERE status = 'paid'
)
SELECT *
FROM paid
WHERE customer_id = 10;

MySQL：

EXPLAIN
WITH paid AS (
    SELECT *
    FROM orders
    WHERE status = 'paid'
)
SELECT *
FROM paid
WHERE customer_id = 10;

重点观察：

• CTE 或派生表是否被展开；
• 是否出现物化节点；
• 过滤条件在哪一层应用；
• 是否使用了 orders(customer_id, status) 等相关索引；
• 估算行数与实际行数是否严重偏离；
• Join 算法和扫描方向是否合理。

3. 实际执行

在 PostgreSQL 中可使用：

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...

它会真正执行语句，并显示实际行数、耗时和缓冲区访问。对 INSERT、UPDATE、DELETE 必须谨慎，必要时先在事务中验证：

BEGIN;

EXPLAIN (ANALYZE, BUFFERS)
DELETE FROM orders
WHERE status = 'cancelled';

ROLLBACK;

但即使回滚，也可能产生锁竞争、触发器副作用、日志写入或缓存影响。不能把回滚当成没有风险的模拟环境。

MySQL 可使用 EXPLAIN ANALYZE 查看实际执行信息，但同样应注意语句是否真正修改数据，以及目标版本对该语法的支持范围。

---

十二、事务与故障边界

CTE 是一条语句内部的查询结构，不是独立事务步骤：

WITH x AS (...)
SELECT ...;

它不会让 x 先提交，再执行外层查询。

在 PostgreSQL 中，数据修改 CTE 与主语句属于同一条语句，失败时由事务规则决定回滚；不能把 CTE 当作可靠的跨语句消息队列或工作流步骤。

递归查询也不是逐轮提交。递归产生的中间行属于同一条语句执行过程：

开始语句
  -> 读取锚点
  -> 生成递归轮次
  -> 生成最终结果
  -> 语句成功或失败

如果查询因递归环、内存不足、超时或取消而失败，不能假设已经返回的部分结果会作为完整业务结果保留。客户端通常只能得到语句失败。

并发方面，CTE 使用的基础表数据受所在事务隔离级别和引擎读取规则影响。单条语句内的多个引用通常应被视为同一条语句的逻辑计算，但具体可见性、锁和一致性细节仍取决于 PostgreSQL 或 MySQL 的事务隔离级别、存储引擎及读类型。需要严格验证时，应在目标引擎、目标隔离级别上编写并发测试，而不是依赖另一种数据库的经验。

---

十三、常见失败表现与诊断方向

子查询返回多行

错误形式通常类似：

subquery returned more than one row

诊断方法：

SELECT customer_id, COUNT(*)
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1;

然后明确业务规则：

• 使用 MAX、MIN 等聚合；
• 使用 ORDER BY ... LIMIT 1；
• 使用窗口函数；
• 如果本应唯一，增加唯一约束，而不是在查询中随意截断。

NOT IN 查询结果为空

优先检查子查询列是否含 NULL：

SELECT COUNT(*)
FROM orders
WHERE customer_id IS NULL;

若存在，改用 NOT EXISTS 或显式过滤 NULL。

递归查询不停止

检查：

1. 递归连接条件是否会产生新行；
2. 数据是否存在环；
3. 是否使用 UNION ALL 重复扩展；
4. 是否缺少访问路径；
5. 是否缺少业务深度上限；
6. 递归列类型是否导致意外转换或截断。

CTE 改写后性能变差

不要只比较 SQL 文本长度。应比较：

• CTE 被引用次数；
• CTE 内部结果规模；
• 外层过滤能否下推；
• 物化结果是否被全部扫描；
• 是否需要重复计算；
• 是否可以对临时表建索引；
• 实际执行计划中的行数和内存使用。

PostgreSQL 中可试验 MATERIALIZED 与 NOT MATERIALIZED；MySQL 中应查看 EXPLAIN，必要时改用临时表或重新组织查询。

---

十四、如何选择表达方式

可以用以下语义问题作判断：

• 只判断是否有匹配行：优先考虑 EXISTS；
• 判断不匹配且子查询可能有 NULL：优先考虑 NOT EXISTS；
• 需要一个聚合值：使用标量聚合子查询或预聚合后连接；
• 需要从相关结果返回多列：考虑 LATERAL 或窗口函数；
• 需要把一个复杂逻辑步骤命名并复用：考虑 CTE；
• 需要跨语句复用、建索引或检查中间数据：使用临时表；
• 需要展开层级或图关系：使用递归 CTE，并显式设计停止条件、环检测和深度限制；
• 需要判断物化是否有益：通过目标数据库的执行计划和实际运行数据验证。

子查询和 CTE 首先是语义工具，其次才是性能工具。相关子查询不必然慢，CTE 不必然快，物化也不必然节省计算。真正的优化边界来自结果是否可等价改写、优化器是否支持该改写，以及中间结果的规模、复用次数和访问方式。

---

系列导航与关联阅读

• 系列入口：数据库完整学习路线：从关系模型、事务索引到分布式与向量检索
• 上一篇：SQL Join 完整指南：Inner、Outer、Semi、Anti、算法和陷阱
• 下一篇：SQL 窗口函数：分区、排序、Frame、排名、累计和间隔分析

官方资料

• PostgreSQL SQL Language
• MySQL Reference Manual

本文依据数据库官方文档重新梳理；正文、示例与生产检查清单由 WR BLOG 编写。