LATERAL不是普通JOIN，而是必须显式声明的行级绑定机制；它允许右侧子查询引用左侧当前行字段，实现逐行执行，否则会报“invalid reference”错误。
为什么LATERAL不是普通JOIN，而必须显式声明
PostgreSQL 中 LATERAL 的核心作用是允许右侧子查询“看到”左侧表的当前行值——普通 JOIN 或 LEFT JOIN 无法做到这点。如果你写了 FROM orders JOIN (SELECT ... FROM items WHERE items.order_id = orders.id) AS sub ON true，会报错 ERROR: invalid reference to FROM-clause entry for table "orders"，因为子查询在逻辑上先于外部表执行。
必须用 LATERAL 显式标记，告诉优化器：“这个子查询依赖左边每一行，要逐行执行”。它不是语法糖，而是执行模型的根本切换。
- 没加
LATERAL→ 子查询被当作一次性独立查询，不能引用外部列 - 加了
LATERAL→ 子查询变成“函数式调用”，每次传入当前行的字段值 -
LATERAL可用于JOIN、LEFT JOIN，也可跟在FROM后直接使用（如FROM orders, LATERAL (SELECT ...)）
用LATERAL计算每个订单的最新3条物流轨迹
典型场景：主表是 orders，关联表 shipments 有时间戳和状态，你想为每个订单动态取最近 3 条轨迹，而不是全量 JOIN 后再 ROW_NUMBER() 窗口过滤（那会先膨胀再收缩，性能差）。
SELECT o.id, o.total,
s.status, s.updated_at, s.tracking_number
FROM orders o
LEFT JOIN LATERAL (
SELECT status, updated_at, tracking_number
FROM shipments s2
WHERE s2.order_id = o.id
ORDER BY s2.updated_at DESC
LIMIT 3
) s ON true;
注意点：
-
LIMIT在子查询内生效，且能利用WHERE s2.order_id = o.id上的索引（比如INDEX ON shipments(order_id, updated_at DESC)） - 用
LEFT JOIN LATERAL保证订单不丢失；若用JOIN LATERAL，没有物流记录的订单会被过滤掉 - 别写成
JOIN LATERAL (...) AS s再加ON s.order_id = o.id—— 这是冗余且错误的，LATERAL子查询里已通过WHERE关联，ON true即可
LATERAL + JSONB/数组函数做实时聚合计算
当需要为每行生成动态结构化结果（比如把关联标签拼成 JSON 对象、或按条件算分），LATERAL 配合 jsonb_object_agg() 或 array_agg() 很自然。
例如：每个用户有多个 user_preferences 记录，想转成单个 jsonb 字段，键为 key 列，值为 value 列：
SELECT u.id, u.email,
p.prefs
FROM users u
LEFT JOIN LATERAL (
SELECT jsonb_object_agg(up.key, up.value) AS prefs
FROM user_preferences up
WHERE up.user_id = u.id
) p ON true;
关键细节：
- 若某用户无偏好，
p.prefs为NULL（因LEFT JOIN），不是空 JSON 对象；需要默认值可用COALESCE(p.prefs, '{}'::jsonb) -
jsonb_object_agg()遇到重复key会报错，加DISTINCT ON (up.key)或先去重子查询 - 聚合函数在
LATERAL内执行，天然按外层行分组，无需写GROUP BY u.id
容易忽略的性能陷阱：LATERAL 不自动走索引？
LATERAL 本身不保证性能，它只是让语法合法。实际执行效率高度依赖子查询里的谓词是否能命中索引。
- 写
WHERE s2.order_id = o.id AND s2.status = 'shipped'，但只有(order_id)索引？那status过滤就得全表扫描该 order 的所有 shipment —— 应建联合索引ON shipments(order_id, status, updated_at) - 子查询里用了函数如
lower(s2.note)做过滤？除非你建了函数索引，否则索引失效 - PostgreSQL 16 对
LATERAL子查询的物化策略更激进，有时会缓存结果复用；但若子查询含random()或clock_timestamp()等易变函数，优化器会强制禁用物化，导致重复执行 —— 查EXPLAIN ANALYZE看是否出现Materialize节点及 “never executed” 提示
最稳妥的做法：对 LATERAL 子查询单独 EXPLAIN，确认它在单行输入下能走索引、响应快；再嵌入主查询看整体计划。
