CTE vs. Subconsulta em SQL: Qual é a diferença?

O que são expressões comuns de tabela (CTEs)? Elas são iguais às subconsultas? Quando você precisaria usar CTEs? Este artigo analisa as semelhanças e diferenças entre CTE e subconsulta.

Quando apresento as expressões comuns de tabela a um aluno, sua primeira reação é: "Isso é apenas uma subconsulta! Por que preciso aprender isso?". Vamos responder a essa pergunta analisando o que você pode fazer com uma subconsulta SQL e quais são as vantagens adicionais de usar um CTE.

Se, depois de ler o artigo, você decidir que gostaria de aprender a usar CTEs SQL, o site LearnSQL.com.br oferece um curso abrangente de Common Table Expressions (Expressões de tabelas comuns) que aborda o assunto em profundidade. Começando com exemplos simples, você trabalhará com conceitos difíceis, como processamento de árvores e gráficos. Cada tópico tem explicações e exemplos passo a passo. Com 114 exercícios interativos, você terá bastante prática. Você provavelmente precisará de cerca de 18 horas para concluir o curso.

O que é uma subconsulta?

Uma subconsulta é uma consulta aninhada dentro da consulta principal; a melhor forma de explicar isso é por meio de um exemplo. Todos os exemplos deste artigo usam o SQL Server; a sintaxe pode ser ligeiramente diferente em outros dialetos de SQL.

Suponha que a gerência queira oferecer um desconto a todos os clientes cujas compras anuais foram maiores do que a média de todos os clientes. A consulta para extrair uma lista desses clientes poderia ter a seguinte aparência:

SELECT 
  account_no, 
  name 
FROM customers 
WHERE annual_purchases >
  (SELECT AVG(annual_purchases) FROM customers);

A consulta principal lista os clientes, e a subconsulta calcula a média de compras anuais de todos os clientes. A subconsulta não precisa de um nome (exceto quando você estiver trabalhando no PostgreSQL).

Se você não estiver familiarizado com subconsultas, talvez queira dar uma olhada no curso LearnSQL.com.br 's SQL para Iniciantes, que tem uma seção dedicada a subconsultas. Você pode ler mais sobre subconsultas em nosso Guia para Iniciantes em Subconsultas SQL. How to Practice SQL Subqueries (Como praticar subconsultas SQL) lhe dá algumas ideias sobre como colocar esses conceitos em prática.

E o que são CTEs?

As expressões de tabela comuns são conjuntos de resultados nomeados que são definidos na frente de uma consulta e podem ser acessados pela consulta como se fossem tabelas. Suponhamos que queiramos comparar os salários dos funcionários com o salário médio de suas funções. A consulta pode ter a seguinte aparência:

WITH avg_salary AS (
  SELECT 
    role, 
    avg(salary) AS average 
  FROM employee 
  GROUP BY role
)
SELECT 
  employee.role, 
  name, 
  salary, 
  avg_salary
FROM employee 
JOIN avg_salary ON avg_salary.role = employee.role
ORDER BY role, name

Os CTEs são introduzidos pela palavra-chave WITH, e os resultados são armazenados em uma tabela temporária nomeada. Neste exemplo, os resultados do CTE são armazenados na tabela avg_salary, que é usada pela consulta principal para obter o salário médio de cada função.

De fato, CTEs simples como esse poderiam ser escritos como subconsultas. O artigo "What Is a CTE?" (O que é um CTE?) explica os CTEs em mais detalhes.

Quais são as diferenças entre CTEs e subconsultas?

Começarei listando brevemente as principais diferenças e, em seguida, examinarei algumas delas com mais detalhes.

• Os CTEs são definidos na frente da consulta, enquanto as subconsultas são definidas em linha.
• Os CTEs sempre devem ser nomeados. Somente o PostgreSQL insiste que as subconsultas devem ter um nome.
• Os CTEs podem ser usados recursivamente. Explicarei isso mais adiante neste artigo.
• Os CTEs são muito mais legíveis do que as subconsultas quando se está escrevendo um relatório complexo.
• Um CTE pode ser usado várias vezes em uma consulta, enquanto uma subconsulta só pode ser usada uma vez. Isso pode tornar a definição da consulta muito mais curta, mas não necessariamente resultará em melhor desempenho.
• As subconsultas podem ser usadas em uma cláusula WHERE em conjunto com as palavras-chave IN ou EXISTS, mas não é possível fazer isso com CTEs.
• As subconsultas podem ser usadas para obter uma única parte dos dados de uma tabela para atualizar um valor em outra tabela.

Algumas funcionalidades são limitadas apenas a subconsultas. Darei dois exemplos em que as subconsultas não podem ser substituídas por CTEs. No primeiro exemplo, a subconsulta faz parte da cláusula WHERE. No segundo, ela é usada para pegar um único valor de uma tabela para atualizar outra. Como mencionei acima, o SQL não permite que você use CTEs para essas tarefas.

Somente subconsultas: Uso de subconsultas em WHERE

No primeiro exemplo, um banco mantém detalhes de todas as transações do dia em uma tabela chamada daily_trans. Os dados dessa tabela incluem um número de conta, um código de transação e um valor.

O banco de dados também tem uma tabela chamada transaction_typese suas colunas incluem o código da transação e um indicador chamado debit_credit, que é definido como 1 para os tipos de transação que creditam a conta do cliente e 2 para aqueles que debitam a conta.

Se o banco quiser uma lista de todas as transações de crédito do dia, a consulta poderá ter a seguinte aparência:

SELECT 
  account_no, 
  tran_code, 
  amount
FROM daily_trans
WHERE tran_code IN
  (SELECT tran_code 
   FROM transaction_types
   WHERE debit_credit = 1);

Esse tipo de consulta só pode ser escrito usando uma subconsulta. Você não poderia substituir a subconsulta por um CTE aqui.

Somente subconsultas: Uso de subconsultas em UPDATE

No segundo exemplo, o mesmo banco tem uma tabela chamada customercujas colunas incluem um número de conta, um nome de cliente e o número de funcionário da pessoa designada para o suporte ao cliente.

O banco fez uma reorganização de responsabilidades e quer reatribuir a pessoa de suporte para alguns clientes. Para fazer isso, eles criaram uma tabela chamada reassignments que contém o número de identificação do funcionário de suporte antigo e o número do funcionário da pessoa de suporte que assumirá suas responsabilidades.

Para realizar as reatribuições, poderíamos escrever uma consulta como esta:

UPDATE customer 
SET support_person =
  (SELECT new_employee 
   FROM reassignments
   WHERE old_employee = customer.support_person);

Os CTEs tornam uma consulta complexa mais legível

Para ilustrar como os CTEs podem facilitar a compreensão, vamos pegar uma consulta que usa várias subconsultas e recodificá-la usando CTEs.

Suponhamos que tenhamos uma loja que vende três tipos de produtos: livros, música e vídeos. O gerente quer saber quanto cada cliente comprou em cada categoria.

O relatório pode ser parecido com este:

Aqui está uma consulta que usa subconsultas para produzir o relatório:

SELECT 
	customer, 
	sum(purchases) AS Total, 
	total_books AS Books, 
	total_music AS Music, 
	total_videos AS Videos
FROM sales
JOIN 
	(SELECT account_no, sum(purchases) AS total_books FROM sales 
	WHERE product_type = 'Books'
	GROUP BY account_no) books
ON books.account_no = sales.account_no
JOIN 
	(SELECT account_no, sum(purchases) AS total_music FROM sales 
	WHERE product_type = 'Music'
	GROUP BY account_no) music
ON music.account_no = sales.account_no
JOIN 
	(SELECT account_no, sum(purchases) AS total_videos FROM sales 
	WHERE product_type = 'Videos'
	GROUP BY account_no) videos
ON videos.account_no = sales.account_no
GROUP BY customer
ORDER BY customer

Como você pode ver, ela é bastante complicada. E é difícil de acompanhar se alguém precisar fazer alterações nele mais tarde.

Agora vamos ver como essa consulta ficaria se a reescrevêssemos usando Common Table Expressions:

WITH books AS (
  SELECT 
    customer, 
    sum(purchases) AS total_books 
  FROM sales 
  WHERE product_type = 'Books'
  GROUP BY customer
),

music AS (
  SELECT 
    customer, 
    sum(purchases) AS total_music 
  FROM sales 
  WHERE product_type = 'Music'
  GROUP BY customer
),

videos as (
  SELECT 
    customer, 
    sum(purchases) AS total_videos 
  FROM sales 
  WHERE product_type = 'Videos'
  GROUP BY customer
)

SELECT 
  customer, 
  sum(purchases) AS Total, 
  total_books AS Books, 
  total_music AS Music, 
  total_videos AS Videos
FROM sales
JOIN books ON books.customer = sales.customer
JOIN music ON music.customer = sales.customer
JOIN videos ON videos.customer = sales.customer
GROUP BY customer
ORDER BY customer

A maioria das pessoas achará essa segunda versão muito mais fácil de entender, embora ambas as consultas produzam exatamente os mesmos resultados.

O que são CTEs recursivos?

Eu disse anteriormente que somente os CTEs podem ser recursivos. Então, o que é uma consulta recursiva? As consultas recursivas permitem que você navegue por dados hierárquicos e produza relatórios adequados para dados do tipo árvore e gráfico. Exemplos de dados hierárquicos incluem:

• Em uma organização, um funcionário pode se reportar a um subgerente; o subgerente se reporta a um gerente, e o gerente se reporta ao gerente.
• Na manufatura, um produto pode ser feito de vários componentes. Cada componente também pode ser feito de muitos subcomponentes, e os subcomponentes podem ser feitos de várias matérias-primas.

Vamos dar uma olhada em um exemplo. Uma sorveteria tem vários itens no cardápio. Cada item do cardápio pode ser feito de vários ingredientes: uma banana split é composta de bananas, calda de chocolate e sorvete. Mas a calda de chocolate também tem vários ingredientes. Eles podem incluir cacau em pó, açúcar e outros ingredientes.

O proprietário quer uma lista completa de cada item do cardápio, seguida de todos os seus ingredientes. Parte da lista pode ter a seguinte aparência:

No banco de dados, temos duas tabelas:

• A tabela Item contém uma lista de cada item do cardápio e de cada ingrediente.
• A tabela Bill_of_materials contém links entre cada item e seus ingredientes.

A tabela Items contém essas informações:

Aqui estão alguns exemplos de entradas na tabela Bill_of_materials tabela. A coluna item_id contém um link para o item pai na tabela, enquanto contém um link para um de seus ingredientes. Items tabela, enquanto component_id contém um link para um de seus ingredientes. A primeira entrada, portanto, mostra que o Item 10: Cacau é um ingrediente do Item 9: Molho de chocolate.

Aqui está a consulta recursiva usada para percorrer essas informações. A consulta foi escrita no SQL Server; outros dialetos seriam ligeiramente diferentes.

WITH menu_ingredients (id, path, description, item_id)
AS (
  SELECT 
CAST (id AS bigint), 
CAST (description as varchar (255)), 
CAST ('' AS varchar(40)),
CAST (id AS bigint)
   FROM items 
   WHERE description = 'Menu'
   
   UNION ALL
   
   SELECT
CAST (bom.component_id AS bigint), 
CAST (m.path + ' > ' + i.description AS varchar(255)),
i.description,
CAST (bom.item_id AS bigint)
   FROM menu_ingredients m, bill_of_materials bom
   JOIN items i
   ON i.id = bom.component_id
   WHERE bom.item_id = m.id
)

SELECT 
  path, 
  description 
FROM menu_ingredients
ORDER BY path

Uma explicação completa do que está acontecendo aqui está fora do escopo deste artigo, mas explicarei rapidamente o básico. Resumidamente, quando uma linha é adicionada ao conjunto de resultados em uma consulta recursiva, ela pode "ver" a linha anterior e usá-la para obter uma informação que pode ser usada para encontrar a próxima linha. Essa consulta começa selecionando a entrada de nível superior na lista de materiais: o próprio menu. A partir daí, ela pode percorrer todas as suas linhas "filhas", ou seja, os ingredientes que o compõem. E cada ingrediente pode selecionar suas próprias linhas filhas, se houver alguma. Para obter uma explicação mais detalhada sobre recursão, consulte O que é um CTE recursivo no SQL? E, como mencionei anteriormente, as consultas recursivas são totalmente abordadas no curso Common Table Expressions (Expressões comuns de tabela) do site LearnSQL.com.br.

CTE vs. Subquery

Em resumo, escolha um CTE quando:

• Você deseja tornar uma consulta complexa mais legível.
• Você precisa usar uma consulta recursiva.

Escolha uma subconsulta quando:

• Você estiver usando as palavras-chave da cláusula WHERE IN ou EXISTS para pegar os critérios de seleção de outra tabela.
• Você quiser selecionar um único dado de outra tabela como o novo valor de um campo em uma instrução UPDATE.

Em todas as outras circunstâncias, a escolha é sua: você pode usar o que se sentir mais confortável.

E se você precisar praticar com CTEs ou subconsultas, talvez queira experimentar o site Curso de Práticas em SQL que tem centenas de exercícios interativos para ajudá-lo a consolidar suas habilidades.

Bom aprendizado!