Postgres LATERAL JOIN的ON谓词工作机制及冗余关联疑问

你的基础理解是准确的：LATERAL JOIN的核心逻辑确实类似带关联的foreach循环——遍历左表（表A）的每一条记录，用当前行的字段去执行右侧的子查询/表函数，再将左表行与子查询的结果进行关联配对，这一过程本身就不是笛卡尔积，而是基于左行的定向关联。

下面针对你的疑问逐一解释：

为什么LATERAL JOIN需要ON谓词？ #

虽然LATERAL已经通过关联子查询绑定了左行与子查询结果，但ON/USING依然有其存在的意义：

• 额外过滤逻辑：即使子查询已经通过WHERE完成了基础关联，ON可以对配对结果做进一步筛选。比如子查询返回了所有与左行route_id匹配的记录，但你只想保留其中status = 'active'的条目，这时候把条件写在ON里（而非子查询WHERE），在LEFT JOIN场景下会保留左表行，只是不满足条件的子查询结果为NULL；如果写在子查询WHERE里，子查询会直接返回空集，最终左表行对应的子查询字段全为NULL，两者效果类似但逻辑侧重点不同。
• 语法强制要求：PostgreSQL中，除了CROSS JOIN LATERAL可以省略关联条件外，LEFT/RIGHT/FULL JOIN LATERAL必须指定ON或USING子句——哪怕是ON TRUE，这是JOIN语法的统一规则，和LATERAL本身的逻辑无关。
• 字段合并与多条件关联：使用USING可以自动合并左右表中同名的关联字段（比如route_id），避免结果集中出现重复的r.route_id和rts.route_id列；如果需要基于多个字段做关联（比如route_id+date），ON可以清晰写出所有匹配条件，逻辑更直观。

关于同事SQL的冗余写法 #

你看到的「子查询内已用WHERE rts.route_id = r.route_id关联，却还加USING(route_id)」的写法，属于合法但冗余的操作：

• 子查询的WHERE已经确保了rts.route_id与r.route_id相等，USING只是重复了这个匹配条件，但会额外合并同名的route_id字段，让结果集更简洁。
• 有人这么写可能是出于习惯（比如普通JOIN中常用USING），或者是为了避免结果集出现重复字段——如果不写USING，查询结果会同时包含r.route_id和rts.route_id两个列，而USING会将它们合并为一个route_id列。
• 从执行效率上看，PostgreSQL的查询优化器会识别这种冗余条件，不会重复执行过滤，所以不会有性能损耗。

内容的提问来源于stack exchange，提问作者Nikita Glukhov