当相关子查询在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 行为不同，调试时得留心。
