SQL para análise de dados · Guias · 30 de outubro de 2026 · 13 min de leitura

CTE no SQL (cláusula WITH): o que é e como usar, com exemplos

Uma CTE (common table expression, ou expressão de tabela comum) é um resultado temporário com nome que existe só durante uma consulta. Você a define com WITH nome AS (SELECT ...) e depois usa nome na consulta principal como se fosse uma tabela. Por baixo dos panos, não acontece nada que uma subconsulta não pudesse fazer. O ganho é legibilidade: em vez de aninhar consultas em três níveis, você as escreve de cima para baixo, uma etapa por CTE, e dá a cada etapa um nome que diz o que ela guarda.

WITH step_one AS (
  SELECT ...
),
step_two AS (
  SELECT ... FROM step_one ...
)
SELECT ... FROM step_two;

Os exemplos rodam no banco de dados de uma companhia aérea usado no curso de SQL da Nomto: 33.121 voos entre 104 aeroportos entre julho e setembro de 2026, com reservas, bilhetes, assentos e um programa de fidelidade. Os valores estão em dólares americanos e os textos dos dados em inglês. O SQL segue a sintaxe do PostgreSQL, e cada resultado mostrado saiu da execução real da consulta.

Sua primeira CTE

Quantos bilhetes os membros do programa de fidelidade costumam ter? Isso exige dois níveis de agregação: bilhetes por membro e, depois, membros por quantidade de bilhetes. Com uma CTE, cada nível ganha o próprio bloco:

WITH per_member AS (
  SELECT member_id, COUNT(*) AS tickets
  FROM ticket_members
  GROUP BY member_id
)
SELECT tickets, COUNT(*) AS members
FROM per_member
GROUP BY tickets
ORDER BY tickets
LIMIT 8;

per_member tem uma linha por membro que possui bilhetes. A consulta principal a lê como uma tabela. Exatamente a mesma lógica cabe numa tabela derivada (FROM (SELECT ...) AS per_member), e para uma etapa só isso está ótimo. A diferença aparece quando há várias etapas.

(O salto em sete e oito bilhetes, depois de os números virem caindo, está nos dados, não é um bug da consulta. Vale olhar com calma antes que alguém monte um gráfico em cima disso, e uma consulta de duas etapas como esta muitas vezes é como você percebe essas coisas.)

Várias CTEs: uma etapa por ideia

Qual é a taxa de ocupação média (passageiros divididos por assentos) dos voos que chegaram, por modelo de avião? São necessários três ingredientes: assentos por modelo, passageiros por voo e os próprios voos. Cada um vira uma CTE, e as CTEs seguintes podem usar as anteriores:

WITH seats_per_model AS (
  SELECT aircraft_code, COUNT(*) AS seats
  FROM seats
  GROUP BY aircraft_code
),
passengers AS (
  SELECT flight_id, COUNT(*) AS pax
  FROM ticket_flights
  GROUP BY flight_id
),
flight_load AS (
  SELECT f.flight_id,
         f.aircraft_code,
         COALESCE(p.pax, 0) AS pax,
         s.seats,
         COALESCE(p.pax, 0) * 1.0 / s.seats AS load_factor
  FROM flights f
  JOIN seats_per_model s ON s.aircraft_code = f.aircraft_code
  LEFT JOIN passengers p ON p.flight_id = f.flight_id
  WHERE f.status = 'Arrived'
)
SELECT aircraft_code,
       COUNT(*) AS flights,
       MAX(seats) AS seats,
       ROUND(AVG(pax), 1) AS avg_pax,
       ROUND(100 * AVG(load_factor), 1) AS avg_load_pct
FROM flight_load
GROUP BY aircraft_code
ORDER BY avg_load_pct DESC;

Cada CTE faz uma coisa, e o SELECT final só resume. Pré-agregar assentos e passageiros antes do join também é o que mantém os números corretos: juntar flights com seats e ticket_flights brutos multiplicaria as linhas e inflaria todas as contagens.

Esta é a mesma consulta escrita com tabelas derivadas aninhadas (reduzida às três primeiras linhas):

SELECT aircraft_code, COUNT(*) AS flights, ROUND(100 * AVG(load_factor), 1) AS avg_load_pct
FROM (
  SELECT f.aircraft_code, COALESCE(p.pax, 0) * 1.0 / s.seats AS load_factor
  FROM flights f
  JOIN (SELECT aircraft_code, COUNT(*) AS seats FROM seats GROUP BY aircraft_code) s
    ON s.aircraft_code = f.aircraft_code
  LEFT JOIN (SELECT flight_id, COUNT(*) AS pax FROM ticket_flights GROUP BY flight_id) p
    ON p.flight_id = f.flight_id
  WHERE f.status = 'Arrived'
) AS flight_load
GROUP BY aircraft_code
ORDER BY avg_load_pct DESC
LIMIT 3;

Mesma resposta. Mas você precisa ler de dentro para fora, e as etapas não têm nome. Numa revisão de código, “confere a passengers” é bem mais fácil de dizer do que “confere a segunda subconsulta dentro da primeira”.

Depurar uma cadeia de CTEs etapa por etapa

A vantagem real das etapas com nome: você pode inspecionar qualquer uma delas trocando o SELECT final. Substitua por SELECT * FROM flight_load LIMIT 20, ou por uma contagem, e você vê exatamente o que aquela etapa produz.

Esse hábito pega erros como este. Troque o LEFT JOIN passengers por um JOIN simples e as taxas de ocupação disparam:

WITH seats_per_model AS (
  SELECT aircraft_code, COUNT(*) AS seats FROM seats GROUP BY aircraft_code
),
passengers AS (
  SELECT flight_id, COUNT(*) AS pax FROM ticket_flights GROUP BY flight_id
)
SELECT f.aircraft_code,
       COUNT(*) AS flights,
       ROUND(100 * AVG(p.pax * 1.0 / s.seats), 1) AS avg_load_pct
FROM flights f
JOIN seats_per_model s ON s.aircraft_code = f.aircraft_code
JOIN passengers p ON p.flight_id = f.flight_id
WHERE f.status = 'Arrived'
GROUP BY f.aircraft_code
ORDER BY avg_load_pct DESC;

A ocupação do Cessna vai de 14% para 50%, porque os 3.328 voos vazios dele foram descartados em silêncio. Uma verificação rápida na etapa passengers mostra o motivo: dos 16.707 voos que chegaram, só 11.438 têm algum bilhete. Um inner join fica só com esses. Nenhuma das versões é “a” resposta por si só, mas você precisa saber qual pergunta está respondendo, e as etapas com nome transformam essa verificação numa linha.

Usar uma CTE mais de uma vez

Uma CTE pode ser referenciada várias vezes na consulta principal, algo que uma tabela derivada só consegue sendo copiada. Um uso clássico: um valor e a participação dele no total.

WITH route_revenue AS (
  SELECT f.departure_airport || '-' || f.arrival_airport AS route,
         SUM(tf.amount) AS revenue
  FROM ticket_flights tf
  JOIN flights f ON f.flight_id = tf.flight_id
  WHERE f.status = 'Arrived'
  GROUP BY route
)
SELECT route,
       ROUND(revenue) AS revenue,
       ROUND(100 * revenue / (SELECT SUM(revenue) FROM route_revenue), 2) AS pct_of_total
FROM route_revenue
ORDER BY revenue DESC
LIMIT 5;

route_revenue é usada duas vezes: como tabela principal e dentro da subconsulta escalar do total. Somando as 451 rotas, os voos que chegaram trouxeram US$ 114,3 milhões, e a rota do topo, de Domodedovo a Khabarovsk, faturou quase 4% disso.

Uma CTE também pode guardar um resumo de uma linha que você junta a todas as linhas. Quais dias tiveram mais reservas em comparação com o dia médio?

WITH daily AS (
  SELECT CAST(book_date AS date) AS day, COUNT(*) AS bookings
  FROM bookings
  GROUP BY day
),
stats AS (
  SELECT AVG(bookings) AS avg_bookings FROM daily
)
SELECT d.day, d.bookings, ROUND(d.bookings / s.avg_bookings, 2) AS vs_avg
FROM daily d
CROSS JOIN stats s
ORDER BY d.bookings DESC
LIMIT 5;

stats lê de daily, e a consulta principal lê das duas. (A média, 4.692,6 reservas por dia, é calculada sobre 56 dias, de 17 de junho a 11 de agosto; o último dia está só em parte nos dados, o que puxa a média um pouco para baixo. Nesse tipo de comparação, normalmente você descartaria os dias incompletos.)

Dar nome às colunas de uma CTE

Você pode listar nomes de colunas depois do nome da CTE. Eles substituem os nomes que a consulta interna dá às colunas:

WITH model_seats (code, seat_count) AS (
  SELECT aircraft_code, COUNT(*)
  FROM seats
  GROUP BY aircraft_code
)
SELECT code, seat_count
FROM model_seats
ORDER BY seat_count DESC
LIMIT 3;

A maioria das pessoas usa alias dentro do SELECT (COUNT(*) AS seat_count). A lista de colunas é uma opção organizada quando a consulta interna tem expressões sem nome, e deixa a “interface” da CTE visível já na primeira linha.

CTE recursiva

WITH RECURSIVE permite que uma CTE faça referência a si mesma. Ela tem duas partes unidas por UNION ou UNION ALL: uma âncora, que produz as linhas iniciais, e uma parte recursiva, que produz linhas novas a partir das linhas da rodada anterior. A recursão para quando uma rodada não produz nada novo.

O menor exemplo gera números:

WITH RECURSIVE n AS (
  SELECT 1 AS i
  UNION ALL
  SELECT i + 1 FROM n WHERE i < 5
)
SELECT i FROM n;

O WHERE i < 5 é a condição de parada. Sem ela, a consulta nunca termina. (Para números e datas, generate_series é mais simples; veja o guia de date spine, em inglês.)

A recursão mostra seu valor em grafos e hierarquias. Quantos voos são necessários para ir de Yuzhno-Sakhalinsk (UUS) aos outros aeroportos da malha?

WITH RECURSIVE routes AS MATERIALIZED (
  SELECT DISTINCT departure_airport AS src, arrival_airport AS dst
  FROM flights
),
reach AS (
  SELECT 'UUS'::VARCHAR AS airport, 0 AS hops
  UNION
  SELECT r.dst, reach.hops + 1
  FROM reach
  JOIN routes r ON r.src = reach.airport
  WHERE reach.hops < 4
),
best AS (
  SELECT airport, MIN(hops) AS hops
  FROM reach
  GROUP BY airport
)
SELECT hops, COUNT(*) AS airports
FROM best
GROUP BY hops
ORDER BY hops;

A partir de UUS há quatro destinos diretos, mais 76 aeroportos exigem uma conexão, e os 23 restantes exigem duas conexões (três voos). Ninguém precisa de um quarto voo: a busca podia ir até quatro saltos de profundidade e não encontrou nada novo depois de três. Somando, são 104 aeroportos: a malha inteira é alcançável a partir de UUS em no máximo três voos.

Quatro detalhes deixam essa consulta segura:

• WITH RECURSIVE é escrito uma vez, no topo, mesmo que só reach seja recursiva. CTEs comuns (routes, best) podem ficar no mesmo WITH.
• routes AS MATERIALIZED calcula a lista de rotas uma vez e a reaproveita em todas as rodadas. Sem essa palavra, a versão do DuckDB usada na sala de SQL devolveu, em algumas execuções, um resultado incompleto para esta consulta (às vezes só as linhas de 0 e 1 salto); com ela, o resultado foi o mesmo em todas as execuções. Mais sobre MATERIALIZED logo abaixo.
• UNION (e não UNION ALL) remove linhas (airport, hops) duplicadas, o que impede que as linhas explodam quando muitos caminhos levam ao mesmo aeroporto.
• hops < 4 é um limite rígido de profundidade. Num grafo com ciclos (e as rotas vão nos dois sentidos), uma recursão sem limite pode rodar para sempre.

CTE vs subconsulta vs tabela temporária vs view

Use uma CTE para etapas dentro de uma análise. Se o mesmo resultado intermediário alimenta várias consultas separadas, uma tabela temporária evita recalculá-lo. Se um time inteiro precisa da lógica, o lugar dela é uma view ou um modelo no seu pipeline de dados.

CTE deixa a consulta mais rápida ou mais lenta?

Nenhum dos dois, por padrão. No PostgreSQL 12 ou mais novo, uma CTE não recursiva referenciada uma vez é incorporada à consulta principal, exatamente como uma subconsulta, e o otimizador enxerga através dela. Uma CTE referenciada mais de uma vez em geral é calculada uma vez e reaproveitada.

Você pode mudar essa escolha. WITH big AS MATERIALIZED (...) força o PostgreSQL a calcular a CTE uma vez e guardá-la, o que ajuda quando a CTE é cara e o otimizador insiste em recalculá-la. NOT MATERIALIZED força a incorporação, para que filtros da consulta principal possam ser empurrados para dentro. O DuckDB aceita as mesmas palavras-chave MATERIALIZED e NOT MATERIALIZED.

O velho conselho “CTEs são uma barreira de otimização” vem do PostgreSQL 11 e anteriores, que sempre as materializavam. Se você leu isso num post antigo, é por isso.

Notas de portabilidade

• PostgreSQL, DuckDB, MySQL 8+, SQLite, BigQuery, Snowflake: todos suportam WITH e WITH RECURSIVE.
• SQL Server suporta CTEs e recursão, mas não usa a palavra RECURSIVE: você escreve só WITH. Ele também exige um ponto e vírgula antes do WITH se a instrução anterior não terminou com um, e é por isso que você vê ;WITH em código de SQL Server.
• CTEs que modificam dados (WITH deleted AS (DELETE ... RETURNING *) SELECT ...) são um recurso do PostgreSQL. O DuckDB e a maioria dos outros bancos só permitem SELECT dentro de uma CTE.
• O cast 'UUS'::VARCHAR na âncora fixa o tipo da coluna para as duas partes da recursão. O PostgreSQL exige tipos iguais entre a âncora e a parte recursiva; CAST('UUS' AS VARCHAR) é a forma portável.

Erros comuns

1. Uma vírgula depois da última CTE. As CTEs são separadas por vírgulas, mas não há vírgula entre a última e o SELECT principal. ), SELECT ... é erro de sintaxe.
2. Escrever WITH duas vezes. Uma instrução tem um WITH; as CTEs extras vêm depois de vírgulas: WITH a AS (...), b AS (...).
3. Esperar que a CTE sobreviva à instrução. Depois do ponto e vírgula, o nome desaparece. Se a próxima consulta precisar dela, repita a CTE ou crie uma tabela temporária.
4. Confiar no ORDER BY dentro de uma CTE. Ele não fixa a ordem do resultado final. Ordene na consulta principal.
5. Uma CTE recursiva sem condição de parada. Sempre limite a profundidade ou garanta que cada rodada acabe não devolvendo linhas. UNION em vez de UNION ALL ajuda quando os caminhos revisitam as mesmas linhas.
6. Uma cadeia longa que ninguém confere. Sete CTEs só são legíveis se cada uma estiver correta. Olhe a contagem de linhas de cada etapa enquanto monta a cadeia, especialmente depois de joins.

Se você quer as formas de subconsulta que as CTEs substituem, veja subconsulta SQL. Muitas cadeias analíticas de CTEs terminam numa função de janela; o guia de funções de janela SQL e o guia de retenção por coorte (em inglês) mostram cadeias reais mais longas.

Perguntas frequentes

O que é CTE no SQL?

Uma common table expression (expressão de tabela comum): um resultado temporário com nome, definido com WITH nome AS (SELECT ...) e usado pela consulta que vem depois. Ela existe só durante essa instrução.

Qual a diferença entre CTE e subconsulta?

Logicamente, nenhuma: uma CTE usada uma vez se comporta como uma tabela derivada. As diferenças são práticas. Uma CTE tem nome, fica no topo para que a consulta seja lida em ordem, pode ser referenciada várias vezes e pode ser recursiva.

Posso usar várias CTEs numa consulta?

Sim. Escreva WITH uma vez e separe as CTEs com vírgulas. Cada CTE pode usar as que foram definidas antes dela.

CTE é mais lenta que subconsulta?

Não nas versões atuais do PostgreSQL e do DuckDB: uma CTE usada uma vez é incorporada como uma subconsulta. No PostgreSQL 11 e anteriores, as CTEs eram sempre materializadas, o que podia bloquear otimizações. Use MATERIALIZED ou NOT MATERIALIZED se precisar controlar isso.

Para que serve uma CTE recursiva?

Hierarquias e grafos: organogramas, árvores de categorias, rotas entre aeroportos, cadeias de eventos. Ela aplica repetidamente uma consulta à própria saída anterior até não aparecerem linhas novas.

Outros idiomas: English, Español, Tiếng Việt, 简体中文