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
Last activity: Dec 11, 2023, 01:06 PM