Como funciona o predicado ON do Postgres LATERAL JOIN?
Deixe-me esclarecer um pouco a questão. Eu li a documentação oficial e vários artigos sobre esse tipo de JOIN. Pelo que entendi é um loop foreach com uma subconsulta correlacionada dentro - ele itera sobre todos os registros de uma tabela A, permitindo referenciar colunas de uma linha "atual" em uma subconsulta correlacionada B e juntar um conjunto de resultados do B para aquela linha "atual" de A - se a consulta B retornar 1 linha, haverá apenas um par, e se a consulta B retornar N linhas, haverá N pares com a linha "atual" duplicada de A. O mesmo comportamento dos JOINs usuais .
Mas por que há necessidade do predicado ON? Para mim, em JOINs usuais usamos ON porque temos um produto cartesiano de 2 tabelas a ser filtrado, e não é o caso de LATERAL JOIN, que produz pares resultantes diretamente. Em outras palavras, na minha experiência de desenvolvedor eu só vi CROSS JOIN LATERAL e LEFT JOIN LATERAL () ON TRUE (este último parece bastante desajeitado), mas um dia um colega me mostrou
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)
e isso explodiu minha mente. Por que using (route_id)? Já temos where rts.route_id = r.route_iddentro da subconsulta!!! Talvez eu tenha entendido errado a mecânica das junções LATERAL?
