LATERAL JOIN no PostgreSQL: top-N por grupo e subconsultas correlacionadas no FROM

Uma subconsulta comum no FROM e selada: ela nao enxerga as colunas das tabelas vizinhas. LATERAL remove essa restricao: uma subconsulta a direita de um join pode referenciar colunas de tabelas listadas a sua esquerda. Isso transforma a lista do FROM em um laco: para cada linha externa a subconsulta e avaliada de novo.

Por que LATERAL existe

Compare duas subconsultas. A primeira e comum e nao vai compilar, porque o tenta alcancar u.id de uma tabela declarada fora dela:

-- Fails: subquery cannot see u.id
SELECT u.id, o.amount
FROM users AS u,
     (SELECT amount FROM orders WHERE user_id = u.id) AS o;

Adicione LATERAL e a subconsulta ganha acesso a u.id. O PostgreSQL executa o lado direito uma vez por linha de users, substituindo os valores daquela linha:

SELECT u.id, o.amount
FROM users AS u,
     LATERAL (SELECT amount FROM orders WHERE user_id = u.id) AS o;

Propriedades principais:

• Voce so pode referenciar tabelas que aparecem a esquerda na lista do FROM.
• Uma virgula no FROM antes de LATERAL e um CROSS JOIN LATERAL.
• A subconsulta roda linha a linha, entao um ORDER BY e um LIMIT ligados a linha externa sao validos dentro dela.

Top-N por grupo

Essa e a jogada de assinatura do LATERAL. Voce quer os dois pedidos mais caros de cada usuario. Uma funcao de janela tambem resolve, mas LATERAL se le de forma mais direta e permite colocar o LIMIT ali dentro:

SELECT u.id, u.email, top.id AS order_id, top.amount
FROM users AS u
JOIN LATERAL (
       SELECT id, amount
       FROM orders
       WHERE user_id = u.id
       ORDER BY amount DESC
       LIMIT 2
     ) AS top ON true;

A subconsulta dispara uma vez por usuario e devolve no maximo duas linhas. O ON true e obrigatorio: a sintaxe de JOIN exige um ON, e a logica de juncao ja vive dentro da subconsulta (WHERE user_id = u.id).

LEFT JOIN LATERAL contra a forma com virgula

Um ponto sutil, porem importante. Com INNER JOIN LATERAL ou a forma com virgula, usuarios sem pedidos somem: a subconsulta devolveu zero linhas, entao a linha externa desaparece.

-- Users with zero orders disappear
SELECT u.id, top.amount
FROM users AS u,
     LATERAL (SELECT amount FROM orders WHERE user_id = u.id
              ORDER BY amount DESC LIMIT 2) AS top;

Para manter todos os usuarios, use LEFT JOIN LATERAL ... ON true. Para os usuarios sem pedidos as colunas da subconsulta viram NULL, mas a linha em si permanece:

SELECT u.id, top.amount
FROM users AS u
LEFT JOIN LATERAL (
       SELECT amount FROM orders WHERE user_id = u.id
       ORDER BY amount DESC LIMIT 2
     ) AS top ON true;

Pegadinha: o ON true em um LEFT JOIN LATERAL e enganoso. Parece um predicado sempre verdadeiro que torna o LEFT inutil, mas nao e: a linha externa sobrevive com NULL apenas quando a subconsulta devolve zero linhas. A correlacao e carregada pelo WHERE interno, nao pelo ON. Se voce poe uma condicao real no ON, ela filtra depois que LATERAL se expande e quebra o comportamento que preserva as linhas.

Funcoes que retornam conjuntos no LATERAL

LATERAL brilha com funcoes como generate_series, unnest ou jsonb_array_elements, que precisam de um valor de uma coluna vizinha. Gere uma linha por mes desde que cada usuario se cadastrou:

SELECT u.id, m.month
FROM users AS u
CROSS JOIN LATERAL generate_series(
       date_trunc('month', u.created_at),
       date_trunc('month', now()),
       interval '1 month'
     ) AS m(month);

Antes de uma chamada de funcao no FROM a palavra LATERAL e opcional: para funcoes de tabela ela e implicita. Escreve-la de forma explicita ainda compensa: o codigo deixa claro que ha uma correlacao a direita.

MySQL e ClickHouse

• MySQL suporta LATERAL desde a 8.0.14, com a mesma sintaxe: JOIN LATERAL (...) ON true. Em versoes mais antigas o top-N e resolvido com variaveis ou funcoes de janela (a partir do 8.0).
• ClickHouse nao tem um LATERAL geral. O top-N por grupo e feito com arrayJoin e funcoes como groupArraySorted, ou com LIMIT BY:

-- ClickHouse: top-2 orders per user
SELECT user_id, id, amount
FROM orders
ORDER BY amount DESC
LIMIT 2 BY user_id;

LIMIT N BY e um substituto compacto do top-N por grupo que o PostgreSQL monta com LATERAL. Lembre do vinculo: uma subconsulta correlacionada no FROM e exatamente LATERAL, e o seu feijao com arroz e o top-N e a expansao de funcoes que retornam conjuntos.