O CTE do MySQL e como usá-lo

As expressões comuns de tabela (CTEs) no MySQL proporcionam ainda mais versatilidade a esse popular sistema de gerenciamento de banco de dados. Descubra como usar CTEs para simplificar consultas complexas e implementar recursão.

Todas as linguagens de programação têm maneiras de simplificar os problemas, dividindo-os em partes que podem ser resolvidas individualmente. Os usuários podem unificar os resultados parciais para obter um único resultado final. Graças a algo chamado expressões de tabela comuns (CTEs), o MySQL agora é uma exceção. Um CTE do MySQL permite que você atribua um nome a um conjunto de resultados temporários e, em seguida, faça referência a esse conjunto de resultados pelo nome (como se fosse uma tabela ou uma visualização) nas instruções SELECT, INSERT, UPDATE, ou DELETE.

Os CTEs fazem parte do padrão SQL desde 1999. Entretanto, sua implementação efetiva nos dialetos de cada sistema de gerenciamento de banco de dados relacional (RDBMS) ocorreu gradualmente, à medida que os usuários começaram a considerar esse novo recurso útil. O MySQL foi um dos últimos RDBMSs populares a incorporar o suporte a CTEs. Foi somente na versão 8, lançada em abril de 2018, que o suporte a CTEs apareceu no MySQL.

Como veremos a seguir, um dos principais usos do CTE do MySQL é implementar a recursão. Você pode fazer nosso curso interativo sobre consultas recursivas no MySQL se quiser ir direto para esse assunto. Neste curso, você pode obter todo o conhecimento necessário para dominar as consultas recursivas no MySQL 8. Tudo o que você precisa é de um navegador da Web, uma conexão com a Internet e conhecimento de SQL básico (incluindo as cláusulas JOIN e GROUP BY ). Há 114 exercícios interativos com dicas e ajuda on-line para que você possa realmente aprimorar suas habilidades.

Por enquanto, porém, vamos nos concentrar nos conceitos básicos dos CTEs do MySQL, começando com a sintaxe mais simples.

Sintaxe básica de um CTE do MySQL

A sintaxe para escrever um CTE do MySQL começa com a palavra WITH, seguida pelo nome do CTE e uma lista das colunas que o CTE retornará. Essa lista não é obrigatória, pois pode ser deixada para ser definida pelo resultado da consulta que define o CTE. Essa consulta deve ser escrita entre parênteses após a palavra AS.

A sintaxe básica das expressões de tabela comum no MySQL pode ser resumida da seguinte forma:

WITH cte_name (column_list) AS (
    query
) 
SELECT * 
FROM cte_name;

Após o parêntese que envolve a consulta CTE, temos uma instrução SQL convencional que se refere ao CTE por seu nome, como se fosse uma visualização. A instrução pode ser um comando DML (Data Manipulation Language, Linguagem de Manipulação de Dados), como SELECT, INSERT, DELETE, ou UPDATE.

Se você quiser um guia de referência rápida para a sintaxe ou os comandos do MySQL, marque nossa MySQL Cheat Sheet gratuita. Ela o ajudará a refrescar sua memória sobre frases comuns (e não tão comuns) do MySQL.

Um CTE no MySQL que retorna um único valor

Vamos ver um exemplo de um CTE criado em um banco de dados MySQL. Usaremos informações de vendas e mostraremos a diferença entre o valor de cada venda e o valor médio da venda.

Temos uma tabela sales com informações detalhadas sobre vendas. Para este exemplo, estamos interessados em três colunas: customer_id sale_id e amount. Aqui estão alguns dados de amostra:

Criaremos um CTE chamado sales_average que retornará um único valor: a média de todos os valores da coluna amount da tabela sales da tabela (ou seja, a média geral de todos os valores de venda). No SELECT externo - aquele que invoca o CTE - usaremos o valor retornado pelo CTE para calcular a diferença entre o valor de cada venda e a média geral. Isso nos permitirá identificar as vendas mais significativas:

WITH sales_average AS (
  SELECT AVG(amount) AS amount_avg
  FROM sales
)
SELECT 
  customer_id, 
  sale_id, 
  amount, 
  amount - amount_avg as difference
FROM sales, sales_average;

Quando o MySQL encontra uma consulta como a acima, ele primeiro executa a consulta na cláusula WITH e salva seus resultados temporariamente sob o nome do CTE (sales_average). Em seguida, ele executa o SELECT externo usando sales_average como uma tabela comum; isso produz o conjunto de resultados finais.

A execução de toda a consulta com os dados de amostra detalhados acima retorna os seguintes resultados:

CTE com funções de agrupamento e agregação

Vejamos um exemplo um pouco mais complexo: criaremos uma consulta que usa um CTE para encontrar as chamadas mais antigas em uma central de atendimento. Temos uma tabela chamada calls que inclui caller_id e call_time de cada chamada (entre outros dados, como a duração da chamada, que não incluiremos aqui). Estas são algumas linhas da tabela calls tabela:

Nosso CTE, chamado first_contact, retornará a data e a hora da chamada mais antiga de cada chamador. No site externo SELECT, juntamos o CTE com a tabela calls para retornar todos os dados de cada chamada mais antiga. Neste exemplo (diferentemente do anterior), detalharemos os nomes das colunas retornadas pelo CTE em sua declaração:

WITH first_contact (caller_id, first_call_time) AS (
	SELECT 
          caller_id, 
          MIN(call_time) AS first_call_time
	FROM calls
	GROUP BY caller_id
)
SELECT c.*
FROM calls AS c
INNER JOIN first_contact AS fc 
ON fc.caller_id = c.caller_id 
  AND fc.first_call_time = c.call_time;

Para executar a consulta acima, o MySQL primeiro obterá os resultados da subconsulta com o nome first_contact. Isso armazenará temporariamente todos os valores distintos de caller_id junto com o call_time mais antigo de cada um. Em seguida, o site externo SELECT juntará os resultados temporários da subconsulta anterior com a tabela calls produzindo todos os dados da chamada mais antiga de cada ID de chamador.

Esses são os resultados da consulta:

Se estiver aprendendo sobre os CTEs do MySQL para se candidatar a uma entrevista de emprego, leia esta compilação de perguntas de entrevista sobre CTEs do SQL para ver o que terá de enfrentar.

CTEs múltiplos e aninhados

No MySQL, uma única instrução SQL pode conter vários CTEs. Independentemente de quantos CTEs você definir em uma consulta, a palavra WITH é usada apenas uma vez no início da instrução; as definições de CTE são separadas por vírgulas.

Uso de vários CTEs em uma única consulta

No exemplo a seguir, temos uma tabela chamada customers que armazena a área em que cada cliente está localizado:

Suponha que estejamos interessados em usar as informações dessa tabela em conjunto com a tabela sales tabela. Especificamente, queremos obter os totais de vendas dos clientes que pertencem às áreas "WEST" e "EAST".

Para isso, definiremos dois CTEs que nos permitirão filtrar somente os clientes das áreas em que estamos interessados. Em seguida, na declaração externa SELECT, combinaremos os dados dos dois CTEs com a tabela sales para obter as médias de vendas de cada uma dessas áreas:

WITH customers_west AS (
	SELECT *
	FROM customers
	WHERE area = 'WEST'),
customers_east AS (
	SELECT *
	FROM customers
	WHERE area = 'EAST')
SELECT 
   cw.area, 
   AVG(sw.amount) AS amount_avg
FROM customers_west AS cw
INNER JOIN sales sw 
ON sw.customer_id = cw.customer_id

UNION

SELECT 
  ce.area, 
  AVG(se.amount)
FROM customers_east AS ce
INNER JOIN sales se 
ON se.customer_id = ce.customer_id;

E aqui está o resultado:

Aninhamento de CTEs

O MySQL também nos permite aninhar CTEs para que um CTE possa fazer referência a um CTE definido anteriormente - tudo em uma única instrução SQL.

A técnica de aninhamento de CTEs nos permite dividir consultas grandes e complexas em subconsultas menores e mais gerenciáveis. Isso nos permite abordar gradualmente a solução de um problema, reduzindo a etapa final a um simples SELECT.

No exemplo a seguir, usamos dois CTEs aninhados para obter o total de vendas agrupadas por país e região. A tabela sales A tabela inclui uma coluna de país que indica o país onde cada venda foi feita.

A tabela countries tabela inclui uma coluna de região que indica a região geográfica à qual cada país pertence:

Para obter os totais de vendas por região, usamos dois CTEs que executam uma abordagem passo a passo para a solução final:

WITH sales_by_country AS (
SELECT 
  country, 
  SUM(amount) AS total_sales_by_country
FROM sales
group BY country
),
sales_by_region AS (
SELECT 
  c.region, 
  SUM(s.total_sales_by_country) AS total_sales_by_region
FROM sales_by_country s
INNER JOIN countries c 
ON c.country = s.country
GROUP BY c.region
)
SELECT * 
FROM sales_by_region;

Na consulta acima, primeiro definimos um CTE chamado sales_by_country que agrupa os dados por país e retorna o total de vendas de cada país. Em seguida, definimos um segundo CTE chamado sales_by_region que une o CTE sales_by_country à tabela de países e agrupa os totais de vendas por região. Por fim, toda a consulta é resolvida com um simples SELECT do CTE sales_by_region.

Uso de CTEs do MySQL com outros comandos DML

Os exemplos anteriores usaram CTEs para resolver consultas SELECT complexas, reduzindo sua complexidade para, por fim, resolvê-las com um simples SELECT. Mas os CTEs também podem ser usados com outros comandos. Nesta seção, veremos como usar CTEs nos comandos INSERT e UPDATE.

Uso de um CTE com INSERT

Suponhamos que tenhamos uma tabela employees com as colunas empl_id (INT), empl_name (VARCHAR) e salary (DECIMAL). Em seguida, temos uma tabela applicants com as mesmas colunas que employees mais a coluna aptitude (TINYINT) que indica se um candidato foi aprovado no teste de aptidão (aptitude = 1) ou não (aptitude = 0). Somente os candidatos aprovados no teste estão qualificados para se tornarem funcionários.

Periodicamente, as linhas da tabela applicants que têm o valor 1 na coluna aptitude devem ser inseridas na tabela. employees tabela. Para isso, usaremos um CTE chamado qualified_applicants para filtrar os candidatos aprovados no teste de aptidão, mas que ainda não se tornaram funcionários. Os dados retornados pelo CTE qualified_applicants são os dados de origem para o INSERT que os incorpora à tabela. employees tabela.

INSERT INTO employees (empl_id, empl_name, salary)
WITH qualified_applicants (empl_id, empl_name, salary) AS (
SELECT 
    empl_id, 
    empl_name, 
    salary
FROM applicants AS a
WHERE a.aptitude = 1
AND NOT EXISTS 
       (SELECT * 
        FROM employees AS e 
        WHERE e.empl_id = a.empl_id)
)
SELECT 
  empl_id, 
  empl_name, 
  salary
FROM qualified_applicants;

Quando um CTE é usado em combinação com um INSERT, todo o código do CTE, desde a cláusula WITH até o SELECT externo, é escrito após a linha INSERT INTO table (column1, column2, ...) linha. Para realizar essa inserção, o MySQL primeiro executará toda a instrução CTE e depois inserirá os resultados na tabela especificada.

O bom da instrução acima é que ela pode ser executada repetidamente sem medo de criar dados duplicados ou violar uma chave primária. Isso ocorre porque a condição WHERE na definição do CTE inclui uma cláusula que impede que o INSERT tente reinserir dados que já estejam na tabela employees tabela.

Uso de um CTE com UPDATE

Assim como usamos um CTE do MySQL para inserir linhas em uma tabela, também podemos usar um CTE para atualizar a tabela com novas informações. No exemplo a seguir, veremos como usar um CTE em conjunto com um comando UPDATE.

Usaremos a mesma employees tabela e uma nova tabela salaries tabela com as colunas empl_id (INT) e salary (DECIMAL). Essa tabela armazena os salários atualizados de cada funcionário. O CTE retornará as linhas de salaries nas quais o salário do funcionário é maior do que na tabela employees tabela para o mesmo funcionário.

Aqui está a consulta completa:

WITH raised_salaries (empl_id, salary) AS (
	SELECT s.empl_id, s.salary
	FROM salaries s
	INNER JOIN employees e 
      ON e.empl_id = s.empl_id
	WHERE s.salary > e.salary
)
UPDATE employees e
INNER JOIN raised_salaries rs 
ON rs.empl_id = e.empl_id
SET e.salary = rs.salary;

A sintaxe necessária para atualizar os dados de um CTE segue a forma geral do comando MySQL UPDATE JOIN. Quando usado junto com um CTE, o comando UPDATE JOIN deve ser usado como um substituto para o SELECT externo do CTE. O nome do CTE é colocado na cláusula JOIN para combiná-lo com a tabela a ser atualizada.

Obviamente, não é possível atualizar os campos do CTE - você só pode atualizar os campos da(s) tabela(s) unidas ao CTE.

Como a instrução INSERT que usamos anteriormente, essa combinação de CTE do MySQL e instrução UPDATE pode ser executada repetidamente sem alterar uma única letra. Cada vez que for executada, ela atualizará somente as linhas de employees com um salário menor do que o declarado na tabela salaries tabela.

CTEs recursivos no MySQL

Além de simplificar e esclarecer consultas complexas, os CTEs possibilitam a implementação de recursão no MySQL. Ao poder resolver a recursão usando apenas instruções SQL, evitamos a necessidade de empregar outras linguagens de programação. Isso nos permite escrever consultas mais eficientes.

Os CTEs recursivos são compostos pelos seguintes elementos:

• Um membro âncora que fornece o(s) valor(es) inicial(is) para a sequência recursiva.
• Um membro recursivo que obtém os resultados das iterações anteriores e adiciona novas informações a eles. O(s) membro(s) âncora e o(s) membro(s) recursivo(s) são combinados pelas cláusulas UNION.
• Uma condição de término, que é uma cláusula WHERE (anexada ao membro recursivo) que define a condição que determinará o fim do ciclo recursivo.
• Uma invocação, ou o SELECT externo que se refere ao CTE recursivo pelo nome (como qualquer outro CTE).

Você encontrará informações mais detalhadas neste artigo sobre CTEs recursivos.

Um uso típico de funções recursivas na programação é a geração de números de Fibonacci até um determinado valor. Os números de Fibonacci são usados em áreas tão variadas quanto o desenvolvimento de algoritmos de pesquisa e a simulação do crescimento populacional. No seguinte CTE recursivo do MySQL, obtemos os 10 primeiros números da sequência de Fibonacci:

WITH RECURSIVE cte_fib AS (
SELECT 
  1 counter, 
  CAST(0 AS decimal) fibo, 
  CAST(0 AS decimal) prev

UNION ALL

SELECT 2, 1, 0

UNION ALL

SELECT 
  counter + 1, 
  fibo + prev, 
  fibo
FROM cte_fib
WHERE counter < 10
AND fibo > 0
)
SELECT counter, fibo
FROM cte_fib
ORDER BY counter;

No exemplo acima, o membro âncora é formado pelos dois primeiros membros do UNION, porque eles fornecem os elementos iniciais da série de Fibonacci (as duas primeiras linhas nos dados resultantes):

SELECT 
  1 counter, 
  CAST(0 AS decimal) fibo, 
  CAST(0 AS decimal) prev

UNION ALL

SELECT 2, 1, 0

O membro recursivo é a terceira parte do UNION, porque adiciona uma linha aos dados retornados pela iteração anterior do mesmo CTE:

SELECT 
  counter + 1, 
  fibo + prev, 
  fibo
FROM cte_fib
WHERE counter < 10
AND fibo > 0

E a condição de término é a cláusula WHERE entre parênteses:

WHERE counter < 10
AND fibo > 0

Ela afirma que a recursão deve parar quando o contador atingir o valor de 10. E a invocação é o SELECT fora dos parênteses, que retorna o contador e o número de Fibonacci para cada linha retornada pelo CTE.

Há muitos outros usos de CTEs recursivos no MySQL. Por exemplo, eles podem ser usados para percorrer tabelas com informações ordenadas hierarquicamente (por exemplo, organogramas) ou estruturas de dados de árvores ou gráficos. Eles podem até ser usados para tarefas SQL não convencionais, como desenhar uma árvore de Natal com os resultados de uma consulta.

CTEs do MySQL em poucas palavras

Ao longo deste artigo, fizemos um tour pelos CTEs no MySQL. Aprendemos que as expressões de tabela comuns:

• Simplificam consultas complexas, dividindo-as e organizando-as em partes menores e mais fáceis de entender. Isso também melhora a legibilidade das consultas.
• Nos ajudam a escrever códigos mais eficientes. Uma subconsulta definida como um CTE pode ser reutilizada em diferentes partes da mesma consulta - até mesmo em outras subconsultas - sem a necessidade de repeti-la.
• Encapsulam a lógica sem criar objetos desnecessários. Os CTEs encapsulam a lógica das subconsultas em um nome, assim como fazem as exibições. Mas, diferentemente das exibições, eles não implicam a criação de objetos permanentes no banco de dados.
• Implementar recursão. Esse é um dos principais motivos da popularidade dos CTEs, pois sem eles é bastante complicado implementar recursão no MySQL.

Até agora, você deve ter uma ideia do que pode ser feito com CTEs recursivos no MySQL. Depois de ler este artigo, você estará pronto para solidificar seu conhecimento por meio de alguns exercícios práticos sobre CTEs.

Mas o que você viu neste artigo é apenas a ponta do iceberg. Para descobrir todo o potencial dos CTEs no MySQL, considere fazer o curso LearnSQL.com.br Consultas Recursivas no MySQL. Com seus 114 exercícios interativos, esse curso fornecerá todo o conhecimento necessário para dominar as consultas recursivas no MySQL 8. Você nem precisa ter acesso a um servidor MySQL; o ambiente do curso fornecerá todas as ferramentas necessárias. Tudo o que você precisa é de um navegador da Web, uma conexão com a Internet e conhecimento de SQL básico. Você pode até fazer o teste introdutório gratuitamente para ter certeza de que o curso atende às suas necessidades!

Concluindo, os CTEs são uma ferramenta poderosa no MySQL, especialmente quando a organização e a legibilidade do código SQL são valorizadas. Em equipes multidisciplinares, onde engenheiros de banco de dados SQL experientes colaboram com analistas de dados, designers de banco de dados e DBAs, os CTEs são um recurso valioso para aumentar a produtividade da equipe e obter um software mais sustentável e durável.