Sample Header Ad - 728x90

ON predicate of Postgres LATERAL JOINs

1 vote
2 answers
377 views
How does ON predicate of Postgres LATERAL JOIN work? Let me clarify question a bit. I've read the official documentation and a bunch of articles about this kind of JOIN. As far as I understood it is a foreach loop with a correlated subquery inside - it iterates over all records of a table A, allowing to reference columns of a "current" row in a correlated subquery B and join a result set of the B to that "current" row of A - if the B query returns 1 row there is only one pair, and if the B query return N rows there are N pairs with duplicated "current" row of the A. The same behavior like in usual JOINs. But why is there a need in ON predicate? For me, in usual JOINs we use ON because we have a cartesian product of 2 tables to be filtered out, and it is not the case of LATERAL JOIN, which produces resulting pairs directly. In other words, in my developer experience I've only seen CROSS JOIN LATERAL and LEFT JOIN LATERAL () ON TRUE (the latter looks quite clumsy, though) but one day a colleague showed me
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)
and this exploded my mind. Why
(route_id)
? We already have
rts.route_id = r.route_id
inside the subquery!!! Maybe I understand the mechanics of LATERAL joins wrong?
Asked by Nikita Glukhov (169 rep)
Dec 10, 2023, 05:30 AM
Last activity: Dec 11, 2023, 01:06 PM