在 postgresql 15 中,使用 lateral 连接优化子查询的核心做法是将原本无法关联外部行的相关子查询改写为 lateral join,使数据库引擎能够利用外层表的每一行数据作为输入来执行内层查询。这种写法通常能将嵌套循环(nested loop)的执行计划转化为更高效的 hash join 或 merge join,特别是在内层查询涉及聚合、排序或复杂过滤时。通过显式声明 lateral,postgresql 可以明确知道内层查询依赖外层上下文,从而避免重复扫描或低效的逐行计算。

LATERAL 与 IN 子句的性能差异
当子查询引用了外层表的列时,IN 子句往往会导致每行都重新执行一次子查询,而 LATERAL JOIN 允许将这种依赖关系显式化,便于优化器选择更好的连接算法。例如,查找每个用户最近的一条订单,使用 IN 子句可能无法有效利用索引,而 LATERAL JOIN 可以直接结合 LIMIT 和 ORDER BY 快速定位。

CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT);
CREATE TABLE orders (id SERIAL PRIMARY KEY, user_id INT, amount NUMERIC, created_at TIMESTAMP);
-- 低效写法:相关子查询
SELECT u.name,
(SELECT o.amount FROM orders o WHERE o.user_id = u.id ORDER BY o.created_at DESC LIMIT 1) as last_order_amount
FROM users u;
-- 高效写法:LATERAL JOIN
SELECT u.name, o.amount
FROM users u
LEFT JOIN LATERAL (
SELECT o.amount
FROM orders o
WHERE o.user_id = u.id
ORDER BY o.created_at DESC
LIMIT 1
) o ON true;
LATERAL 与 CROSS APPLY 的等价性
在 PostgreSQL 中,LATERAL 关键字既可用于 INNER JOIN 也可用于 LEFT JOIN,这与 SQL Server 中的 CROSS APPLY 和 OUTER APPLY 概念完全对应。理解这一点有助于从其他数据库迁移时的语法转换。LATERAL 本质上是一个表值函数调用机制,它允许右侧的表表达式访问左侧表的列。

-- 模拟 CROSS APPLY (INNER JOIN LATERAL)
SELECT u.name, o.amount
FROM users u
JOIN LATERAL (
SELECT o.amount
FROM orders o
WHERE o.user_id = u.id
LIMIT 1
) o ON true;
-- 模拟 OUTER APPLY (LEFT JOIN LATERAL)
SELECT u.name, o.amount
FROM users u
LEFT JOIN LATERAL (
SELECT o.amount
FROM orders o
WHERE o.user_id = u.id
LIMIT 1
) o ON true;
处理无匹配行的空值情况
当使用 LATERAL JOIN 时,如果内层查询没有返回任何结果,JOIN 的行为取决于使用的是 INNER JOIN 还是 LEFT JOIN。对于 INNER JOIN LATERAL,外层行会被过滤掉;而对于 LEFT JOIN LATERAL,外层行会保留,内层字段显示为 NULL。这在处理“每个用户的最后一条订单”且某些用户可能没有订单的场景下至关重要。

















