DeepSeek总结的 LEFT JOIN LATERAL相关问题

在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

TA LEFT JOIN LATERAL TB ON cond

• 这是标准的LATERAL LEFT JOIN
• 对于TA的每一行，TB根据cond进行关联
• 如果TB中没有匹配的行，仍然会返回TA的行，TB的列为NULL
• cond可以包含TA和TB的列

2. TA LEFT JOIN LATERAL (TB WHERE cond) ON true

TA LEFT JOIN LATERAL (TB WHERE cond) ON true

• 子查询(TB WHERE cond)先对TB进行过滤
• 然后与TA进行LEFT JOIN，条件是true（总是成立）
• 效果与第一种写法等价，因为：
  • 如果TB中没有匹配cond的行，子查询返回空集
  • LEFT JOIN仍然返回TA的行，TB的列为NULL
• 不过语法上，ON true可以省略为ON 1=1或直接使用CROSS JOIN形式

3. TA CROSS JOIN LATERAL (TB WHERE cond)

TA CROSS JOIN LATERAL (TB WHERE cond)

• 这不等价于前两种写法
• CROSS JOIN不会保留TA中没有匹配的行
• 如果TB子查询返回空集，该TA行不会出现在结果中
• 相当于INNER JOIN的效果

等价性总结

— 这三种写法等价：
TA LEFT JOIN LATERAL TB ON cond
TA LEFT JOIN LATERAL (TB WHERE cond) ON true
TA LEFT JOIN LATERAL (TB WHERE cond) ON 1=1

— 但以下写法不等价（可能丢失TA的行）：
TA CROSS JOIN LATERAL (TB WHERE cond)

实际示例

— 创建示例数据
CREATE TABLE TA (id INT, val_a VARCHAR(10));
CREATE TABLE TB (id INT, ta_id INT, val_b VARCHAR(10));

INSERT INTO TA VALUES (1, 'A1'), (2, 'A2');
INSERT INTO TB VALUES (1, 1, 'B1'), (2, 1, 'B2');

— 示例1: LEFT JOIN LATERAL – 保留所有TA行
SELECT * FROM TA
LEFT JOIN LATERAL (
SELECT * FROM TB WHERE TB.ta_id = TA.id
) AS t ON true;
— 返回2行（TA.id=1有2个匹配，TA.id=2没有匹配但有NULL）

— 示例2: CROSS JOIN LATERAL – 可能丢失TA行
SELECT * FROM TA
CROSS JOIN LATERAL (
SELECT * FROM TB WHERE TB.ta_id = TA.id
) AS t;
— 只返回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 │
└───────┴───────┘