SQL LATERAL vs 子查询 vs JOIN LATERAL 的相关子查询优化路径

lateral用于让子查询逐行依赖外层表，解决普通子查询无法引用外层列的问题；不加lateral时子查询必须自包含，加lateral才允许o.id等动态引用，且适用于每行动态计算、结果行数不定等场景。

为什么普通子查询里引用外层表会报 Error: invalid reference to FROM-clause entry

因为标准 sql 中，FROM 子句里的子查询默认是“独立评估”的——它看不见前面的表，哪怕你写成 select * FROM orders, (SELECT * FROM items WHERE items.order_id = orders.id)，postgresql 也会直接拒绝。这不是语法疏忽，而是设计原则：子查询必须自包含、可单独执行。

• 只有加了 LATERAL，才明确告诉优化器：“这个子查询要逐行依赖左边的当前行”
• orders.id 这种写法一定报错；必须先给外层表起别名（如 o），再写 o.id
• 不加 LATERAL 却想引用外层列，等价于让数据库执行一个逻辑上不可能的任务

LATERAL (SELECT ...) 和 JOIN LATERAL (SELECT ...) ON TRUE 有啥实际区别

语义完全等价，但写法影响可读性、扩展性和维护成本。PostgreSQL 把 JOIN LATERAL 当作语法糖，底层都转成嵌套循环计划，但人看代码时感受不同。

• 用 , LATERAL (SELECT ...) 更适合简单推导，比如只取一行、只算一个值：SELECT o.id, i.name FROM orders o, LATERAL (SELECT name FROM items WHERE order_id = o.id LIMIT 1) i
• 用 LEFT JOIN LATERAL (SELECT ...) ON TRUE 更清晰表达“这是一次关联”，尤其当你后续可能加 WHERE 或改成 INNER JOIN LATERAL 时
• 千万别写 JOIN LATERAL (...) using (col) —— USING 会自动匹配列名，而 LATERAL 子查询通常不暴露同名字段，极易 silently 错配或报错

什么时候该死磕 LATERAL，而不是硬套 JOIN 或窗口函数

核心就一条：子查询逻辑是否需要“为每一行动态重算”，且结果结构不能被提前固定（比如返回行数不固定、字段由函数生成、依赖实时条件过滤）。

• 要查每个用户的最新订单？LATERAL (SELECT * FROM orders WHERE user_id = u.id ORDER BY created_at DESC LIMIT 1) —— JOIN 做不到 per-row 排序 + 截断
• 要展开 json 数组？LATERAL jsonb_array_elements(data->'tags') —— 普通 JOIN 没法把一个值变成多行
• 要调用返回记录集的函数？LATERAL pg_stat_file('base/12345') —— 函数返回多列，必须靠 LATERAL 拆解
• 反例：两张物理表固定关联，比如 users ↔ departments，用普通 INNER JOIN 性能更好 —— LATERAL 会锁死执行顺序，让优化器没法重排连接策略

性能坑和索引失效的典型场景

LATERAL 天然对应 Nested Loop 计划，这意味着它大概率会为外层每行都触发一次子查询执行。如果外层扫描行数多、子查询又没走索引，性能会断崖式下跌。

• 子查询中所有对外层列的引用（如 o.id）必须出现在 WHERE 条件的最左前缀位置，否则索引可能失效
• 避免在子查询里写 NOT EXISTS (SELECT ...) 套娃，尤其是里面还引用外层列 —— 这类嵌套会让 planner 难以估算代价，容易选错计划
• 用 EXPLAIN 看执行计划时，重点盯 Nested Loop 下面的 Index Scan 是否命中，以及 Rows Removed by Filter 是否高得离谱
• 如果发现子查询反复执行相同逻辑（比如多次 (SELECT count(*) FROM logs WHERE user_id = u.id)），应改用单次 LATERAL 计算后复用字段，而不是放多个子查询

LATERAL 不是银弹，它是把“相关子查询”从 SELECT 列表里解放出来、搬到 FROM 子句中的一种机制。真正难的从来不是怎么写，而是判断：这一行逻辑，到底该让数据库“一次性算完再连”，还是“边走边算”。