Conhecendo CTE: WITH

No mundo da programação, dá pra separar um pedaço do código e dar um nome pra ele — tipo criar uma função. Com CTE é a mesma vibe. Você pode separar um SELECT-subquery do seu query principal, dar um nome pra ele e depois usar esse nome no seu SQL principal.

CTE (Common Table Expressions, ou expressões de tabela comuns) é tipo um respiro pra quem já cansou de subquery aninhada. Deixa o SQL não só mais fácil de ler, mas também bem mais elegante. Se antes você ficava perdido no meio de um monte de subquery e já tava vendo tudo embaralhado — tá na hora de conhecer a "mágica" do CTE.

Pensa que você tá construindo uma casa. Normalmente, a gente quer logo colocar janela, pendurar porta (ou seja, já sair jogando subquery), mesmo que as paredes nem estejam prontas. Com CTE é diferente: primeiro você faz um rascunho organizado — cria uma tabela temporária, tipo o projeto da casa. Depois, vai construindo os andares do query, passo a passo. Fica estiloso, seguro, técnico.

No fim das contas, CTE é uma tabela virtual que você cria rapidinho usando um SELECT. É tipo uma subquery, só que mais daora. Se na programação dá pra separar uma lógica numa função com nome legal, no SQL quem faz isso é o CTE. Você escreve um SELECT, dá um nome pra ele — e usa esse nome depois como parte de um query grandão e complicado. Bonito, né? Pois é.

Exemplo de SQL com subquery:

-- query principal
SELECT *
FROM (
    SELECT *
    FROM students
    WHERE grade > 75
) AS filtered_students; -- subquery que ganhou o apelido filtered_students

Agora separando a subquery:

-- CTE/subquery que ganhou o apelido filtered_students
WITH filtered_students AS (
    SELECT *
    FROM students
    WHERE grade > 75
)

-- query principal
SELECT *
FROM filtered_students;

Incrível, mas as subqueries apareceram 20 anos antes do CTE! No padrão SQL-89 já tinha subquery, mas CTE só chegou no SQL-2009.

Sintaxe do WITH

CTE começa com a palavra-chave WITH e fica mais ou menos assim:

WITH cte_name AS (
    SELECT ... -- seu query aqui
)

SELECT ...
FROM cte_name;

Aqui:

• cte_name — é o nome do seu CTE. Pode escolher qualquer nome que faça sentido, tipo high_scores, filtered_data ou até best_students.
• Dentro dos parênteses () vai o query que prepara os dados pra usar depois.
• Depois de definir o CTE, você pode usar ele como se fosse uma tabela normal, no query principal.

Exemplo 1: CTE simples

Bora ver como o CTE funciona na prática. Imagina que você tem uma tabela students — lista de estudantes com as notas deles:

Nosso objetivo — pegar todos os estudantes com nota acima de 85 e mostrar os dados deles.

Sem CTE:

Dá pra fazer isso com uma subquery:

SELECT *
FROM (
    SELECT *
    FROM students
    WHERE grade > 85
) AS filtered_students;

Agora, usando CTE — bem mais agradável de ler:

WITH filtered_students AS (
    SELECT *
    FROM students
    WHERE grade > 85
)
SELECT *
FROM filtered_students;

Fala sério, ficou mais limpo e fácil de entender. A gente separou direitinho a preparação dos dados (WITH) da parte principal do query (SELECT). É tipo arrumar a mesa antes de começar a trabalhar — até respira melhor.

Exemplo 2: Vários CTE

Você pode definir vários CTE dentro do mesmo query. Isso é massa quando precisa preparar os dados em etapas.

Temos: tabela grades, onde ficam as notas dos estudantes por curso:

Missão: pra cada estudante, achar a média das notas e depois pegar só quem ficou acima de 85.

Resolvendo com vários CTE:

WITH student_averages AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
),
high_achievers AS (
    SELECT student_id, avg_grade
    FROM student_averages 	-- usando o primeiro CTE - student_averages
    WHERE avg_grade > 85
)

SELECT *
FROM high_achievers; -- usando o segundo CTE - high_achievers

Aqui:

1. student_averages prepara os dados — médias dos estudantes.
2. high_achievers usa esses dados pra pegar só quem ficou acima de 85.

Diferença entre CTE e subquery

Spoiler: CTE não substitui subquery, mas em vários casos é bem mais prático.

Subquery é um query dentro de outro query. Ajuda quando você quer um resultado rápido, mas se começa a empilhar subquery, o código vira uma bagunça.

Exemplo:

SELECT *
FROM (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
) AS student_averages
WHERE avg_grade > 85;

Subqueries podem estar dentro do SELECT, do FROM, do WHERE e do HAVING. E ainda podem acessar colunas do query de fora. CTE já não consegue fazer isso tão fácil.

Por outro lado, CTE deixa o código muito mais legível, então fica mais fácil de manter e com menos chance de erro. Em vez de ficar aninhando query, CTE deixa você só "dar nome" pro resultado da subquery e usar depois.

WITH student_averages AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
)

SELECT *
FROM student_averages
WHERE avg_grade > 85;

CTE é especialmente útil quando você precisa usar os dados preparados várias vezes no mesmo query.

Quando usar CTE?

• Quando você quer dividir um query complicado em etapas lógicas.
• Se o query precisa ser fácil de ler e manter. Ninguém merece se perder em código aninhado que parece macarrão.
• Pra preparar dados temporários que só vão ser usados no query atual.

Exemplo final: análise de cursos

Bora juntar tudo que aprendemos:

1. Achar estudantes com média alta.
2. Mostrar o nome deles e os cursos em que estão matriculados.

WITH student_averages AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
),
high_achievers AS (
    SELECT student_id
    FROM student_averages
    WHERE avg_grade > 85
),
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, sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id;

Repara como ficou tudo organizado:

1. Primeiro preparamos as médias.
2. Depois pegamos só os melhores estudantes.
3. Depois ligamos eles aos cursos.

Agora você tá oficialmente pronto pra usar CTE e criar SQL bonito, fácil de ler e poderoso.

Bora pros seus próprios projetos!