Lateral Join结合Right Join的执行顺序及结果异常原因问询

核心问题的根源 #

你的原始SQL存在连接优先级误解，导致执行顺序和预期不符，进而出现item_id=3、4的行未被过滤的情况。

1. 隐式Cross Lateral Join的优先级陷阱 #

你最初用逗号分隔表A和jsonb_array_elements(items)，这是隐式Cross Lateral Join，但PostgreSQL中JOIN关键字的优先级高于逗号，所以你写的SQL：

select A.id, item->>'id' as item_id, b.id 
from a, jsonb_array_elements(items) rx(item) 
right join b on b.id::varchar = item->>'id'

实际等价于：

select a.id, rx.item->>'id' as item_id, b.id
from a
cross join lateral (
  jsonb_array_elements(a.items) rx(item)
  right join b on b.id::varchar = rx.item->>'id'
) as combined

这个逻辑是：对表A的每一行，先拆分items得到rx行，再和表B做Right Join。Right Join会保留表B的所有行（id=1、2），同时保留rx中匹配B的行；但如果rx中有3、4这类不匹配B的行，本应被过滤——你看到这些行保留且b.id为null，说明你的实际需求是先做A和rx的Cross Lateral，再和B做Right Join，但逗号的优先级破坏了这个顺序。

2. Left Lateral Join解决问题的原因 #

当你把隐式Cross Lateral改成显式Left Lateral Join后：

select A.id, item->>'id' as item_id, b.id 
from a
left join lateral jsonb_array_elements(items) rx(item) on true
right join b on b.id::varchar = item->>'id'

此时left join lateral是一个完整的JOIN单元，优先级高于后续的right join，执行顺序变为：

1. 先执行表A与jsonb_array_elements(items)的Left Lateral Join：保留表A的所有行，拆分items得到包含1、2、3、4的行。
2. 再将上述结果与表B做Right Join：Right Join的逻辑是保留表B的所有行，仅匹配左表中item_id等于b.id的行，左表中item_id=3、4的行因无法匹配B，会被直接过滤，最终符合你的预期。

关键执行顺序总结 #

• 逗号分隔的隐式连接优先级低于显式JOIN，会导致Lateral Join与Right Join的执行顺序颠倒。
• 显式Left Lateral Join会先与表A完成连接，再执行与表B的Right Join，实现“只保留item_id在B中的行”的需求。

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