Postgres LATERAL JOIN
的ON
predicate 如何工作?
让我澄清一下问题,我已经阅读了官方文档和一些关于这种JOIN
的文章。
据我所知,它是一个foreach循环,里面有一个相关的子查询
- 它迭代表
A
的所有记录,从而允许引用相关子查询B
中的“当前”行的列并将B
的结果集连接到A
的“当前”行--如果B
查询返回1行,则只有一对,并且如果B查询返回N行,则存在N对与A的“当前”行重复。与通常的JOIN中的行为相同。
但是为什么需要ON
predicate 呢?
对我来说,在通常的JOIN中,我们使用ON
,因为我们需要过滤掉2个表的carnival乘积,而LATERAL JOIN
的情况并非如此,它直接产生结果对。
换句话说,在我的开发经验中,我只见过CROSS JOIN LATERAL
和LEFT JOIN LATERAL () ON TRUE
(尽管后者看起来很笨拙),但有一天,一位同事向我展示了
SELECT
r.acceptance_status, count(*) as count
FROM route r
LEFT JOIN LATERAL (
SELECT rts.route_id, array_agg(rts.shipment_id) shipment_ids
FROM route_to_shipment rts
where rts.route_id = r.route_id
GROUP BY rts.route_id
) rts using (route_id)
字符串
这让我大吃一惊。为什么是using (route_id)
?我们已经在子查询中有where rts.route_id = r.route_id
了!
也许我对LATERAL
连接的机制理解错了?
3条答案
按热度按时间plicqrtu1#
Postgres LATERAL JOIN的ON predicate 是如何工作的?
和常规的非
LATERAL
JOIN
一样,它独立于子查询中发生的事情。但为什么需要ON predicate ?
事实上,子查询包括什么 * 可以 * 以与连接条件相同的方式有效地使用,并没有改变
JOIN
外部的语法要求。为什么要使用(route_id)?我们已经在子查询中有了where rts.route_id = r.route_id!
无论你是在
where
的子查询内部,join
的外部,还是在where
的外部,或者这三个点的任何组合中应用条件,它都不会改变结果。在这种情况下,一个有用的区别是,join
允许PostgreSQL更好地推理查询并优化它:将using (route_id)
更改为on true
将删除该优点。看起来这并不重要,理想情况下,它不应该。不幸的是,Planner/Optimizer仍然必须以某种方式理解任何查询的声明性含义。显式的
join
条件清楚地传达了依赖性,并允许它检查连接表之间的关系,以应用适当的优化技术。lateral join (...)subquery on true
仅意味着要对子查询进行评估,每一行和on true
混淆了 * 它如何 * 依赖于那一行-这是作为横向子查询的内部逻辑而保持不变的,而lateral
仅仅是allows它使用外部引用,而没有与规划器进行更多的通信,所以它是left unoptimised。理想情况下,规划器应该查看内部并看到
where
,但它没有(至少在PostgreSQL 16.1中)。如果您将where
移出外部查询old-implicit-join-style,它就会doesn't这样做,尽管这确实有助于它在其他方面加快速度。当你使用run your friend's query时,它只会让
join
中的lateral
变得更明显,而且实际上不需要join
。你没有从子查询中请求任何东西,所以它只提供route
的匹配,这对于计算匹配行的组合可能很有用,但是因为你也在那里聚合,它最多只能提供一个匹配。最后,结果是它对查询没有任何贡献,可以有效地缩短为:字符串
但是,由于
left join
和left join
会忽略不匹配的route_to_shipment.route_id
,而left join
会获取不匹配的route.route_id
,无论它们是否存在于子查询中,因此可以完全删除:型
对于所有三种形式的查询,您都需要get the exact same plan,在所有情况下都非常简短和快速:
型
如果你使用
left lateral join...on true
,你就假装你想为每一行计算子查询,不管是什么,你混淆了依赖关系,所以规划器确实这样做了:型
Complete demo at db<>fiddle:字符串
flvlnr442#
这个问题的一个变体已经在https://dba.stackexchange.com/questions/301884/do-postgresql-lateral-joins-require-or-allow-an-on-clause上得到了回答。
简而言之,
ON
子句是CROSS JOIN
或NATURAL JOIN
之外的语法要求(后者是一个构思不良的想法,应该从SQL中删除)。对于LEFT JOIN LATERAL
,使用ON TRUE
而不是USING
,以避免对子查询的选择列表的不必要依赖。pxy2qtax3#
你对横向连接如何工作的理解基本上是正确的,你对强制性
on
子句的困惑也是正确的。一个例子是set returning functions:你不能在函数中放置
where
子句;如果你需要一个呢?字符串
https://dbfiddle.uk/Yr2Fn4gP的
另一个虚构的例子:从两个相关客户的最近10次购买中找到共同的物品:
型
第一个
on
子句用于满足语法要求,第二个子句使用来自两个横向连接的引用。也就是说,可以将上面的
on
子句编写为最外层的where
子句。使用on
和where
之间的选择取决于您正在使用的RDBMS(该子句是允许的,可选的或不允许的)和您的个人偏好。