SQL CTE: Common Table Expressions e a Cláusula WITH

Lição 6.4 · Tempo de leitura: ~8 min · Tarefas para praticar ↓

Uma CTE em SQL (common table expression) é um resultado temporário com nome que você define com a cláusula WITH e depois usa na consulta principal como uma tabela. Nesta lição, você vai aprender a sintaxe da CTE, como ela se compara a uma subconsulta, como encadear várias CTEs e como montar uma análise em várias etapas que continua fácil de ler.

Nas lições anteriores, você escreveu subconsultas dentro de FROM e WHERE. Elas funcionam, mas à medida que a lógica cresce, as subconsultas aninhadas ficam difíceis de ler: é preciso começar pela consulta mais interna e ir para fora. Uma CTE permite dar um nome a cada etapa e escrever as etapas de cima para baixo, na ordem em que você pensa nelas.

O Que É uma CTE em SQL?

Uma common table expression é um resultado de consulta com nome, definido no início de uma instrução. Ela só existe enquanto essa instrução é executada: nada é guardado no banco de dados, e outras consultas não a veem.

Você pode pensar numa CTE como uma subconsulta com nome. As principais vantagens são:

• Legibilidade: cada etapa tem um nome significativo.
• Reutilização: a consulta principal pode referenciar a mesma CTE várias vezes.
• Modularidade: uma consulta complexa divide-se em pequenas etapas lógicas.
• Depuração: cada CTE pode ser executada e verificada sozinha.

Sintaxe Básica

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

• WITH introduz a CTE.
• cte_name é o nome que a consulta principal vai usar.
• A consulta entre parênteses define as linhas da CTE.
• A consulta principal que vem a seguir trabalha com cte_name como com uma tabela comum.

A Sua Primeira CTE

Vamos encontrar os clientes que gastaram mais de $100 no total:

WITH customer_spending AS (
    SELECT customer_id,
           SUM(amount) AS total_spent,
           COUNT(*) AS payment_count,
           AVG(amount) AS avg_payment
    FROM payment
    GROUP BY customer_id
)
SELECT customer_id, total_spent, payment_count, avg_payment
FROM customer_spending
WHERE total_spent > 100
ORDER BY total_spent DESC;

Resultado: os clientes com mais de $100 em pagamentos, com o número de pagamentos e o pagamento médio, os que mais gastaram primeiro.

A CTE calcula o gasto por cliente, e a consulta principal apenas filtra e ordena. O nome customer_spending diz ao leitor o que são os dados intermediários antes mesmo de ele ler como são calculados.

CTE vs Subconsulta

Esta é a mesma consulta escrita com uma subconsulta no FROM:

SELECT customer_id, total_spent, payment_count, avg_payment
FROM (
    SELECT customer_id,
           SUM(amount) AS total_spent,
           COUNT(*) AS payment_count,
           AVG(amount) AS avg_payment
    FROM payment
    GROUP BY customer_id
) AS spending_data
WHERE total_spent > 100
ORDER BY total_spent DESC;

As duas consultas devolvem as mesmas linhas. A diferença está em como elas se leem:

Para uma única etapa curta, uma subconsulta serve perfeitamente. Quando a lógica tem duas ou mais etapas, ou um resultado é necessário duas vezes, uma CTE costuma ser mais clara.

Várias CTEs numa Consulta

Você pode definir várias CTEs separadas por vírgulas. Cada uma pode usar as CTEs definidas antes dela:

WITH customer_spending AS (
    SELECT customer_id, SUM(amount) AS total_spent
    FROM payment
    GROUP BY customer_id
),
high_spenders AS (
    SELECT customer_id, total_spent
    FROM customer_spending
    WHERE total_spent > 150
)
SELECT c.customer_id,
       CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
       c.email,
       hs.total_spent
FROM high_spenders AS hs
JOIN customer AS c ON c.customer_id = hs.customer_id
ORDER BY hs.total_spent DESC;

Resultado: nomes e e-mails dos clientes que gastaram mais de $150, os que mais gastaram primeiro.

A consulta lê-se como um pequeno plano:

1. customer_spending soma os pagamentos de cada cliente.
2. high_spenders mantém apenas os clientes acima de $150.
3. A consulta principal acrescenta nomes e e-mails a partir de customer.

Repare que WITH é escrito apenas uma vez, antes da primeira CTE.

Referenciar uma CTE Mais de Uma Vez

Uma CTE pode aparecer várias vezes na consulta principal, por exemplo para juntar um resultado consigo mesmo. Vamos comparar a receita de cada mês com a do mês anterior:

WITH monthly_sales AS (
    SELECT CAST(DATE_FORMAT(payment_date, '%Y-%m-01') AS DATE) AS month_start,
           SUM(amount) AS monthly_total
    FROM payment
    GROUP BY month_start
)
SELECT cur.month_start,
       cur.monthly_total AS current_sales,
       prev.monthly_total AS previous_sales,
       ROUND((cur.monthly_total - prev.monthly_total) / prev.monthly_total * 100, 2) AS percent_change
FROM monthly_sales AS cur
LEFT JOIN monthly_sales AS prev
       ON prev.month_start = cur.month_start - INTERVAL 1 MONTH
ORDER BY cur.month_start;

Resultado: a receita de cada mês ao lado da receita do mês anterior e a variação percentual. Quando o mês anterior não tem pagamentos (maio de 2005, o primeiro mês, e fevereiro de 2006, depois de uma lacuna nos dados), as colunas de comparação são NULL.

monthly_sales é definida uma vez e usada duas vezes, como cur e como prev. Com subconsultas, seria preciso escrever a mesma agregação duas vezes e manter as duas cópias sincronizadas.

Exemplo Prático: o Último Aluguel de Cada Cliente

Uma tarefa comum é encontrar o evento mais recente de cada cliente. Com uma CTE, ela divide-se em duas etapas claras: encontrar a data mais recente e depois acrescentar os detalhes.

WITH last_rental AS (
    SELECT customer_id, MAX(rental_date) AS last_rental_date
    FROM rental
    GROUP BY customer_id
)
SELECT c.customer_id,
       CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
       lr.last_rental_date
FROM customer AS c
LEFT JOIN last_rental AS lr ON lr.customer_id = c.customer_id
ORDER BY lr.last_rental_date
LIMIT 20;

Resultado: os 20 clientes que estão há mais tempo sem alugar nada, com a data do seu último aluguel.

No Módulo 7, você vai ver como as funções de janela resolvem tarefas do tipo "última linha por grupo" quando é preciso a linha inteira, e não só a data.

Exemplo Prático: Análise de Coortes

As CTEs são especialmente úteis em análises de várias etapas. Numa análise de coortes, os clientes são agrupados pelo mês do seu primeiro aluguel, e depois a sua atividade é acompanhada mês a mês:

WITH customer_cohort AS (
    SELECT customer_id,
           CAST(DATE_FORMAT(MIN(rental_date), '%Y-%m-01') AS DATE) AS cohort_month
    FROM rental
    GROUP BY customer_id
),
monthly_activity AS (
    SELECT cc.cohort_month,
           CAST(DATE_FORMAT(r.rental_date, '%Y-%m-01') AS DATE) AS rental_month,
           r.customer_id
    FROM customer_cohort AS cc
    JOIN rental AS r ON r.customer_id = cc.customer_id
)
SELECT cohort_month,
       rental_month,
       COUNT(DISTINCT customer_id) AS active_customers,
       COUNT(*) AS rentals
FROM monthly_activity
GROUP BY cohort_month, rental_month
ORDER BY cohort_month, rental_month;

Resultado: para cada coorte (mês do primeiro aluguel), quantos dos seus clientes alugaram filmes em cada mês seguinte e quantos aluguéis fizeram.

1. customer_cohort atribui cada cliente ao mês do seu primeiro aluguel.
2. monthly_activity anexa a coorte a cada aluguel.
3. A consulta principal conta os clientes ativos e os aluguéis por coorte e mês.

CTEs e Desempenho

Uma CTE é, antes de tudo, uma forma de organizar uma consulta. Na maioria dos bancos de dados modernos, uma CTE simples tem o mesmo desempenho que a subconsulta equivalente, porque o otimizador a incorpora na consulta principal.

Há diferenças entre bancos de dados:

• O PostgreSQL antes da versão 12 sempre materializava uma CTE (calculava-a uma vez num resultado temporário). Desde a versão 12, uma CTE referenciada uma só vez é incorporada; AS MATERIALIZED e AS NOT MATERIALIZED permitem escolher.
• O MySQL (8.0+) e o MariaDB (10.2+) podem fundir uma CTE na consulta principal ou materializá-la. Uma CTE referenciada várias vezes costuma ser calculada uma vez.

Se uma consulta com CTEs estiver lenta, verifique o seu plano de execução com EXPLAIN, como faria com qualquer outra consulta.

Principais conclusões:

• Uma CTE é um resultado temporário com nome, definido com WITH e disponível apenas dentro de uma instrução.
• As CTEs permitem escrever lógica de várias etapas de cima para baixo, com um nome significativo para cada etapa.
• Várias CTEs são separadas por vírgulas, e cada uma pode usar as CTEs definidas antes dela.
• Uma CTE pode ser referenciada várias vezes na consulta principal, sem repetir a mesma subconsulta.
• Para uma única etapa curta, uma subconsulta serve; para lógica de várias etapas, uma CTE costuma ser mais legível.
• O desempenho costuma ser o mesmo das subconsultas, mas as regras de materialização diferem entre bancos de dados.

Perguntas Frequentes

Qual é a diferença entre uma CTE e uma subconsulta?

As duas definem um resultado intermediário dentro de uma consulta. Uma CTE é escrita antes da consulta principal com um nome, pode ser referenciada várias vezes e lê-se de cima para baixo. Uma subconsulta é escrita diretamente onde é usada e tem de ser repetida a cada uso.

Uma CTE é mais rápida do que uma subconsulta?

Normalmente, nenhuma é mais rápida: a maioria dos otimizadores processa da mesma forma uma CTE simples e a subconsulta equivalente. A diferença aparece quando um banco de dados materializa uma CTE, o que pode ajudar quando o resultado é reutilizado e prejudicar quando a consulta principal o filtra muito. Verifique o plano com EXPLAIN.

Uma CTE é o mesmo que uma tabela temporária?

Não. Uma CTE só existe durante uma instrução e não é guardada. Uma tabela temporária é criada explicitamente, dura até o fim da sessão, pode ser usada por muitas consultas e pode ter índices.

Quais bancos de dados suportam a cláusula WITH?

PostgreSQL, SQL Server, Oracle, SQLite (3.8.3+), MySQL (8.0+) e MariaDB (10.2+) suportam CTEs.