CTEs e Tabelas Temporárias

Q: P: Múltiplas CTEs podem se referenciar mutuamente?

R: Sim. Múltiplas CTEs definidas na mesma cláusula WITH podem referenciar as anteriores. No entanto, não podem fazer referência cruzada (CTE A referencia CTE B enquanto CTE B também referencia CTE A).

🌍 Analogia do Mundo Real

Imagine que você está cozinhando um prato complexo:

Subconsulta — Toda vez que precisar de "cebolinha picada", você precisa picá-la novamente (re-executada a cada vez)
CTE — Pique a cebolinha primeiro e coloque em uma tigela, depois use-a quando precisar (nomeada uma vez, referenciada múltiplas vezes)
Tabela temporária — Coloque os ingredientes picados em um prato temporário; não apenas este prato pode usá-los, mas eles ficam disponíveis durante toda a sessão de cozinha (acessíveis repetidamente dentro da sessão)

CTEs e tabelas temporárias seguem a abordagem "prepare primeiro, use depois", tornando consultas complexas claras e organizadas.

🎯 Conceitos Fundamentais

Cláusula WITH / CTE

Uma CTE (Common Table Expression — Expressão de Tabela Comum) usa a palavra-chave WITH para definir um conjunto de resultados nomeado temporário que pode ser referenciado como uma tabela dentro da consulta atual.

SQL

WITH nome_cte AS (
    -- Consulta da CTE
    SELECT coluna1, coluna2 FROM tabela1
)
-- Consulta principal referencia a CTE
SELECT * FROM nome_cte;

Vantagens:

Legibilidade: Divide consultas complexas em múltiplas etapas lógicas
Reutilizabilidade: A mesma CTE pode ser referenciada múltiplas vezes na consulta principal
Recursão: Suporta travessia de estruturas em árvore

CTE Recursiva

Uma CTE recursiva é definida com WITH RECURSIVE e contém duas partes:

Membro âncora: A consulta inicial, o ponto de partida da recursão
Membro recursivo: Referencia a si mesmo, expandindo passo a passo

SQL

WITH RECURSIVE nome_cte AS (
    -- Âncora: consulta inicial
    SELECT ... FROM tabela WHERE condição
    UNION ALL
    -- Recursivo: referencia a si mesmo
    SELECT ... FROM tabela JOIN nome_cte ON condição
)
SELECT * FROM nome_cte;

Casos de uso: Organogramas, diretórios de categorias, busca de caminhos e outros dados hierárquicos.

CTE vs Subconsulta

Característica	CTE	Subconsulta
Legibilidade	Melhor (nomeia primeiro, depois usa)	Difícil de ler quando profundamente aninhada
Reutilizabilidade	Múltiplas referências	Re-executada a cada referência
Recursão	✅ Suportada	❌ Não suportada
Performance	Geralmente otimizada para o mesmo plano	Igual
Escopo	Apenas a instrução atual	Apenas a instrução atual

💡 Regra geral: Quando a lógica da consulta é complexa e você precisa referenciar o mesmo conjunto de resultados múltiplas vezes, prefira CTEs.

Tabelas Temporárias — CREATE TEMPORARY TABLE

Tabelas temporárias existem dentro da sessão atual do banco de dados e são excluídas automaticamente quando a sessão termina.

SQL

CREATE TEMPORARY TABLE temp_result (
    id INT,
    name VARCHAR(50),
    amount DECIMAL(10,2)
);

INSERT INTO temp_result SELECT ...;
SELECT * FROM temp_result;

Casos de uso:

Múltiplas instruções SQL compartilhando o mesmo resultado intermediário
Dados intermediários em procedimentos armazenados
Necessidade de criar índices em resultados intermediários

Resumo de Casos de Uso de CTE

Cenário	Abordagem Recomendada
Resultados intermediários em consultas simples	Subconsulta
Dividir consultas complexas em etapas	CTE
Travessia de dados em árvore/hierárquicos	CTE recursiva
Compartilhar dados entre múltiplas instruções SQL	Tabela temporária
Resultados intermediários precisam de índices	Tabela temporária

Travessia de Dados em Árvore com WITH RECURSIVE

Campos como department_id ou manager_id autorreferenciante em uma tabela de funcionários podem formar relacionamentos hierárquicos. CTEs recursivas podem facilmente percorrê-los:

SQL

-- Encontrar todos os superiores de um funcionário (percorrer para cima)
-- Encontrar todos os subdepartamentos sob um departamento (percorrer para baixo)
-- Gerar uma série de datas (série de números)

📝 Sintaxe Básica

SQL

-- CTE básica
WITH nome_cte AS (
    SELECT ...
)
SELECT ... FROM nome_cte;

-- Múltiplas CTEs
WITH cte1 AS (...),
     cte2 AS (...)
SELECT ... FROM cte1 JOIN cte2 ON ...;

-- CTE recursiva
WITH RECURSIVE nome_cte AS (
    -- Âncora (ponto de partida)
    SELECT ... FROM tabela WHERE condição_inicial
    UNION ALL
    -- Recursivo (referencia a si mesmo)
    SELECT ... FROM tabela 
    JOIN nome_cte ON condição_de_junção
    WHERE condição_de_terminação
)
SELECT * FROM nome_cte;

-- Tabela temporária
CREATE TEMPORARY TABLE nome_temp AS
SELECT ... FROM ...;

-- Ou criar manualmente
CREATE TEMPORARY TABLE nome_temp (
    col1 INT,
    col2 VARCHAR(50)
);

💡 Dica:

CTEs só podem ser usadas na instrução SELECT/INSERT/UPDATE/DELETE que imediatamente as sucede

CTEs recursivas devem ter uma condição de terminação, caso contrário entrarão em loop infinito

Nomes de tabelas temporárias não podem conflitar com nomes de tabelas existentes

MySQL usa CREATE TEMPORARY TABLE, SQL Server usa #temp_table

📌 Exemplos

Exemplo: Usando uma CTE para Decompor uma Consulta Complexa — Análise de Vendas Per Capita por Departamento (Dificuldade ⭐⭐)

SQL

WITH dept_stats AS (
    SELECT 
        d.department_id,
        d.department_name AS department,
        COUNT(DISTINCT e.employee_id) AS employee_count,
        COUNT(o.order_id) AS order_count,
        COALESCE(SUM(o.total_amount), 0) AS total_sales
    FROM departments d
    LEFT JOIN employees e ON d.department_id = e.department_id
    LEFT JOIN orders o ON e.employee_id = o.employee_id AND o.status != 'cancelled'
    GROUP BY d.department_id, d.department_name
)
SELECT 
    department,
    employee_count,
    order_count,
    total_sales,
    CASE WHEN employee_count > 0 THEN ROUND(total_sales / employee_count, 2) ELSE 0 END AS sales_per_capita,
    CASE WHEN employee_count > 0 THEN ROUND(order_count * 1.0 / employee_count, 2) ELSE 0 END AS orders_per_capita
FROM dept_stats
ORDER BY sales_per_capita DESC;

▶ Experimente

Resultado:

TEXT

department | employee_count | order_count | total_sales | sales_per_capita | orders_per_capita
-----------+----------------+-------------+-------------+------------------+------------------
Sales      |              2 |           4 |    15100.00 |          7550.00 |              2.00
Tech       |              2 |           3 |     8750.00 |          4375.00 |              1.50
Marketing  |              1 |           0 |        0.00 |             0.00 |              0.00
Finance    |              0 |           0 |        0.00 |             0.00 |              0.00

Explicação: A CTE dept_stats primeiro agrega os dados base, depois a consulta principal calcula métricas per capita. A lógica é clara e fácil de manter.

Exemplo: CTE Recursiva — Hierarquia de Gestão de Funcionários (Dificuldade ⭐⭐⭐)

Suponha que a tabela employees tenha um campo manager_id indicando o gerente direto:

SQL

-- Primeiro adicione a coluna manager_id e dados
ALTER TABLE employees ADD COLUMN manager_id INT;

UPDATE employees SET manager_id = NULL WHERE employee_id = 1;  -- Zhang Wei: Diretor Geral
UPDATE employees SET manager_id = 1    WHERE employee_id = 2;  -- Li Na -> Zhang Wei
UPDATE employees SET manager_id = 1    WHERE employee_id = 3;  -- Wang Qiang -> Zhang Wei
UPDATE employees SET manager_id = 3    WHERE employee_id = 4;  -- Zhao Min -> Wang Qiang
UPDATE employees SET manager_id = 3    WHERE employee_id = 5;  -- Liu Yang -> Wang Qiang
UPDATE employees SET manager_id = NULL WHERE employee_id = 6;  -- Chen Jing: Sem gerente

-- Consulta recursiva: partindo de Zhang Wei, encontrar todos os subordinados (percorrer para baixo)
WITH RECURSIVE emp_hierarchy AS (
    -- Âncora: ponto de partida (Zhang Wei)
    SELECT 
        employee_id,
        CONCAT(first_name, last_name) AS name,
        manager_id,
        0 AS level,
        CAST(CONCAT(first_name, last_name) AS CHAR(500)) AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursivo: encontrar subordinados
    SELECT 
        e.employee_id,
        CONCAT(e.first_name, e.last_name),
        e.manager_id,
        h.level + 1,
        CAST(CONCAT(h.path, ' → ', e.first_name, e.last_name) AS CHAR(500))
    FROM employees e
    INNER JOIN emp_hierarchy h ON e.manager_id = h.employee_id
)
SELECT 
    CONCAT(REPEAT('  ', level), name) AS org_chart,
    level,
    path
FROM emp_hierarchy
ORDER BY path;

▶ Experimente

Resultado:

TEXT

org_chart      | level | path
---------------+-------+----------------------------
Zhang Wei      |     0 | Zhang Wei
  Li Na        |     1 | Zhang Wei → Li Na
  Wang Qiang   |     1 | Zhang Wei → Wang Qiang
    Zhao Min   |     2 | Zhang Wei → Wang Qiang → Zhao Min
    Liu Yang   |     2 | Zhang Wei → Wang Qiang → Liu Yang
Chen Jing      |     0 | Chen Jing

Explicação:

O membro âncora encontra todos os funcionários de nível superior "sem gerente"
O membro recursivo encontra subordinados nível a nível, com level + 1 registrando a profundidade
REPEAT(' ', level) usa indentação para exibir visualmente a hierarquia
CAST(... AS CHAR(500)) previne truncamento de strings durante a recursão

🎬 Prática de Cenários

Cenário 1: Calculando o Valor Vitalício do Cliente Passo a Passo (Dificuldade ⭐⭐⭐)

SQL

WITH order_summary AS (
    -- Etapa 1: Agregar dados de pedidos para cada cliente
    SELECT 
        customer_id,
        MIN(order_date) AS first_purchase,
        MAX(order_date) AS last_purchase,
        COUNT(order_id) AS order_count,
        SUM(total_amount) AS total_spent
    FROM orders
    WHERE status != 'cancelled'
    GROUP BY customer_id
),
customer_ltv AS (
    -- Etapa 2: Calcular dias de vida e frequência de gastos
    SELECT 
        customer_id,
        first_purchase,
        last_purchase,
        order_count,
        total_spent,
        DATEDIFF(last_purchase, first_purchase) AS lifetime_days,
        CASE 
            WHEN DATEDIFF(last_purchase, first_purchase) > 0 
            THEN ROUND(total_spent / (DATEDIFF(last_purchase, first_purchase) / 30.0), 2)
            ELSE total_spent
        END AS monthly_avg_spent
    FROM order_summary
)
SELECT 
    customer_id,
    first_purchase,
    last_purchase,
    order_count,
    total_spent,
    lifetime_days,
    monthly_avg_spent,
    CASE
        WHEN monthly_avg_spent >= 3000 THEN 'Alto Valor'
        WHEN monthly_avg_spent >= 1000 THEN 'Médio Valor'
        ELSE 'Baixo Valor'
    END AS customer_tier
FROM customer_ltv
ORDER BY total_spent DESC;

Ponto-chave: Múltiplas CTEs encadeadas, com lógica clara em cada etapa — agregar primeiro, calcular segundo, classificar por último. Muito mais legível do que subconsultas aninhadas.

Cenário 2: Gerando uma Série de Datas para Análise de Tendência de Vendas (Dificuldade ⭐⭐⭐)

SQL

WITH RECURSIVE date_series AS (
    -- Âncora: data de início
    SELECT CAST('2026-01-01' AS DATE) AS dt
    UNION ALL
    -- Recursivo: adiciona 1 dia a cada vez
    SELECT DATE_ADD(dt, INTERVAL 1 DAY)
    FROM date_series
    WHERE dt < '2026-06-30'
)
SELECT 
    ds.dt AS date,
    COALESCE(COUNT(o.order_id), 0) AS order_count,
    COALESCE(SUM(o.total_amount), 0) AS sales
FROM date_series ds
LEFT JOIN orders o ON o.order_date = ds.dt AND o.status != 'cancelled'
GROUP BY ds.dt
ORDER BY ds.dt;

Ponto-chave: Uma CTE recursiva gera uma série contínua de datas, depois o LEFT JOIN com a tabela de pedidos garante que datas sem pedidos também sejam exibidas (com valor 0), sendo adequado para gráficos de tendência.

❓ Perguntas Frequentes

P: Qual a diferença entre uma CTE e uma tabela temporária? R: Uma CTE só é válida dentro da instrução atual e desaparece quando a instrução termina; uma tabela temporária é válida durante toda a sessão e pode ser referenciada por múltiplas instruções SQL. CTEs são adequadas para decompor uma consulta complexa única, enquanto tabelas temporárias são adequadas para cenários onde resultados intermediários precisam ser acessados múltiplas vezes.

P: Uma CTE recursiva pode entrar em loop infinito? R: Sim, se não houver uma condição de terminação adequada. Certifique-se de que a parte recursiva tenha uma condição WHERE para limitar a profundidade ou o escopo. MySQL tem um parâmetro cte_max_recursion_depth (padrão 1000) para prevenir recursão infinita.

P: Uma CTE pode ter índices? R: Não, uma CTE em si não pode ter índices criados nela. Se resultados intermediários precisarem de índices para otimização de performance, use uma tabela temporária, pois tabelas temporárias suportam índices.

P: Múltiplas CTEs podem se referenciar mutuamente? R: Sim. Múltiplas CTEs definidas na mesma cláusula WITH podem referenciar as anteriores. No entanto, não podem fazer referência cruzada (CTE A referencia CTE B enquanto CTE B também referencia CTE A).

📖 Resumo

Tecnologia	Escopo	Reutilizável	Recursível	Indexável
Subconsulta	Instrução única	❌	❌	❌
CTE	Instrução única	✅ Múltiplas referências	✅	❌
Tabela temporária	Sessão inteira	✅ Múltiplas instruções SQL	—	✅

CTEs são definidas com WITH, melhorando a legibilidade e manutenibilidade de consultas complexas
CTEs recursivas usam WITH RECURSIVE, adequadas para travessia de dados em árvore e geração de sequências
Tabelas temporárias são excluídas automaticamente quando a sessão termina, adequadas para compartilhar resultados intermediários entre instruções SQL

📝 Exercícios

Use uma CTE para encontrar o funcionário mais bem pago de cada departamento e mostrar a diferença entre seu salário e a média do departamento.
Use uma CTE recursiva para gerar uma série de números de 1 a 20.
Use uma tabela temporária para armazenar dados resumidos de pedidos de cada cliente, depois realize uma análise de classificação de clientes com base na tabela temporária.
Questão reflexiva: Se uma CTE for referenciada duas vezes pela consulta principal, ela será executada duas vezes? Verifique com EXPLAIN.

Próxima Lição

👉 21-transactions - Processamento de Transações: Aprenda sobre as propriedades ACID de transações SQL, sintaxe BEGIN/COMMIT/ROLLBACK, pontos de salvamento SAVEPOINT, níveis de isolamento de transações e o conceito de deadlocks.

Equipe Técnica Web-Tutorial

Uma plataforma de tutoriais mantida por diversos desenvolvedores. Cada tutorial é escrito e revisado por profissionais da área correspondente. Trabalhamos para manter nosso conteúdo preciso e confiável — se encontrar algum problema, avise-nos.

Sobre Nós