CTEs do SQL: Uma visão geral completa das expressões comuns de tabela

Este artigo fornece um guia detalhado de tudo o que você precisa saber sobre expressões comuns de tabela (CTEs), desde a definição básica até as consultas recursivas mais complexas. Se o banco de dados que você usa suporta CTEs, aqui está tudo o que você precisa para tirar o máximo proveito dele.

As expressões de tabela comum - também chamadas de CTEs, cláusulas WITH ou consultas recursivas (embora esse último nome seja, na verdade, uma aplicação específica) - são um recurso relativamente novo do SQL. Seu objetivo é simplificar consultas complexas, tornando-as mais fáceis de ler, entender e manter. Talvez você queira começar lendo sobre o que é uma expressão de tabela comum para ter uma visão completa. Depois, aperte o cinto e prepare-se, pois este artigo o conduzirá por todos os diferentes recursos de CTE (cursos, exercícios, exemplos e explicações) disponíveis em nosso LearnSQL.com.br site.

Os CTEs são úteis para dividir consultas grandes e complexas em partes menores e mais compreensíveis, assim como as exibições, subconsultas e tabelas temporárias. E, assim como visualizações, subconsultas e tabelas temporárias, você pode combinar CTEs para chegar a um resultado final. No entanto, a vantagem de um CTE é que ele não gera objetos que persistem ou ocupam espaço no banco de dados (como fazem as exibições e as tabelas temporárias). Além disso, eles são mais fáceis de ler e interpretar do que as subconsultas.

Se você fizer nosso Consultas Recursivas curso, você terá uma visão geral completa dos CTEs do SQL. Nele, você aprenderá a sintaxe do CTE e as diferentes maneiras de usá-lo. Você terá mais de 100 exercícios interativos que podem ser executados livremente em nosso banco de dados on-line. Tudo o que você precisa é ter um conhecimento básico de SQL e um navegador com conexão à Internet.

Sintaxe de CTE

A forma geral de um CTE SQL começa com uma cláusula WITH seguida de uma definição de consulta (apenas uma instrução SELECT normal) à qual é atribuído um nome. Após essa definição, há uma instrução SELECT que se refere à consulta CTE pelo nome atribuído a ela, como se fosse uma tabela ou uma exibição. Por exemplo:

WITH cte_name AS (cte_query_definition)
SELECT *
FROM cte_name;

A cláusula WITH encabeça a consulta e contém uma subconsulta à qual é atribuído um nome. A consulta principal (localizada após a cláusula WITH ) é uma instrução SELECT normal que pode usar a subconsulta nomeada quantas vezes forem necessárias.

Quando você executa uma consulta que contém CTEs, o mecanismo de banco de dados executa primeiro a(s) consulta(s) CTE, salvando os resultados da consulta CTE até que termine de executar a consulta inteira; os resultados CTE podem ser considerados resultados intermediários. Ele usa esses resultados intermediários como tabelas para compor o resultado final. Em seguida, ele retorna o resultado final e descarta os resultados intermediários gerados anteriormente.

Um exemplo básico de CTE

Vamos ver um exemplo simples. Você tem um esquema com três tabelas: employee, division, e payment. Você precisa obter uma lista dos funcionários com seu pagamento máximo e a divisão à qual cada funcionário pertence. Você pode criar um CTE que primeiro resolva o pagamento máximo de cada funcionário em uma subconsulta chamada max_paid. Em seguida, no site principal SELECT, você une max_paid com employee e division para obter o resultado final:

WITH max_paid (employee_id, max_payment) AS (
	SELECT
		emp.employee_id,
		MAX(pay.payment) AS max_payment
	FROM
		employee AS emp
INNER JOIN payment AS pay ON
pay.employee_id = emp.employee_id
	GROUP BY
		emp.employee_id
	)
SELECT
	emp.employee_id,
	emp.name AS employee_name,
	div.name AS division_name,
	mp.max_payment
FROM max_paid mp
	INNER JOIN employee AS emp ON
		emp.employee_id = mp.employee_id
	INNER JOIN division AS div ON
		div.division_id = emp.division_id;

Quando o banco de dados executa essa consulta, ele primeiro cria um conjunto de dados temporário com os resultados da consulta CTE e o nomeia max_paid. Em seguida, no SELECT externo, ele usa max_paid como se fosse uma tabela. Ele se junta max_paid juntamente com as tabelas reais no esquema (employee e division) para criar o resultado final.

Para entender melhor a sintaxe de CTE do SQL, recomendo estes 5 exemplos práticos de cláusulas WITH.

Vários CTEs em uma única consulta

Mais de um CTE pode ser usado na mesma consulta. A maneira de fazer isso é simplesmente separar cada definição de CTE com uma vírgula:

WITH cte1 AS (
	SELECT ...
	FROM ...
),
cte2 AS (
	SELECT ...
	FROM ...
)
SELECT ...
FROM cte1 JOIN cte2, ...

Cada CTE pode fazer uso de outros CTEs definidos anteriormente na mesma consulta; isso é chamado de aninhamento. Isso permite que os CTEs decomponham consultas grandes e complexas em subconsultas menores (e mais gerenciáveis). Você pode resolver gradualmente cada parte de um problema até que ele seja simplificado em um único SELECT.

O exemplo a seguir usa dois CTEs aninhados que obtêm informações de uma tabela chamada sales. O primeiro CTE, SalesByDayRegionobtém os totais de vendas por dia e por região. O segundo, SalesByDay, baseia-se em SalesByDayRegion para obter os totais de vendas de cada dia. O último SELECT junta os dois CTEs para calcular a porcentagem de vendas de cada região em relação aos totais por dia.

Aqui está a consulta:

WITH SalesByDayRegion AS (
	SELECT
		day,
		region,
		SUM(amount) AS AmountByDayRegion
	FROM 	Sales
	GROUP BY day, region
	),
	SalesByDay AS (
	SELECT
		day
		SUM(GroupedAmount1) AS AmountByDay
	FROM 	Sales
	GROUP BY day
	)
SELECT
	sdr.day,
	sdr.region,
	AmountByDayRegion
	AmountByDayRegion / AmountByDay AS Percentage
FROM SalesByDayRegion sdr 
INNER JOIN SalesByDay sd
ON sdr.day = sd.day;

Consultas Recursivas

WITH As cláusulas - ou seja, os CTEs - dão ao SQL a capacidade de implementar a recursão. Isso elimina a necessidade de fazer isso por meio de outras linguagens de programação, obtendo assim maior eficiência na execução da consulta.

Em qualquer linguagem de programação, a recursão é implementada fazendo com que uma função ou procedimento chame a si mesmo repetidamente até que uma condição de término seja atendida. Cada iteração recursiva adiciona ou modifica os dados resultantes da iteração anterior e fornece seus resultados para a próxima iteração. Quando a condição de término é atendida, ele retorna o resultado final.

No SQL, a recursão é possível porque os CTEs podem fazer referência a si mesmos. Na maioria dos dialetos SQL (exceto o Transact SQL), a palavra RECURSIVE é usada após WITH para indicar recursão. Você pode ler mais sobre o que é um CTE recursivo aqui.

Os CTEs recursivos são compostos dos seguintes elementos: um membro âncora, um membro recursivo, uma verificação de término e uma invocação.

O membro âncora estabelece o ponto de partida para a recursão. Essa parte do CTE deve ser solucionável sem invocar a si mesma. O membro recursivo usa os resultados de uma iteração anterior para processar ou adicionar mais dados aos resultados, obtendo um novo conjunto de resultados para a próxima iteração - ou, quando a condição de encerramento for atendida, um conjunto de resultados para o membro de invocação.

O membro âncora e o membro recursivo são combinados usando um operador UNION. Isso significa que os conjuntos de resultados de ambos os membros devem ter a mesma estrutura de coluna: suas colunas devem estar na mesma ordem e devem ter os mesmos tipos de dados.

No exemplo a seguir, usamos um CTE recursivo para calcular o fatorial dos números de 1 a 5:

WITH RECURSIVE factorial(n, factorial) AS (
SELECT 1, 1
UNION ALL
SELECT n + 1, (n +1) * factorial FROM factorial WHERE n < 5
)
SELECT * FROM factorial;

Nesse exemplo, o membro âncora do CTE recursivo é:

SELECT 1, 1

E o membro recursivo, junto com a condição de término, é o seguinte SELECT:

SELECT n + 1, (n +1) * factorial 
FROM factorial 
WHERE n < 5

A invocação é simplesmente um SELECT * do CTE factorial. Observe que esse SELECT trata o factorial como se fosse uma tabela. Se estivermos interessados apenas em ver as três primeiras linhas do resultado do cálculo fatorial, podemos adicionar isso à invocação:

SELECT * 
FROM factorial 
WHERE n <= 3;

Esse exemplo é extremamente simples. Os CTEs recursivos podem ser usados para resolver necessidades mais complexas, como percorrer um conjunto de dados que representa uma estrutura de árvore. Você pode usar isso para explorar o organograma de uma empresa, como veremos em um exemplo a seguir.

Se você quiser se aprofundar na recursividade em SQL, confira nosso curso on-lineConsultas Recursivas .

Os benefícios das expressões de tabela comuns

As expressões de tabela comuns têm muitos benefícios, desde melhorar a compreensibilidade das consultas até percorrer estruturas de dados hierárquicas. Você pode até mesmo desenhar imagens com o SQL. Como ponto de partida, sugiro que você leia os artigos já mencionados, CTEs explicados com exemplos e 5 exemplos práticos de CTEs em SQL. Depois, continue lendo para ver quando é útil e eficaz usar CTEs.

Facilitando a organização e a clareza da consulta

Os CTEs permitem que você resolva consultas complexas dividindo-as em várias consultas menores, mais gerenciáveis e mais fáceis de ler. Por sua vez, a capacidade de usar CTEs aninhados permite que você avance gradualmente em direção a uma solução.

Um CTE é equivalente a uma subconsulta, mas com uma vantagem muito importante: você pode dar um nome a um CTE e usá-lo repetidamente em diferentes partes da consulta. É como se você estivesse criando uma visualização - com a diferença de que a visualização é um objeto perene do banco de dados. O CTE existe somente enquanto você executa a consulta; depois, ele desaparece sem deixar rastros. O exemplo a seguir mostra como criar um CTE para obter dados resumidos de uma tabela sales e, em seguida, usar esse CTE para fazer consultas diferentes:

WITH TotalSalesByCategory AS (
	SELECT customer_category, SUM(amount) AS total_sales
	FROM sales
	GROUP BY customer_category
	)
SELECT 'big customers' AS grouping, SUM(total_sales) AS grand_total
FROM TotalSalesByCategory
WHERE customer_category IN ('A', 'B', 'C')
UNION
SELECT 'medium customers' AS grouping, SUM(total_sales) AS grand_total
FROM TotalSalesByCategory
WHERE customer_category IN ('D', 'E', 'F')
UNION
SELECT 'small customers' AS grouping, SUM(total_sales) AS grand_total
FROM TotalSalesByCategory
WHERE customer_category IN ('G', 'H', 'I');

Saiba como organizar suas consultas nestes artigos:

• Como organizar consultas SQL com CTEs
• Melhorando a legibilidade da consulta com expressões comuns de tabela

Agregações em vários níveis

Vimos diferentes opções para incluir vários CTEs na mesma consulta. Ao fazer isso, você multiplica o poder dos CTEs para reduzir a complexidade de uma consulta e melhorar sua legibilidade. As opções para usar vários CTEs no SQL se resumem a:

1. Vários CTEs independentes.
2. CTEs aninhados.
3. Usar um CTE recursivo junto com outros não recursivos.

Saiba como tirar proveito dos CTEs combinados com os artigos a seguir:

• Como escrever vários CTEs no SQL
• Como usar 2 CTEs em uma única consulta SQL
• É possível usar várias instruções WITH no SQL?

Consulta de dados hierárquicos

Os dados hierárquicos são caracterizados por terem uma relação ordenada entre seus elementos. Em geral, os dados hierárquicos são organizados em níveis; você pode descobrir quais dados estão "acima" ou "abaixo" de outras informações.

Nos bancos de dados relacionais, as relações hierárquicas podem ser estabelecidas em uma tabela fazendo com que cada linha contenha um campo que permita que ela seja relacionada a uma linha de nível superior na mesma tabela. Dessa forma, é possível criar estruturas de dados hierárquicas com linhas "pai" e linhas "filho", que geralmente são representadas visualmente como uma estrutura semelhante a uma árvore.

Os CTEs também são usados para consultar dados hierárquicos. Saiba como lendo mais sobre a consulta de dados hierárquicos no SQL.

Um exemplo típico de estrutura de dados hierárquica é o organograma de uma empresa, em que as linhas da tabela contêm dados de funcionários e cada funcionário se refere ao seu chefe. Para obter mais detalhes, leia como encontrar todos os funcionários de cada gerente no SQL.

Divirta-se desenhando imagens com CTEs

Procurando uma maneira divertida de escrever consultas engenhosas em SQL? Não é possível fazer grandes obras de arte usando SQL, mas você pode fazer desenhos esquemáticos usando CTEs recursivos.

Mencionamos anteriormente que você pode usar a recursão para percorrer estruturas de dados hierárquicas em forma de árvore, como um organograma. Talvez você se surpreenda ao saber que é possível desenhar e também percorrer estruturas hierárquicas com SQL. Para descobrir isso, leia este artigo sobre o uso do SQL para desenhar uma árvore de Natal bastante singular.

Alternativas aos CTEs do SQL

Deixando de lado a recursão (que no SQL é uma virtude exclusiva dos CTEs), o restante dos benefícios oferecidos pelos CTEs pode ser obtido com subconsultas, exibições e tabelas temporárias. Vamos comparar cada um deles com um CTE.

CTEs vs. subconsultas

As subconsultas em SQL devem ser escritas entre parênteses e incluídas com o restante dos elementos da consulta principal. Isso significa que elas não dão muita clareza à consulta. Os CTEs são escritos separadamente, no início da consulta e dentro de sua própria cláusula WITH. Isso torna a leitura da consulta muito mais fácil. Se você não estiver interessado em ver o funcionamento interno do CTE, poderá ignorar a cláusula WITH e ler apenas o SELECT principal para entender os resultados.

Você pode obter mais detalhes sobre as diferenças entre CTEs e subconsultas lendo este artigo que explica detalhadamente as diferenças entre CTEs e subconsultas.

Além disso, as subconsultas não são reutilizáveis. Se você quiser usar o resultado de uma subconsulta em diferentes partes de uma consulta, terá de escrevê-la novamente todas as vezes. Enquanto isso, os CTEs podem ser reutilizados quantas vezes forem necessárias no site principal SELECT ou em outros CTEs da mesma consulta. Isso melhora a facilidade de leitura e o desempenho da consulta; cada CTE é executado apenas uma vez e seus resultados estão disponíveis em toda a consulta.

Por fim, é bom observar que as subconsultas dentro da cláusula FROM contêm um alias que é usado no restante da consulta para acessar seus resultados. Mas outras subconsultas incluídas na lista de colunas de uma cláusula SELECT ou WHERE não têm aliases. Os CTEs sempre têm um nome que é usado para fazer referência a eles no restante da consulta.

CTEs vs. visualizações

As visualizações têm muito em comum com os CTEs, mas há uma diferença crucial entre elas. As visualizações permanecem como objetos no banco de dados até que alguém as remova. Os CTEs existem apenas no contexto de sua consulta. Quando a consulta termina de ser executada, o CTE desaparece. Leia este artigo sobre as diferenças entre CTEs e exibições para saber mais.

CTEs vs. tabelas temporárias

As tabelas temporárias também têm muitas semelhanças com os CTEs. O ciclo de vida de uma tabela temporária termina com a sessão do banco de dados na qual a tabela temporária foi criada, de modo que você não precisa se preocupar em deixar objetos desnecessários bagunçando o esquema do banco de dados. Mas há uma diferença fundamental: a criação da tabela temporária deve ser feita em um comando separado da consulta que a utiliza. Não é possível criar uma tabela temporária dentro de uma consulta SELECT; se isso fosse feito, seria uma subconsulta, não uma tabela temporária.

Se você criar uma tabela temporária e depois perceber que precisa adicionar uma coluna a ela ou alterar qualquer outra coisa, deverá excluí-la e recriá-la. Com um CTE, você só precisa fazer a alteração em sua definição e executar a consulta novamente.

CTEs em diferentes DBMSs

Os CTEs apareceram no padrão SQL de 1999. Sua implementação em cada um dos sistemas de gerenciamento de banco de dados (DBMSs) mais populares foi a seguinte:

• PostgreSQL: versão 8.4, julho de 2009
• MS SQL Server: versão 2012, maio de 2012
• Oracle Database: versão 9.2, junho de 2002
• MySQL: versão 8.0, abril de 2018
• MariaDB: versão 10.2.2, maio de 2018

O Google BigQuery também oferece suporte para CTEs. Como ele é executado na nuvem como um data warehouse totalmente gerenciado, basta saber que atualmente ele oferece suporte a CTEs.

A sintaxe de CTE do SQL é a mesma para todos os DBMSs. A única exceção é no MS SQL Server (T-SQL), onde a sintaxe para escrever um CTE recursivo não requer o uso da palavra-chave RECURSIVE. Você pode saber mais sobre CTEs no MS SQL Server lendo sobre recursão no SQL Server e vendo exemplos de CTE no SQL Server.

Colocando em prática seu conhecimento sobre CTEs do SQL

Ao longo deste artigo, você aprendeu tudo sobre expressões de tabela comuns no SQL. Agora você precisa colocar esse conhecimento em prática. A melhor maneira de fazer isso é fazer um de nossos cursos específicos sobre CTEs e consultas recursivas, onde você encontrará muitos exercícios interativos.

Esses cursos são voltados para estudantes, analistas de banco de dados intermediários e cientistas de dados iniciantes. O único requisito é o domínio do SQL básico, como as declarações SELECT, JOINs, GROUP BY, etc.

Você pode optar pelo nosso curso padrão Consultas Recursivas, que é independente do dialeto SQL, ou escolher um curso específico para o DBMS de sua preferência:

• Consultas Recursivas no MS SQL Server
• Consultas Recursivas no MySQL 8
• Consultas Recursivas em PostgreSQL

Nenhum desses cursos exige que você use seus próprios bancos de dados. Nossa plataforma fornece a você um banco de dados pronto para uso, completo com esquema e preenchido com informações. Esses ambientes de prática são seguros; você não precisa se preocupar em cometer erros, pois não há chance de quebrar nada.

Confira estes outros recursos valiosos para fortalecer seu conhecimento sobre as consultas WITH:

• As 5 principais perguntas da entrevista sobre SQL CTE
• 11 exercícios de expressão de tabela comum SQL

A importância de conhecer os CTEs em SQL

O domínio das expressões de tabela comuns (CTEs) e das consultas recursivas do SQL começa com a compreensão de seus conceitos básicos e, em seguida, avança para aplicações mais complexas. Nossos recursos selecionados tornam o aprendizado de CTEs agradável e prático.

Para manter suas habilidades em CTE, crie o hábito de praticar diariamente. Ao se deparar com consultas complexas, considere se o uso de um ou mais CTEs poderia simplificar a tarefa, dividindo-a em partes menores e mais gerenciáveis. Em breve, você poderá usar CTEs rotineiramente em suas tarefas SQL.

Além disso, recomendo que confira nosso cursoConsultas Recursivas para aprimorar ainda mais sua compreensão dos CTEs SQL. Ele o equipará com as ferramentas para lidar com confiança até mesmo com as consultas mais complexas. Bom aprendizado!