Postgres LATERAL JOIN的ON predicate 是如何工作的？
和常规的非LATERALJOIN一样，它独立于子查询中发生的事情。
但为什么需要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的匹配，这对于计算匹配行的组合可能很有用，但是因为你也在那里聚合，它最多只能提供一个匹配。最后，结果是它对查询没有任何贡献，可以有效地缩短为：

SELECT
r.acceptance_status, count(*) as count
FROM route r
LEFT JOIN LATERAL (
    SELECT distinct rts.route_id
    FROM route_to_shipment rts
) rts using (route_id)
group by 1

字符串
但是，由于left join和left join会忽略不匹配的route_to_shipment.route_id，而left join会获取不匹配的route.route_id，无论它们是否存在于子查询中，因此可以完全删除：

SELECT r.acceptance_status, count(*)
FROM route r group by 1;

型
对于所有三种形式的查询，您都需要get the exact same plan，在所有情况下都非常简短和快速：

QUERY PLAN
Sort  (cost=41.60..42.10 rows=200 width=40) (actual time=0.590..0.591 rows=5 loops=1)
  Output: r.acceptance_status, (count(*))
  Sort Key: r.acceptance_status, (count(*))
  Sort Method: quicksort  Memory: 25kB
  ->  HashAggregate  (cost=31.95..33.95 rows=200 width=40) (actual time=0.583..0.585 rows=5 loops=1)
        Output: r.acceptance_status, count(*)
        Group Key: r.acceptance_status
        Batches: 1  Memory Usage: 40kB
        ->  Seq Scan on public.route r  (cost=0.00..24.97 rows=1397 width=32) (actual time=0.007..0.158 rows=2000 loops=1)
              Output: r.route_id, r.acceptance_status
Planning Time: 0.056 ms
Execution Time: 0.608 ms

型
如果你使用left lateral join...on true，你就假装你想为每一行计算子查询，不管是什么，你混淆了依赖关系，所以规划器确实这样做了：

QUERY PLAN
Incremental Sort  (cost=475713.66..475735.06 rows=200 width=40) (actual time=3076.386..3076.388 rows=5 loops=1)
  Output: r.acceptance_status, (count(*))
  Sort Key: r.acceptance_status, (count(*))
  Presorted Key: r.acceptance_status
  Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 25kB  Peak Memory: 25kB
  ->  GroupAggregate  (cost=475713.59..475726.06 rows=200 width=40) (actual time=3076.153..3076.348 rows=5 loops=1)
        Output: r.acceptance_status, count(*)
        Group Key: r.acceptance_status
        ->  Sort  (cost=475713.59..475717.08 rows=1397 width=32) (actual time=3076.035..3076.135 rows=2000 loops=1)
              Output: r.acceptance_status
              Sort Key: r.acceptance_status
              Sort Method: quicksort  Memory: 86kB
              ->  Nested Loop Left Join  (cost=0.00..475640.60 rows=1397 width=32) (actual time=34.858..3074.645 rows=2000 loops=1)
                    Output: r.acceptance_status
                    ->  Seq Scan on public.route r  (cost=0.00..24.97 rows=1397 width=36) (actual time=0.013..0.387 rows=2000 loops=1)
                          Output: r.route_id, r.acceptance_status
                    ->  GroupAggregate  (cost=0.00..340.44 rows=1 width=36) (actual time=1.536..1.536 rows=1 loops=2000)
                          Output: rts.route_id, NULL::integer[]
                          ->  Seq Scan on public.route_to_shipment rts  (cost=0.00..340.43 rows=101 width=4) (actual time=0.166..1.532 rows=10 loops=2000)
                                Output: rts.shipment_id, rts.route_id
                                Filter: (rts.route_id = r.route_id)
                                Rows Removed by Filter: 19990
Planning Time: 0.134 ms
JIT:
  Functions: 11
  Options: Inlining false, Optimization false, Expressions true, Deforming true
  Timing: Generation 0.896 ms, Inlining 0.000 ms, Optimization 1.520 ms, Emission 31.319 ms, Total 33.735 ms
Execution Time: 3279.075 ms

型
Complete demo at db<>fiddle:字符串

这个问题的一个变体已经在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，以避免对子查询的选择列表的不必要依赖。

你对横向连接如何工作的理解基本上是正确的，你对强制性on子句的困惑也是正确的。
一个例子是set returning functions：你不能在函数中放置where子句;如果你需要一个呢？

select c.id, c.validfrom, c.validupto, c.cancelledon, years.year
from contracts as c
join lateral generate_series(c.validfrom, c.validupto) as years(year)
  on c.cancelledon is null or years.year < c.cancelledon

字符串
https://dbfiddle.uk/Yr2Fn4gP的
另一个虚构的例子：从两个相关客户的最近10次购买中找到共同的物品：

select referrals.person1, referrals.person2, p1.product as both_bought
from referrals
join lateral (
    select product
    from purchases
    where customer = referrals.person1
    order by date desc
    limit 10
) as p1 on true
join lateral (
    select product
    from purchases
    where customer = referrals.person2
    order by date desc
    limit 10
) as p2 on p1.product = p2.product

型
第一个on子句用于满足语法要求，第二个子句使用来自两个横向连接的引用。

也就是说，可以将上面的on子句编写为最外层的where子句。使用on和where之间的选择取决于您正在使用的RDBMS（该子句是允许的，可选的或不允许的）和您的个人偏好。