Criando tabelas temporárias usando WITH

Tabelas temporárias são uma ótima maneira de guardar resultados intermediários pra depois trabalhar com eles. É tipo em programação: ao invés de copiar aquele mesmo pedaço de código gigante, você salva ele numa variável e usa de boa depois. No mundo do SQL, essas "variáveis" geralmente são as tabelas temporárias.

Mas o CTE deixa tudo ainda mais fácil — ele permite criar tabelas temporárias sem dor de cabeça:

• Sem se preocupar em apagar depois.

  O CTE só existe enquanto a query tá rodando. Assim que o SQL termina — o CTE some, tipo um assistente invisível que faz tudo e desaparece sem deixar rastro.

• O código fica mais claro.

  Criar um CTE é bem mais simples do que ficar criando e apagando tabela temporária física. Tá tudo ali, na cara, direto ao ponto.

• Perfeito pra tarefas "de uma vez só".

  Se você só precisa processar os dados numa etapa — o CTE resolve fácil. Rápido, limpo, sem efeitos colaterais.

Sintaxe do CTE como tabela temporária

Vamos relembrar a sintaxe do CTE:

WITH cte_name AS (
    SELECT ...
)
SELECT ...
FROM cte_name;

Aqui o WITH cria uma "expressão de tabela" temporária, que fica disponível durante toda a query onde ela foi definida. Essa expressão parece uma tabela, mas só vive até o fim da execução da query.

Exemplo: vamos achar os melhores alunos

Bora criar uma tabela temporária usando CTE pra encontrar os estudantes que têm média (grade) maior que 90. Depois vamos mostrar a lista deles.

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
)
SELECT *
FROM high_achievers;

Aqui a gente:

• usa o WITH pra criar a tabela temporária high_achievers.
• dentro do CTE, agrupa as notas (grades) de cada estudante (student_id) e calcula a média.
• na query SELECT * a gente só referencia a tabela temporária high_achievers como se fosse uma tabela normal.

Comparando CTE e tabelas temporárias

Às vezes rola a dúvida: qual a diferença entre usar CTE e usar tabelas temporárias criadas com o comando CREATE TEMP TABLE?

Olha um exemplo de tabela temporária tradicional (CREATE TEMP TABLE) pros mesmos dados:

CREATE TEMP TABLE high_achievers_temp AS
	SELECT student_id, AVG(grade) AS avg_grade
	FROM grades
	GROUP BY student_id
	HAVING AVG(grade) > 90;

SELECT *
FROM high_achievers_temp;

DROP TABLE high_achievers_temp;  -- Não esquece de apagar a tabela!

E a mesma consulta usando CTE:

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
)
SELECT *
FROM high_achievers;

Quando usar CTE e quando usar tabelas temporárias

O CTE é ótimo quando você quer preparar dados intermediários rapidinho e já trabalhar com eles — sem complicação. Não precisa se preocupar em apagar: o CTE some sozinho assim que a query termina. Ele já aparece na estrutura do código SQL, não fica escondido igual tabela temporária. Isso deixa as queries mais limpas, simples e fáceis de entender, principalmente se você tá fazendo vários passos de processamento de dados em sequência. E ainda dá pra combinar vários CTEs, aninhar um dentro do outro e montar lógicas complexas — a gente vai falar disso depois.

Essa estrutura funciona muito bem quando a query é de uso único e os dados só vão ser usados ali. Agora, se você pretende usar o resultado várias vezes em partes diferentes do sistema ou quer guardar os dados intermediários durante toda a sessão — as tabelas temporárias são uma escolha mais confiável. Principalmente se o volume de dados for grande e precisar de performance: nesses casos, as tabelas temporárias físicas costumam ser mais estáveis e rápidas.

Depende da tarefa: CTE é uma ferramenta rápida e elegante pra processamento local de dados. Já a tabela temporária é o trampo pesado pra cenários mais longos e complexos.

Exemplo: Agregando dados

Imagina que a gente tem uma tabela enrollments, que guarda quais estudantes estão inscritos em quais cursos. Queremos saber quantos estudantes estão em cada curso, mas só mostrar cursos com mais de 5 alunos.

Com CTE, fica assim:

WITH course_counts AS (
    SELECT course_id, COUNT(student_id) AS student_count
    FROM enrollments
    GROUP BY course_id
    HAVING COUNT(student_id) > 5
)

SELECT *
FROM course_counts
ORDER BY student_count DESC;

Múltiplos CTE: Montando etapas

E se precisar dividir a tarefa em várias etapas? Tipo, primeiro pegar os estudantes com média alta, depois achar os cursos deles? Suave!

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
),
student_courses AS (
    SELECT e.student_id, c.course_name
    FROM enrollments e
    JOIN courses c ON e.course_id = c.course_id
)

SELECT ha.student_id, ha.avg_grade, sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id;

Agora a query tem dois CTE:

1. high_achievers acha os melhores alunos.
2. student_courses liga os estudantes aos cursos.

O resultado é uma lista dos estudantes com média alta e os cursos deles.

Erros comuns ao usar CTE

Dados grandes demais. Os CTEs são materializados na memória do PostgreSQL. Se você criar um CTE com um resultado gigante, pode deixar a query lenta ou até estourar o limite de memória.

Exagero no uso. Usar CTE onde um subquery simples resolveria pode só complicar tudo.

Índices esquecidos. Se os dados do CTE vêm de tabelas grandes sem índice, a query vai ficar lenta.