Início / dba / Perguntas / 333909
Accepted
Nikita Glukhov
Asked: 2023-12-10 13:30:58 +0800 CST

Predicado ON de LATERAL JOINs do Postgres

  • 772

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?

postgresql

2 respostas

  1. CREATE TABLE ta (aid INT, a INT);
CREATE TABLE tb (aid INT, b INT);
INSERT INTO ta VALUES (1,10),(2,20);
INSERT INTO tb VALUES (1,100),(1,200);
SELECT * FROM ta LEFT JOIN LATERAL (SELECT * FROM tb WHERE tb.aid=ta.aid) ON true;

 aid | a  | aid  |  b
-----+----+------+------
   1 | 10 |    1 |  100
   1 | 10 |    1 |  200
   2 | 20 | Null | Null

SELECT * FROM ta LEFT JOIN LATERAL (SELECT * FROM tb) USING (aid);

 aid | a  |  b
-----+----+------
   1 | 10 |  100
   1 | 10 |  200
   2 | 20 | Null

    A cláusula USING (colunas) não duplica as colunas especificadas no conjunto de resultados, enquanto ON (ta.column=tb.column) duplica as colunas. Aqui a coluna duplicada é “ajuda”. No caso de um JOIN padrão sobre igualdade, as colunas serão iguais, portanto a duplicação é inútil, o que significa que USING é preferível. Também é mais legível. No caso de um JOIN externo (direita, esquerda, completo), você pode querer que as duas colunas sejam duplicadas para saber se uma delas é NULL.

    Se você deseja um CROSS JOIN (sem condição ON):

    SELECT * FROM ta CROSS JOIN LATERAL (SELECT * FROM tb WHERE tb.aid=ta.aid);

    Você também pode usar um JOIN e mover algumas das condições que estariam no WHERE da tabela LATERAL para a cláusula ON(), o resultado é o mesmo:

    SELECT * FROM ta JOIN LATERAL (SELECT * FROM tb WHERE ...) ON (tb.aid=ta.aid);

    Mas não há CROSS LEFT JOIN, portanto, se você quiser um LEFT JOIN LATERAL, deverá declarar explicitamente LEFT JOIN, e isso requer a cláusula ON.

    SELECT * FROM ta JOIN LATERAL (SELECT * FROM tb WHERE tb.aid=ta.aid) ON true WHERE ta.aid<10;

    Na verdade, no caso de uma junção LATERAL, a cláusula ON pode ser supérflua.

    • 1
  2. Best Answer

    Resposta curta: LEFT JOIN requer uma condição de junção - em oposição a CROSS JOIN. Noções básicas no manual.
    Veja também:

    Mas a condição de junção ainda pode fazer sentido para filtrar quais linhas anexar no lado direito depois de calcular um conjunto na subqery lateral. Como:

    SELECT r.acceptance_status
     , count(*) AS count_routes
     , count(rts.shipment_ids) AS count_routes_with_more_than_one_shipment
FROM   route r
LEFT   JOIN LATERAL (
   SELECT array_agg(rts.shipment_id) shipment_ids
        , count(*) AS shipments
   FROM   route_to_shipment rts
   WHERE  rts.route_id = r.route_id
   -- GROUP  BY rts.route_id  -- just noise
   ) rts ON shipments > 1;  -- !!!

    Isso retorna todas as linhas de table route, mas anexa apenas shipment_idsonde mais de uma linha relacionada na tabela route_to_shipmentfor encontrada.

    Não há necessidade de adicionar itens rts.route_idà SELECTlista da subconsulta.
    GROUP BY rts.route_idé apenas barulho depois WHERE rts.route_id = r.route_id.
    E ainda estou gerando o array shipment_idsem vão, como o original.

    Demonstrando também resultados diferentes para count(*)vs.count(shipment_ids)

    A condição de junção não pode passar para a WHEREcláusula, onde teria um efeito diferente. Você pode adicionar uma HAVINGcláusula à suquery:

    SELECT r.acceptance_status
     , count(*) AS ct_routes
     , count(rts.shipment_ids) AS ct_routes_with_more_than_1_shipment
FROM   route r
LEFT   JOIN LATERAL (
   SELECT array_agg(rts.shipment_id) shipment_ids
   FROM   route_to_shipment rts
   WHERE  rts.route_id = r.route_id
   HAVING count(*) > 1  -- !!!
   ) rts ON true
GROUP  BY r.acceptance_status;

    Mas existem subconsultas laterais sem agregação (portanto, nenhuma HAVINGcláusula é possível). Para o seu caso:

    SELECT r.acceptance_status
     , count(*) AS ct_routes
     , count(rts.shipment_ids) AS ct_routes_with_more_than_1_shipment
FROM   route r
LEFT   JOIN LATERAL (
   SELECT ARRAY (
      SELECT rts.shipment_id
      FROM   route_to_shipment rts
      WHERE  rts.route_id = r.route_id
      ) AS shipment_ids
   ) rts ON cardinality(shipment_ids) > 1  -- !!!
GROUP  BY r.acceptance_status;

    violino

    Só faz sentido se usarmos esse array, é claro. Então, de qualquer maneira, um construtor de array é provavelmente o ideal para sua consulta. Ver:

    • 1

relate perguntas