在SQL中TA left JOIN LATERAL TB on cond 和TA left JOIN LATERAL (TB where cond) on true是否等价？与TA cross JOIN LATERAL (TB where cond) 呢？
这是一个很好的SQL问题，涉及到LATERAL JOIN的不同写法。让我们一步步分析：
1.TA LEFT JOIN LATERAL TB ON cond
TALEFTJOINLATERAL TBONcond- 这是标准的LATERAL LEFT JOIN
- 对于TA的每一行，TB根据
cond进行关联 - 如果TB中没有匹配的行，仍然会返回TA的行，TB的列为NULL
cond可以包含TA和TB的列
2.TA LEFT JOIN LATERAL (TB WHERE cond) ON true
TALEFTJOINLATERAL(TBWHEREcond)ONtrue- 子查询
(TB WHERE cond)先对TB进行过滤 - 然后与TA进行LEFT JOIN，条件是
true（总是成立） - 效果与第一种写法等价，因为：
- 如果TB中没有匹配
cond的行，子查询返回空集 - LEFT JOIN仍然返回TA的行，TB的列为NULL
- 如果TB中没有匹配
- 不过语法上，
ON true可以省略为ON 1=1或直接使用CROSS JOIN形式
3.TA CROSS JOIN LATERAL (TB WHERE cond)
TACROSSJOINLATERAL(TBWHEREcond)- 这不等价于前两种写法
- CROSS JOIN不会保留TA中没有匹配的行
- 如果TB子查询返回空集，该TA行不会出现在结果中
- 相当于INNER JOIN的效果
等价性总结
-- 这三种写法等价：TALEFTJOINLATERAL TBONcond TALEFTJOINLATERAL(TBWHEREcond)ONtrueTALEFTJOINLATERAL(TBWHEREcond)ON1=1-- 但以下写法不等价（可能丢失TA的行）：TACROSSJOINLATERAL(TBWHEREcond)实际示例
-- 创建示例数据CREATETABLETA(idINT,val_aVARCHAR(10));CREATETABLETB(idINT,ta_idINT,val_bVARCHAR(10));INSERTINTOTAVALUES(1,'A1'),(2,'A2');INSERTINTOTBVALUES(1,1,'B1'),(2,1,'B2');-- 示例1: LEFT JOIN LATERAL - 保留所有TA行SELECT*FROMTALEFTJOINLATERAL(SELECT*FROMTBWHERETB.ta_id=TA.id)AStONtrue;-- 返回2行（TA.id=1有2个匹配，TA.id=2没有匹配但有NULL）-- 示例2: CROSS JOIN LATERAL - 可能丢失TA行SELECT*FROMTACROSSJOINLATERAL(SELECT*FROMTBWHERETB.ta_id=TA.id)ASt;-- 只返回TA.id=1的2行，TA.id=2的行被过滤掉了性能考虑
虽然前两种写法逻辑上等价，但性能可能有差异：
- 第一种写法：优化器可能将
ON cond条件下推到TB - 第二种写法：WHERE条件在子查询内，优化器处理方式可能不同
建议在实际使用时查看执行计划，选择性能更好的写法。通常第一种写法更直观且被广泛支持。
在实际编程中，我遇到如下写法，在postgresql是允许的，在duckdb不允许，
select * from range(3)t(i)left JOIN LATERAL (select i+1 j) on i>1; Binder Error: Join condition for non-inner LATERAL JOIN must be a comparison between the left and right side所以改写成下面几种写法，上面的理论解释了其中cross JOIN LATERAL(cond)写法不等价的原因。
select i,case when i>1 then j end j from range(3)t(i)left JOIN LATERAL (select i+1 j) on true; ┌───────┬───────┐ │ i │ j │ │ int64 │ int64 │ ├───────┼───────┤ │ 0 │ NULL │ │ 1 │ NULL │ │ 2 │ 3 │ └───────┴───────┘ select i,case when i>1 then j end j from range(3)t(i)cross JOIN LATERAL (select i+1 j) ; ┌───────┬───────┐ │ i │ j │ │ int64 │ int64 │ ├───────┼───────┤ │ 0 │ NULL │ │ 1 │ NULL │ │ 2 │ 3 │ └───────┴───────┘ memory D select i,j from range(3)t(i)cross JOIN LATERAL (select i+1 j where i>1) ; ┌───────┬───────┐ │ i │ j │ │ int64 │ int64 │ ├───────┼───────┤ │ 2 │ 3 │ └───────┴───────┘ memory D select i,j from range(3)t(i)left JOIN LATERAL (select i+1 j where i>1) on true; ┌───────┬───────┐ │ i │ j │ │ int64 │ int64 │ ├───────┼───────┤ │ 2 │ 3 │ │ 0 │ NULL │ │ 1 │ NULL │ └───────┴───────┘
