SQL Avançado Desmistificado

SQL avançado não é uma linguagem diferente do SQL que você já escreve, é o mesmo modelo declarativo estendido para cobrir problemas que antes forçavam uma ida de volta ao código da aplicação.

CTEs, funções de janela, joins LATERAL e consultas recursivas existem para permitir que uma única instrução descreva lógica que, de outra forma, exigiria loops, tabelas temporárias ou várias idas e vindas ao banco de dados.

Esta página constrói o modelo mental por trás dessas quatro ferramentas para que as páginas dedicadas a CTEs, funções de janela, joins LATERAL e CTEs recursivas sejam lidas como aplicações de uma única ideia, em vez de quatro sintaxes não relacionadas para memorizar.

Resumo

• Recursos avançados de SQL permitem nomear resultados intermediários, computar entre conjuntos de linhas sem colapsá-los, correlacionar subconsultas por linha e iterar até um ponto fixo, tudo dentro de uma única instrução declarativa.
• Por que é Importante: Expressar essa lógica em SQL mantém o trabalho próximo aos dados, evita idas e vindas linha por linha e dá ao planejador de consultas visibilidade total para otimizar toda a computação em conjunto.
• Conceitos Chave: expressão de tabela comum (CTE), função de janela, partição, correlação LATERAL, ponto fixo recursivo.
• Quando Usar: Construir relatórios que classificam ou comparam linhas dentro de grupos, percorrer hierarquias ou grafos, obter um resultado "top N por grupo", ou nomear uma transformação de várias etapas para legibilidade.
• Limitações / Compromissos: Essas ferramentas trocam alguma simplicidade bruta por poder expressivo, e uma CTE mal formatada ou uma consulta recursiva podem ocultar um problema de desempenho que um join simples teria exposto imediatamente.
• Tópicos Relacionados: planejamento de consultas e EXPLAIN, ordenação suportada por índice, views materializadas, pensamento baseado em conjuntos versus loops procedurais.

Fundamentos

Todo recurso avançado de SQL nesta página ainda é apenas uma consulta sobre conjuntos de linhas, a diferença está apenas em quanta estrutura você pode impor a essa computação antes que o resultado final retorne.

Uma CTE (a cláusula WITH) não é nada mais do que uma subconsulta nomeada que você pode referenciar posteriormente na mesma instrução, existindo puramente para tornar uma consulta longa legível, dividindo-a em etapas nomeadas.

Uma função de janela computa um valor em um conjunto de linhas relacionadas, chamado sua partição, sem mesclar essas linhas em uma única linha de saída como o GROUP BY faria.

LATERAL permite que uma subconsulta na cláusula FROM veja colunas de itens FROM que aparecem antes dela, executando efetivamente uma pequena consulta correlacionada uma vez por linha externa, ainda produzindo um único resultado baseado em conjuntos.

Uma CTE recursiva repete uma consulta contra seu próprio resultado crescente até que nenhuma nova linha apareça, que é como o SQL expressa caminhadas de hierarquia e travessia de grafos sem um loop do lado do cliente.

A maneira mais simples de manter as quatro em mente é pensar em uma instrução SQL como uma linha de montagem: CTEs nomeiam as estações, funções de janela adicionam colunas computadas sem quebrar a linha em cintos separados, LATERAL permite que uma estação olhe para trás para a linha de saída de uma estação anterior, linha por linha, e a recursão permite que uma estação realimente sua própria saída até que a linha pare naturalmente.

Mecânicas e Interações

O planejador de consultas processa as cláusulas de uma instrução em uma ordem lógica fixa que é diferente da ordem em que você as digita, e entender essa ordem explica por que as funções de janela podem referenciar resultados de GROUP BY, mas não o contrário.

FROM / JOIN  ->  WHERE  ->  GROUP BY  ->  HAVING
   ->  funções de janela  ->  lista SELECT
   ->  DISTINCT  ->  ORDER BY  ->  LIMIT / OFFSET

Como as funções de janela são executadas após o agrupamento e a filtragem, mas antes que a lista SELECT final seja montada, elas podem classificar ou comparar linhas já agregadas sem a necessidade de uma segunda passagem pela tabela.

Uma CTE não recursiva não é automaticamente uma "barreira de otimização" como era em versões mais antigas do PostgreSQL; desde o PostgreSQL 12, o planejador pode incorporar uma consulta WITH na instrução circundante exatamente como uma subconsulta, a menos que seja referenciada mais de uma vez ou marcada como MATERIALIZED.

Essa distinção é importante porque uma CTE incorporada permite que o planejador empurre filtros para dentro dela, enquanto uma materializada é computada uma vez e depois lida novamente, o que pode ajudar quando a mesma CTE é reutilizada várias vezes, mas pode prejudicar quando impede o empurrão de filtros.

A correlação LATERAL é mecanicamente semelhante a uma subconsulta por linha, mas como ela reside na cláusula FROM, ela pode retornar várias colunas e várias linhas por linha externa, o que é exatamente o que uma subconsulta correlacionada simples na lista SELECT não pode fazer.

SELECT a.id, recent.total
FROM app.accounts a
JOIN LATERAL (
  SELECT total FROM app.orders o
  WHERE o.account_id = a.id
  ORDER BY o.created_at DESC LIMIT 1
) recent ON true;

CTEs recursivas são executadas como um loop de tabela de trabalho: o termo não recursivo semeia o primeiro lote de linhas, o termo recursivo é executado novamente contra apenas as linhas produzidas na iteração anterior, e o loop para no instante em que uma iteração produz zero novas linhas.

Esse loop não tem detecção de ciclo embutida, portanto, uma hierarquia autorreferenciada com um ciclo girará até que você adicione uma guarda explícita, geralmente um array de chaves visitadas verificadas com NOT (id = ANY(path)).

Considerações e Aplicações Avançadas

Funções de janela não têm um tipo de índice dedicado próprio, mas um índice que corresponde às colunas PARTITION BY e ORDER BY permite que o planejador evite uma etapa de ordenação explícita antes de computar a janela, o que geralmente é o maior custo em uma consulta de janela.

CTEs recursivas têm um custo real de materialização por iteração, pois o PostgreSQL precisa armazenar a tabela de trabalho entre as etapas, portanto, uma caminhada de grafo em uma tabela grande e mal delimitada pode consumir muito mais work_mem e tempo do que o loop equivalente em código de aplicação.

O pivoteamento de linhas em colunas pode ser feito com agregação condicional usando FILTER, com crosstab() da extensão tablefunc, ou com jsonb_object_agg() quando as colunas de destino não são conhecidas antecipadamente, e cada uma dessas opções troca a simplicidade de formato fixo por flexibilidade dinâmica de maneiras diferentes.

Muitas equipes recorrem a loops do lado da aplicação por hábito, mesmo depois que uma consulta poderia expressar a mesma lógica declarativamente, e o compromisso honesto é que as versões SQL desses padrões são menos familiares para ler rapidamente, mas evitam a latência de enviar linhas de volta e para frente entre o banco de dados e a camada de aplicação.

Equívocos Comuns

• "Toda CTE é uma barreira de otimização." Isso era verdade antes do PostgreSQL 12, mas uma consulta WITH simples hoje é incorporada como uma subconsulta, a menos que seja referenciada várias vezes ou explicitamente marcada como MATERIALIZED.
• "Funções de janela precisam de GROUP BY para funcionar." Uma função de janela pode ser executada em todo o conjunto de resultados sem nenhum GROUP BY, pois PARTITION BY dentro da cláusula OVER define seu próprio agrupamento independente do GROUP BY da consulta.
• "LATERAL é apenas uma subconsulta correlacionada." Uma subconsulta correlacionada na lista SELECT só pode retornar um valor escalar por linha externa, enquanto LATERAL na cláusula FROM pode retornar várias linhas e colunas por linha externa.
• "CTEs recursivas usam recursão real como uma pilha de chamadas de função." O PostgreSQL as avalia iterativamente como um loop de ponto fixo sobre uma tabela de trabalho, não como chamadas de função aninhadas, razão pela qual a recursão descontrolada aparece como uma tabela de trabalho em constante crescimento em vez de um estouro de pilha.
• "Pivoteamento sempre requer a extensão tablefunc." A agregação condicional com FILTER lida com colunas de pivô fixas e conhecidas sem instalar nada extra.

FAQs

Relacionados

• Noções Básicas de SQL Avançado - exemplos práticos de CTEs, funções de janela e LATERAL
• Expressões de Tabela Comuns - sintaxe de CTE, nomenclatura e materialização em profundidade
• Funções de Janela - a sintaxe completa OVER, PARTITION BY e frame
• CTEs Recursivas - caminhadas de hierarquia e grafo com guardas de ciclo
• Joins LATERAL - subconsultas correlacionadas por linha na cláusula FROM
• Noções Básicas de EXPLAIN - lendo o plano por trás de qualquer uma dessas consultas

Versões do Stack: Esta página foi escrita para PostgreSQL 18.4 (linha estável 18, linha de manutenção 17), onde o comportamento de incorporação de CTE introduzido no PostgreSQL 12 e os mecanismos de função de janela e consulta recursiva descritos aqui permanecem atuais.