PostgreSQL 如何用 LATERAL JOIN 替换相关子查询

当相关子查询在select或WHERE中反复调用且依赖外层字段导致性能下降时，应改用LATERAL JOIN；它支持物化、索引下推、结果复用，并能稳定实现每行Top-N，但需注意索引和NULL值处理。

什么时候该用 LATERAL JOIN 替换相关子查询

当你发现相关子查询（correlated subquery）在 SELECT 列表里反复出现、或在 WHERE 中被多次调用，且每次都要依赖外层行的字段时，性能通常会明显下降——postgresql 会对每行外层数据重新执行一次子查询。这时 LATERAL JOIN 就是更可控的替代方案：它把子查询变成可复用的“行级关联表”，支持物化、索引下推，还能在后续 JOIN 或 WHERE 中复用计算结果。

LATERAL 子查询必须写成 JOIN 形式才能引用外层列

直接写 (SELECT ... FROM ...) AS t 是不行的——PostgreSQL 会报错 Error: invalid reference to FROM-clause entry for table "xxx"，因为普通子查询无法访问外层作用域。必须显式加上 LATERAL 关键字，并用 JOIN 语法连接：

SELECT u.name, orders.total FROM users u JOIN LATERAL (   SELECT COALESCE(SUM(amount), 0) AS total   FROM orders   WHERE orders.user_id = u.id     AND orders.created_at >= CURRENT_DATE - INTERVAL '7 days' ) orders ON true;

• LATERAL 告诉优化器：这个子查询可以引用左侧（u）的列
• ON true 是必需的连接条件（不能省略），表示无条件关联；也可用 ON 1=1，但语义上 true 更清晰
• 子查询别名 orders 可以像普通表一样在 SELECT 和后续 WHERE 中使用

用 LATERAL 实现“为每行取 Top-N”比子查询更可靠

相关子查询写 ORDER BY ... LIMIT 1 在某些版本 PostgreSQL 中可能因优化器误判导致结果不一致（尤其配合 GROUP BY 或窗口函数时）。LATERAL 能明确绑定执行顺序：

SELECT u.name, latest_order.amount FROM users u JOIN LATERAL (   SELECT amount   FROM orders   WHERE orders.user_id = u.id   ORDER BY created_at DESC   LIMIT 1 ) latest_order ON true;

• 子查询中 ORDER BY + LIMIT 会被真正按每行 u.id 执行，结果稳定
• 如果想取 Top-3，只需改 LIMIT 3，并配合 ARRAY_AGG 或额外 JOIN 展开
• 注意：若外层行多、子查询又没走索引，LATERAL 仍可能慢——确保 orders(user_id, created_at) 有复合索引

和普通子查询相比，LATERAL 的关键差异在执行时机与可见性

相关子查询本质是“表达式”，只能出现在 SELECT 或 WHERE；而 LATERAL JOIN 是真正的“表源”，能参与整个查询计划：

• 可在 FROM 阶段多次引用同一个 LATERAL 结果（比如先过滤再聚合）
• 支持在 WHERE 中用其字段做条件，而相关子查询嵌套太深时优化器常放弃下推
• LATERAL 子查询里的 GROUP BY、HAVING、甚至另一个 LATERAL 都合法；相关子查询则不允许
• 错误示例：SELECT (SELECT * FROM generate_series(1, u.id)) FROM users u 会报错；必须写成 JOIN LATERAL generate_series(1, u.id) g(i) ON true

真正容易被忽略的是：LATERAL 子查询的 WHERE 条件里如果用了外层 NULL 值，结果可能为空——不是报错，而是静默跳过该行，这点和 LEFT JOIN 行为不同，调试时得留心。