核心内容摘要
梦幻粉色光芒：苏州晶体2023的ISO级美学探索
在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的不同写法。
让我们一步步分析
TA LEFT JOIN LATERAL TB ON condTALEFTJOINLATERAL TBONcond这是标准的LATERAL LEFT JOIN对于TA的每一行TB根据cond进行关联如果TB中没有匹配的行仍然会返回TA的行TB的列为NULLcond可以包含TA和TB的列
TA LEFT JOIN LATERAL (TB WHERE cond) ON trueTALEFTJOINLATERAL(TBWHEREcond)ONtrue子查询(TB WHERE cond)先对TB进行过滤然后与TA进行LEFT JOIN条件是true总是成立效果与第一种写法等价因为如果TB中没有匹配cond的行子查询返回空集LEFT JOIN仍然返回TA的行TB的列为NULL不过语法上ON true可以省略为ON 11或直接使用CROSS JOIN形式
TA CROSS JOIN LATERAL (TB WHERE cond)TACROSSJOINLATERAL(TBWHEREcond)这不等价于前两种写法CROSS JOIN不会保留TA中没有匹配的行如果TB子查询返回空集该TA行不会出现在结果中相当于INNER JOIN的效果等价性
总结-- 这三种写法等价TALEFTJOINLATERAL TBONcond TALEFTJOINLATERAL(TBWHEREcond)ONtrueTALEFTJOINLATERAL(TBWHEREcond)ON11-- 但以下写法不等价可能丢失TA的行TACROSSJOINLATERAL(TBWHEREcond)实际示例-- 创建示例数据CREATETABLETA(idINT,val_aVARCHAR(
);CREATETABLETB(idINT,ta_idINT,val_bVARCHAR(
);INSERTINTOTAVALUES(1,A
,(2,A
;INSERTINTOTBVALUES(1,1,B
,(2,1,B
;-- 示例1: LEFT JOIN LATERAL - 保留所有TA行SELECT*FROMTALEFTJOINLATERAL(SELECT*FROMTBWHERETB.ta_idTA.id)AStONtrue;-- 返回2行TA.id1有2个匹配TA.id2没有匹配但有NULL-- 示例2: CROSS JOIN LATERAL - 可能丢失TA行SELECT*FROMTACROSSJOINLATERAL(SELECT*FROMTBWHERETB.ta_idTA.id)ASt;-- 只返回TA.id1的2行TA.id2的行被过滤掉了性能考虑虽然前两种写法逻辑上等价但性能可能有差异第一种写法优化器可能将ON cond条件下推到TB第二种写法WHERE条件在子查询内优化器处理方式可能不同建议在实际使用时查看执行计划选择性能更好的写法。
通常第一种写法更直观且被广泛支持。
在实际编程中我遇到如下写法在postgresql是允许的在duckdb不允许select * from range(
t(i)left JOIN LATERAL (select i1 j) on i1; 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 i1 then j end j from range(