Cláusula WITH do MySQL (CTE) Explicada: Sintaxe, Exemplos, Consultas Recursivas e Dicas de Performance

1. Introdução

MySQL é um sistema de gerenciamento de banco de dados amplamente usado por desenvolvedores e administradores de banco de dados, oferecendo funcionalidade SQL poderosa e flexível. Entre seus recursos, a cláusula WITH (Expressão de Tabela Comum: CTE), introduzida no MySQL 8.0, é uma ferramenta robusta que torna as consultas SQL mais legíveis e melhora a manutenibilidade.

Este artigo explica a cláusula WITH desde o básico até o uso avançado, direcionado a usuários iniciantes e intermediários. Em particular, abordamos tópicos práticos como substituição de subconsultas e implementação de consultas recursivas.

Para quem está aprendendo SQL ou tem dificuldade em otimizar consultas no dia a dia, este artigo tem como objetivo fornecer soluções concretas. Siga o conteúdo abaixo para entender os fundamentos da cláusula WITH e aplicá‑la em cenários reais.

2. Conceitos Básicos da Cláusula WITH (Expressões de Tabela Comum)

O que é a Cláusula WITH?

A cláusula WITH é uma sintaxe usada para definir um conjunto de resultados temporário (Expressão de Tabela Comum, CTE) dentro de uma consulta SQL e referenciá‑lo em consultas subsequentes. Compatível a partir do MySQL 8.0, permite que subconsultas complexas sejam reescritas de forma mais clara e concisa.

Por exemplo, ao escrever subconsultas diretamente, a legibilidade pode diminuir e a consulta geral pode ficar extensa. Ao usar a cláusula WITH, você pode dividir as consultas em blocos lógicos, facilitando a compreensão.

Sintaxe Básica da Cláusula WITH

Abaixo está a sintaxe básica da cláusula WITH:

WITH table_name AS (
  SELECT column1, column2
  FROM original_table
  WHERE condition
)
SELECT column1, column2
FROM table_name;

Nessa sintaxe, uma tabela virtual (Expressão de Tabela Comum) é definida após WITH e, em seguida, utilizada na consulta principal. Isso torna subconsultas usadas repetidamente mais fáceis de expressar de forma concisa.

Diferenças em Relação a Subconsultas e Views

A cláusula WITH cria um conjunto de resultados temporariamente disponível e difere de subconsultas e views em vários aspectos.

A cláusula WITH é mais legível que subconsultas e é ideal quando você não precisa criar um objeto permanente como uma view.

Benefícios de Usar a Cláusula WITH

1. Melhoria da Legibilidade da Consulta – Mesmo quando há múltiplas subconsultas, organizá‑las com a cláusula WITH esclarece a estrutura.
2. Maior Reusabilidade – Ao definir um conjunto de resultados temporário, ele pode ser referenciado várias vezes dentro da mesma consulta.
3. Facilidade de Manutenção – Como as consultas podem ser divididas logicamente, modificações e extensões tornam‑se mais simples.

3. Uso Básico da Cláusula WITH no MySQL

Substituindo Subconsultas

A cláusula WITH é uma ferramenta poderosa para simplificar subconsultas complexas. Incorporar subconsultas diretamente pode tornar a consulta inteira complicada e difícil de ler, mas usar a cláusula WITH melhora a legibilidade.

Abaixo está um exemplo básico de substituição de uma subconsulta usando a cláusula WITH.

Usando uma Subconsulta:

SELECT AVG(sales.total) AS average_sales
FROM (
  SELECT SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
) AS sales;

Usando a Cláusula WITH:

WITH sales AS (
  SELECT SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
)
SELECT AVG(sales.total) AS average_sales
FROM sales;

Neste exemplo, um conjunto de resultados temporário chamado sales é definido usando a cláusula WITH e, em seguida, referenciado na consulta principal. Isso torna a consulta inteira mais fácil de entender e melhor organizada.

Definindo Múltiplas Expressões de Tabela Comum (CTEs)

A cláusula WITH permite definir múltiplas CTEs. Isso possibilita uma modularização ainda maior de consultas complexas.

Exemplo:

WITH 
  sales_per_customer AS (
    SELECT customer_id, SUM(amount) AS total_sales
    FROM orders
    GROUP BY customer_id
  ),
  high_value_customers AS (
    SELECT customer_id
    FROM sales_per_customer
    WHERE total_sales > 10000
  )
SELECT customer_id
FROM high_value_customers;

Neste exemplo, sales_per_customer calcula o total de vendas por cliente e, com base nesse resultado, high_value_customers extrai os clientes com altos valores de compra. Ao usar múltiplas CTEs sequencialmente, as consultas podem ser construídas passo a passo.

Usando CTEs Aninhados

Ao usar CTEs aninhados, você pode realizar operações de dados mais complexas.

Exemplo:

WITH 
  sales_data AS (
    SELECT product_id, SUM(amount) AS total_sales
    FROM orders
    GROUP BY product_id
  ),
  ranked_sales AS (
    SELECT product_id, total_sales,
           RANK() OVER (ORDER BY total_sales DESC) AS rank
    FROM sales_data
  )
SELECT product_id, total_sales
FROM ranked_sales
WHERE rank <= 5;

Nesta consulta, sales_data agrega as vendas por produto, e ranked_sales atribui classificações com base no volume de vendas. Finalmente, os cinco principais produtos são extraídos.

Pontos Principais para Uso Prático

1. Pense em Etapas Lógicas Construa CTEs passo a passo para melhorar a legibilidade e facilitar a depuração.
2. Armazene Resultados de Cálculos Intermediários Agrupe resultados de cálculos ou condições de filtragem usadas múltiplas vezes em CTEs para reduzir a duplicação de código.
3. Tenha Cuidado com Conjuntos de Dados Grandes Como as CTEs geram conjuntos de resultados temporários, considere o impacto de desempenho ao trabalhar com grandes volumes de dados.

4. Exemplos Práticos de Cláusulas WITH Recursivas

O que é uma Cláusula WITH Recursiva?

Uma cláusula WITH recursiva (CTE recursiva) é um método que usa uma Common Table Expression para executar repetidamente uma consulta autorreferente, permitindo processar dados hierárquicos e cálculos iterativos. CTEs recursivas são suportadas no MySQL 8.0 e posteriores e são especialmente úteis ao trabalhar com relações pai‑filho e estruturas hierárquicas.

Sintaxe Básica de uma CTE Recursiva

Para definir uma CTE recursiva, use a palavra‑chave WITH RECURSIVE. A sintaxe básica é a seguinte:

WITH RECURSIVE recursive_table_name AS (
  initial_query -- starting point of the recursion
  UNION ALL
  recursive_query -- query called recursively
)
SELECT * FROM recursive_table_name;

• Consulta inicial : Recupera o primeiro conjunto de dados para o processo recursivo.
• Consulta recursiva : Gera novas linhas com base na consulta inicial ou nos resultados da iteração anterior.
• UNION ALL : Combina os resultados da consulta inicial e da consulta recursiva.

Exemplo: Processamento de Dados Hierárquicos

CTEs recursivas são frequentemente usadas para expandir dados com uma estrutura hierárquica (por exemplo, árvores organizacionais ou árvores de categorias).

Exemplo: Expansão de uma hierarquia de gerenciamento de funcionários

Considere a seguinte tabela employees:

Usando esses dados, podemos criar uma consulta que recupera a hierarquia completa a partir de um determinado funcionário.

WITH RECURSIVE employee_hierarchy AS (
  -- Initial query: get top-level employees
  SELECT employee_id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive query: get direct reports
  SELECT e.employee_id, e.name, e.manager_id, eh.level + 1
  FROM employees e
  INNER JOIN employee_hierarchy eh
  ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

Resultado:

Nesta consulta, os subordinados são pesquisados recursivamente com base em manager_id, expandindo a hierarquia completa.

Limitações e Precauções para CTEs Recursivas

1. É Necessária uma Condição de Término Se a consulta recursiva não atender a uma condição de término, pode ocorrer um loop infinito. Sempre inclua condições adequadas para evitar recursão infinita.
2. Impacto de Desempenho CTEs recursivas podem envolver muitos cálculos em grandes conjuntos de dados, o que pode aumentar o tempo de execução. Use cláusulas LIMIT e condições de filtragem para melhorar a eficiência.
3. Limites de Profundidade de Recursão O MySQL tem um limite de profundidade de recursão, portanto tenha cuidado ao executar processamentos recursivos muito profundos. Esse limite pode ser configurado com o parâmetro max_recursive_iterations.

Cenários em que CTEs Recursivas são Úteis

• Percorrendo estruturas de pastas : Pesquisar recursivamente pastas e subpastas.
• Construindo organogramas : Visualizar hierarquias de gerentes a subordinados.
• Exibindo árvores de categorias : Recuperar categorias de produtos hierárquicas ou estruturas de tags.

CTEs recursivas são uma forma poderosa de escrever consultas SQL concisas para esses cenários, ao mesmo tempo que melhoram a legibilidade.

5. Notas e Considerações ao Usar a Cláusula WITH

Impacto de Performance e Otimização

1. Recálculo de CTE Em geral, uma CTE definida com a cláusula WITH é recalculada cada vez que é referenciada. Portanto, usar a mesma CTE várias vezes pode aumentar o tempo de execução da consulta. Exemplo:

   WITH sales AS (
     SELECT product_id, SUM(amount) AS total_sales
     FROM orders
     GROUP BY product_id
   )
   SELECT * FROM sales WHERE total_sales > 1000;
   SELECT COUNT(*) FROM sales;
   

No caso acima, sales é referenciada duas vezes, portanto é computada duas vezes. Para evitar isso, quando precisar referenciar resultados múltiplas vezes, armazenar o resultado em uma tabela temporária pode ser eficaz.

Solução:

CREATE TEMPORARY TABLE temp_sales AS
SELECT product_id, SUM(amount) AS total_sales
FROM orders
GROUP BY product_id;

SELECT * FROM temp_sales WHERE total_sales > 1000;
SELECT COUNT(*) FROM temp_sales;

2. Dividindo CTEs Complexas Se a cláusula WITH ficar muito aninhada, a consulta geral pode se tornar complexa e mais difícil de depurar. É importante dividir a lógica adequadamente para que o processamento dentro de uma única CTE não se torne excessivamente complicado.

Usando Cláusulas WITH em Conjuntos de Dados Grandes

A cláusula WITH gera conjuntos de dados temporários durante a execução. Ao lidar com grandes volumes de dados, isso pode sobrecarregar a memória ou o armazenamento.

Contramedidas:

• Filtrar Dados com Cláusulas WHERE Reduzir a computação filtrando dados desnecessários dentro da CTE.

  WITH filtered_orders AS (
    SELECT *
    FROM orders
    WHERE order_date > '2023-01-01'
  )
  SELECT customer_id, SUM(amount)
  FROM filtered_orders
  GROUP BY customer_id;
  

• Usar Cláusulas LIMIT Se o conjunto de dados for grande, use LIMIT para extrair apenas os dados necessários.

Compatibilidade de Versão do MySQL

A cláusula WITH no MySQL é suportada a partir do MySQL 8.0. Como versões anteriores não suportam a cláusula WITH, é necessário considerar alternativas.

Alternativas:

• Usar Subconsultas Use subconsultas diretamente em vez da cláusula WITH.

  SELECT AVG(total_sales)
  FROM (
    SELECT customer_id, SUM(amount) AS total_sales
    FROM orders
    GROUP BY customer_id
  ) AS sales;
  

• Criar Views Se precisar de consultas reutilizáveis, usar uma view pode ser eficaz.

  CREATE VIEW sales_view AS
  SELECT customer_id, SUM(amount) AS total_sales
  FROM orders
  GROUP BY customer_id;
  
  SELECT AVG(total_sales) FROM sales_view;
  

Como Usar a Cláusula WITH Corretamente

1. Priorizar Legibilidade O objetivo da cláusula WITH é organizar consultas e melhorar a legibilidade. O uso excessivo pode tornar as consultas mais complexas, portanto use-a apenas quando necessário.
2. Verificar Performance Verifique o plano de execução (o comando EXPLAIN) e considere como otimizar a performance.

   EXPLAIN
   WITH sales AS (
     SELECT product_id, SUM(amount) AS total_sales
     FROM orders
     GROUP BY product_id
   )
   SELECT * FROM sales WHERE total_sales > 1000;
   

6. Casos de Uso Práticos em Cenários Reais

Agregando Dados de Vendas

Aqui está um exemplo de agregação de dados de vendas por mês e, em seguida, usando esse resultado para calcular a média mensal de vendas.

Exemplo: Agregando Vendas Mensais e Calculando a Média

WITH monthly_sales AS (
  SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS sales_month,
    SUM(amount) AS total_sales
  FROM orders
  GROUP BY sales_month
)
SELECT 
  sales_month, 
  total_sales,
  AVG(total_sales) OVER () AS average_sales
FROM monthly_sales;

Nesta consulta, monthly_sales calcula as vendas por mês e, com base nesse resultado, a média geral de vendas é calculada. Isso permite que os dados sejam organizados de forma clara e simplifica a análise.

Filtrando Dados com Base em Condições Específicas

Ao separar a lógica de filtragem complexa em uma cláusula WITH, a legibilidade pode ser aprimorada.

Exemplo: Criando uma Lista de Clientes de Alto Gasto

WITH customer_totals AS (
  SELECT 
    customer_id, 
    SUM(amount) AS total_spent
  FROM orders
  GROUP BY customer_id
)
SELECT 
  customer_id, 
  total_spent
FROM customer_totals
WHERE total_spent > 100000;

Nesta consulta, customer_totals calcula o valor total de compras por cliente, e os clientes que atendem à condição especificada são extraídos.

Analisando Dados Hierárquicos

Ao analisar dados hierárquicos, como estruturas organizacionais ou categorias, cláusulas WITH recursivas são extremamente úteis.

Exemplo: Recuperando uma Lista de Subordinados Diretos e Indiretos

WITH RECURSIVE employee_hierarchy AS (
  SELECT 
    employee_id, 
    name, 
    manager_id, 
    1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT 
    e.employee_id, 
    e.name, 
    e.manager_id, 
    eh.level + 1
  FROM employees e
  INNER JOIN employee_hierarchy eh
  ON e.manager_id = eh.employee_id
)
SELECT 
  employee_id, 
  name, 
  manager_id, 
  level
FROM employee_hierarchy
ORDER BY level, manager_id;

Esta consulta constrói dados hierárquicos em employee_hierarchy e recupera funcionários agrupados por nível. Ela permite a geração dinâmica de informações semelhantes a organogramas.

Análise Avançada Usando Múltiplas CTEs

Ao aproveitar múltiplas cláusulas WITH, os dados podem ser processados passo a passo, tornando a análise complexa mais simples.

Exemplo: Extraindo os Produtos Mais Vendidos por Categoria

WITH category_sales AS (
  SELECT 
    category_id, 
    product_id, 
    SUM(amount) AS total_sales
  FROM orders
  GROUP BY category_id, product_id
),
ranked_sales AS (
  SELECT 
    category_id, 
    product_id, 
    total_sales,
    RANK() OVER (PARTITION BY category_id ORDER BY total_sales DESC) AS rank
  FROM category_sales
)
SELECT 
  category_id, 
  product_id, 
  total_sales
FROM ranked_sales
WHERE rank <= 3;

Nesta consulta, as vendas por categoria são calculadas e os três principais produtos dentro de cada categoria são extraídos. Essa abordagem é eficaz ao refinar os dados com base em condições específicas.

Pontos-Chave para Aplicação Prática

1. Desenhar Consultas em Etapas Lógicas Use a cláusula WITH para dividir consultas e processar os dados passo a passo, mantendo a legibilidade.

2. Extrair Apenas os Dados Necessários Use cláusulas WHERE e LIMIT para evitar processar dados desnecessários e projetar consultas eficientes.

3. Aplicações Empresariais Flexíveis A cláusula WITH pode ser aplicada de forma flexível à análise de vendas, segmentação de clientes, gerenciamento de inventário e mais.

7. FAQ (Perguntas Frequentes)

Q1: Quando devo usar a cláusula WITH?

A1:
A cláusula WITH é especialmente eficaz nos seguintes cenários:

• Quando você deseja simplificar subconsultas complexas.
• Quando você precisa reutilizar o mesmo conjunto de dados várias vezes dentro de uma consulta.
• Quando você deseja dividir logicamente uma consulta para melhorar a legibilidade.

Por exemplo, em consultas que utilizam os mesmos resultados de agregação várias vezes, a cláusula WITH permite uma organização mais eficiente.

Q2: Quando uma CTE recursiva é útil?

A2:
CTEs recursivas são úteis ao lidar com estruturas hierárquicas ou cálculos iterativos. Especificamente:

• Processamento de dados hierárquicos (por exemplo, árvores organizacionais, estruturas de categorias).
• Exibição de hierarquias de pastas ou arquivos.
• Cálculos sequenciais de números ou períodos de tempo (por exemplo, calcular a sequência de Fibonacci).

Usar CTEs recursivas facilita a expansão e o processamento de dados autorreferentes.

Q3: As consultas que utilizam a cláusula WITH são mais eficientes que as views?

A3:
Depende do caso de uso.

• Cláusula WITH : Cria um conjunto de resultados temporário usado apenas dentro da consulta. Adequado para dados que não precisam ser reutilizados com frequência.
• View : Armazenada permanentemente no banco de dados e reutilizável por outras consultas. Adequada para consultas que são usadas repetidamente.

Escolher o método apropriado dependendo do cenário é importante.

Q4: O que causa degradação de desempenho ao usar a cláusula WITH?

A4:
As principais causas de degradação de desempenho ao usar a cláusula WITH incluem:

• Recálculo da CTE : Os resultados são recalculados a cada vez que são referenciados, aumentando o tempo de processamento.
• Manipulação de grandes conjuntos de dados : Gerar grandes conjuntos de dados dentro da CTE aumenta o uso de memória e reduz o desempenho.
• Falta de indexação adequada : Se as consultas dentro da CTE não utilizarem índices apropriados, o desempenho pode ser reduzido.

Contramedidas:

• Considere tabelas temporárias ou views se a frequência de reutilização for alta.
• Use cláusulas WHERE e LIMIT para restringir adequadamente os dados.

Q5: Quais alternativas existem para versões do MySQL que não suportam a cláusula WITH?

A5:
Em versões anteriores ao MySQL 8.0, a cláusula WITH não é suportada, portanto use as seguintes alternativas:

• Usar Subconsultas Use subconsultas diretamente em vez da cláusula WITH.

  SELECT AVG(total_sales)
  FROM (
    SELECT customer_id, SUM(amount) AS total_sales
    FROM orders
    GROUP BY customer_id
  ) AS sales;
  

• Usar Tabelas Temporárias Armazene conjuntos de dados reutilizáveis em uma tabela temporária.

  CREATE TEMPORARY TABLE temp_sales AS
  SELECT customer_id, SUM(amount) AS total_sales
  FROM orders
  GROUP BY customer_id;
  
  SELECT AVG(total_sales) FROM temp_sales;
  

Q6: Quais são as melhores práticas ao usar a cláusula WITH?

A6:
Mantenha as seguintes boas práticas em mente:

• Priorizar a simplicidade : Não force lógica complexa em uma única cláusula WITH. Divida-a adequadamente.
• Verificar o desempenho : Verifique o plano de execução usando o comando EXPLAIN e otimize conforme necessário.
• Considerar a reutilização : Se a frequência de reutilização for alta, aproveite views ou tabelas temporárias.

8. Conclusão

Este artigo abordou a cláusula WITH (Common Table Expression, CTE) introduzida no MySQL 8.0, desde os fundamentos até aplicações avançadas. A cláusula WITH é um recurso extremamente útil para tornar consultas complexas concisas e legíveis. A seguir, os principais pontos.

Principais Benefícios da Cláusula WITH

1. Melhoria na Legibilidade das Consultas Organiza subconsultas complexas, aprimorando a legibilidade e a manutenção do código SQL.
2. Reutilização de Consultas Processa dados de forma eficiente ao referenciar o mesmo conjunto de dados várias vezes.
3. Permite Operações de Dados Recursivas CTEs recursivas simplificam o processamento de dados hierárquicos e cálculos iterativos.

Pontos Práticos de Uso

• Útil para análise de vendas e dados de clientes, permitindo agregação passo a passo.
• CTEs recursivas são eficazes para processamento de dados hierárquicos (como organogramas ou estruturas de categorias).
• Combinar cláusulas WITH com views ou tabelas temporárias permite operações de banco de dados flexíveis e eficientes.

Considerações Importantes

• A cláusula WITH é poderosa, mas o uso inadequado pode reduzir o desempenho.
• Avalie a reutilização e o desempenho caso a caso, e escolha entre views ou tabelas temporárias quando apropriado.
• Sempre verifique a eficiência da consulta usando o plano de execução (comando EXPLAIN).

Próximos Passos

Ao usar a cláusula WITH, você pode criar consultas SQL mais eficientes e fáceis de manter. Experimente aplicá‑la em seus projetos reais seguindo estas etapas:

1. Comece com consultas simples e pratique estruturá‑las usando a cláusula WITH.
2. Desafie‑se com CTEs recursivas para dados hierárquicos e cenários complexos.
3. Foque na otimização de desempenho para aprimorar ainda mais suas habilidades em SQL.

Isso conclui o artigo. Use seu conhecimento da cláusula WITH do MySQL em seu trabalho diário e estudos.