简记左、右、结果表的记录数分别为 LR(left rows), RR, JR
1 left join
左关联,右表根据关联列,匹配相应的记录,无法匹配时填充NULL,当右表的关联列存在重复值时,JR > LR.
2 left semi join
左半连接,当左表关联右表时,对于每个关联列的值,匹配右表的第一条记录即返回,相比于left join,在右表关联列大量重复时性能提升比较明显,JR = LR.
3.asof join
前2个连接都是等值连接,而aj可以做到非等值连接,通常用于时序场景。比如左表找右表时间最邻近的一条记录。JR = LR
n=4
id=stretch(`a`b`c, n)
id2=stretch(`a`c`d, n)
val1=7 7 8 9
val2=7 6 7 9
t1=table(id, val)
t2=table(id2, val2)
select * from t1 left join t2 on t1.id = t2.id2
select * from t1 left semi join t2 on t1.id = t2.id2
select * from aj(t1, t2, `id`val, `id2`val2)
lj, lsj, aj的结果分别是:
LJ
id val val2
-- --- ----
a 7 7
a 7 6
a 7 7
a 7 6
b 8
c 9 7
LSJ
id val val2
-- --- ----
a 7 7
a 7 7
b 8
c 9 7
AJ
id val val2
-- --- ----
a 7 6
a 7 6
b 8
c 9 7