CTE vs. tabela temporária: Qual é a diferença?

O SQL oferece a você duas opções para lidar com consultas complexas de forma eficiente: CTEs e tabelas temporárias. Continue lendo para avaliar os prós e os contras dos CTEs e das tabelas temporárias.

No SQL (Structured Query Language), uma expressão de tabela comum (CTE) permite que você defina uma subconsulta e atribua um nome a ela para que possa ser usada em uma consulta principal. Uma tabela temporária é uma tabela de banco de dados que você cria explicitamente, como qualquer outra tabela, mas com a particularidade de que ela desaparece quando você fecha a conexão do banco de dados em que foi criada. Portanto, sim, os CTEs e as tabelas temporárias têm algumas coisas em comum. Mas o debate entre CTEs e tabelas temporárias é mais complexo.

Para dominar completamente esse tópico, você pode fazer nosso cursoConsultas Recursivas . Você aprenderá a usar CTEs para processar estruturas de dados complexas, como árvores e gráficos. Com seus 114 exercícios, esse curso o guiará até o domínio das consultas SQL mais complexas. Ele também oferece um questionário final para testar seu conhecimento adquirido.

Os CTEs e as tabelas temporárias têm objetivos em comum. Ambos geram resultados intermediários para uma consulta sem deixar objetos permanentes no banco de dados; isso economiza espaço de armazenamento. Mas há diferenças importantes entre eles, por isso é útil saber quando usar um ou outro. Vamos começar examinando a sintaxe do CTE.

Sintaxe do CTE

A seguir, veremos um caso de uso em que um CTE SQL é preferível a uma consulta recursiva.

Um CTE SQL começa com uma cláusula WITH, seguida de um nome e da definição de sua subconsulta. Depois disso, há uma instrução SQL comum (geralmente um SELECT) que consulta os resultados do CTE como uma tabela normal. Em sua forma mais simples, um CTE tem a seguinte aparência:

WITH cte_name AS (
  cte_query_definition
)
SELECT * 
FROM cte_name;

Para obter mais detalhes sobre a sintaxe do CTE, leia O que é uma expressão de tabela comum em SQL.

Sintaxe de tabela temporária

Para tabelas temporárias, a sintaxe varia um pouco, dependendo do seu sistema de gerenciamento de banco de dados relacional (RDBMS). A tabela a seguir mostra como criar tabelas temporárias nos RDBMSs mais populares:

CREATE TEMPORARY TABLE MyTempTbl (
  Id INT, 
  Name VARCHAR(50)
);

-- Local temp table:
CREATE TABLE #MyTempTbl (
  Id INT, 
  Name NVARCHAR(50)
);

-- Global temp table:
CREATE TABLE ##MyTempTbl (
  Id INT, 
  Name NVARCHAR(50)
);

CREATE TEMPORARY TABLE MyTempTbl (
  Id INT, 
  Name VARCHAR(50)
);

CREATE GLOBAL TEMPORARY TABLE MyTempTbl (
  Id NUMBER, 
  Name VARCHAR2(50)
)
-- data is kept until the end 
-- of the current transaction
ON COMMIT DELETE ROWS;

CREATE GLOBAL TEMPORARY TABLE MyTempTbl (
  Id NUMBER, 
  Name VARCHAR2(50))
-- data is kept until the end 
-- of the current session
ON COMMIT PRESERVE ROWS;

Para saber mais sobre a criação de todos os tipos de tabelas em SQL, faça nosso curso The Basics of Creating Tables in SQL. Seus 80 exercícios o ajudarão a dominar o comando CREATE TABLE e todas as suas opções. Você começará criando uma tabela em sua forma mais básica e, em seguida, avançará para a definição de chaves primárias e estrangeiras, colunas NULL/NOT NULL e modificação da estrutura de tabelas existentes.

Agora que já vimos a sintaxe dos CTEs e das tabelas temporárias, vamos examinar como cada um deles funciona

Como resolver o mesmo problema com CTEs e tabelas temporárias

Vamos tentar um exemplo de problema que pode ser resolvido usando um CTE e uma tabela temporária.

Você tem duas tabelas: uma detalhada Sales detalhada e uma tabela Countries tabela com informações específicas do país. Você deseja listar os totais de vendas por país junto com as informações detalhadas do país. Para isso, você pode usar uma expressão de tabela comum ou uma tabela temporária. Vamos explorar os dois métodos para ver como eles podem ser utilizados de forma eficaz para essa tarefa.

A solução baseada em CTE usa um CTE chamado CountryTotals com uma subconsulta que calcula os totais agrupados por CountryCode. Ela também tem uma consulta principal que combina esses totais com os dados de cada país:

WITH CountryTotals (CountryCode, TotalSales) AS (
  SELECT 
    CountryCode, 
    SUM(Sales) AS TotalSales
  FROM Sales
  GROUP BY CountryCode
)
SELECT 
  c.*, 
  ct.TotalSales
FROM Countries c
INNER JOIN CountryTotals ct 
  ON ct.CountryCode = C.CountryCode;

Você pode encontrar muitos outros exemplos de expressões de tabela comuns nesses CTEs explicados com exemplos.

Para obter o mesmo resultado usando uma tabela temporária, você deve primeiro criar a tabela temporária com as colunas CountryCode e TotalSales. O código de exemplo a seguir usa a sintaxe do MySQL para fazer isso:

CREATE TEMPORARY TABLE CountryTotals (
  CountryCode char(3), 
  TotalSales decimal(15, 2)
);

As colunas que definem a estrutura da tabela temporária são as mesmas incluídas na definição de CTE acima. Para criar a tabela temporária, o tipo de dados de cada coluna deve corresponder ao tipo retornado pela coluna correspondente da consulta que você usará para preenchê-la com dados. Essa consulta é mostrada abaixo:

Observe que o SELECT colocado após o INSERT é semelhante ao que define a subconsulta no CTE original.

Agora que você tem os totais de vendas por país na tabela temporária, pode juntá-la à tabela Countries para obter o resultado final:

SELECT
  c.*,
  ct.TotalSales
FROM Countries c
INNER JOIN CountryTotals ct 
  ON ct.CountryCode = C.CountryCode;

Essa consulta também é idêntica à consulta principal do CTE (a que está fora da cláusula WITH ), pois a tabela temporária tem o mesmo nome do CTE.

É importante observar que nem sempre é possível substituir as tabelas temporárias por CTEs. Isso pode ser feito quando as linhas da tabela temporária vêm de um único SELECT. Mas se os dados forem modificados por UPDATE, DELETE ou outros comandos INSERT depois de terem sido carregados na tabela temporária, você não poderá substituir a tabela temporária por um CTE.

Quando usar um CTE em vez de uma tabela temporária

Mesmo nas situações em que um CTE pode ser substituído por uma tabela temporária (ou vice-versa), nem sempre é uma boa prática fazer isso. Os casos de uso de CTEs e de tabelas temporárias são diferentes; há situações em que um CTE é preferível e situações em que uma tabela temporária é preferível.

Vamos examinar os casos de uso de CTEs e tabelas temporárias, começando pelos CTEs.

Casos de uso de CTEs

Os CTEs não geram objetos persistentes no banco de dados. Em outras palavras, o CTE vive apenas enquanto a consulta que o contém é executada. Quando a execução da consulta é concluída, o CTE desaparece sem deixar rastros. Além disso, o CTE e a consulta que o consome estão contidos na mesma instrução SQL. Essas qualidades tornam o CTE preferível para melhorar a legibilidade e a organização dos scripts SQL, ou seja, para facilitar a compreensão das consultas pelos seres humanos.

Conjuntos de dados pequenos

Quando o resultado de uma subconsulta produz um número relativamente pequeno de linhas (ou seja, 10 ou 20 mil linhas), um CTE é a maneira ideal de implementá-la. Como ele não usa armazenamento persistente, não gera operações de E/S em disco. Portanto, as consultas são resolvidas muito mais rapidamente.

Visualizações internas

Não é possível criar, preencher com dados ou consultar uma tabela temporária dentro de uma visualização. Mas é possível incluir um CTE dentro de uma visualização e usá-lo em qualquer lugar como se fosse uma tabela somente de leitura.

Refatoração

Ao refatorar consultas de banco de dados, os CTEs são grandes aliados. Em consultas longas e complexas, os CTEs permitem que você defina resultados intermediários (ou seja, subconsultas nomeadas) que podem ser reutilizados repetidamente na mesma consulta.

Além disso, a complexidade de uma consulta longa pode ser reduzida gradualmente com CTEs aninhados até que seja simplificada para um SELECT de apenas algumas linhas.

Melhoria do desempenho do banco de dados

Em alguns RDBMSs (por exemplo, MS SQL Server), os CTEs nos permitem criar planos de execução mais eficientes do que as tabelas temporárias. Isso pode melhorar consideravelmente o desempenho do banco de dados, reduzindo os tempos de execução das consultas.

O benefício de desempenho obtido depende do otimizador do RDBMS, o que significa que o uso de CTEs nem sempre resulta em melhor desempenho. Mas esse é um fator a ser considerado ao escolher entre um CTE e uma tabela temporária para o tratamento de subconsultas.

Recursão

Uma área em que o uso de CTEs é obrigatório é em consultas recursivas. No SQL, um CTE recursivo é capaz de invocar a si mesmo para implementar processos recursivos, como percorrer estruturas de dados hierárquicas. Em outras palavras, a recursão requer CTEs.

Permitir a recursão no SQL evita a necessidade de usar outras linguagens de programação, o que promove maior eficiência nas consultas que precisam invocar a si mesmas. Você pode ler mais em nosso artigo O que é um CTE recursivo no SQL?

Agora que você sabe em quais casos é conveniente usar um CTE, pode aumentar seu conhecimento revisando as perguntas mais comuns da entrevista sobre CTE em SQL e treinando-se com estes exercícios sobre CTE.

Casos de uso de tabelas temporárias

A principal vantagem do CTE - não armazenar dados de forma persistente - pode ser uma desvantagem. Em alguns casos de uso, é preferível que os dados sejam armazenados em tabelas (mesmo que temporárias). Vamos dar uma olhada nas situações mais comuns.

Reutilização de conjuntos de dados

A reutilização do código CTE é limitada a uma única consulta. Os dados armazenados em uma tabela temporária, por outro lado, podem ser usados repetidamente em diferentes consultas. O principal requisito é que essas consultas sejam executadas na mesma conexão de banco de dados.

Processos de ETL

Quando você implementa processos ETL (Extract, Transform, Load) usando scripts SQL, geralmente precisa gerar resultados intermediários e aplicar transformações sucessivas a um único conjunto de dados. Somente quando isso for feito, você poderá finalmente despejá-los em tabelas permanentes. Nessas situações, é muito útil manter tabelas temporárias disponíveis durante todo o processo de transformação.

Conjuntos de dados grandes

Ao trabalhar com conjuntos de dados temporários e de grande volume - digamos, milhões de registros -, as tabelas temporárias são sempre preferíveis aos CTEs. Se você tentar executar um CTE que inclua uma subconsulta de grande volume, é muito provável que o mecanismo de banco de dados tente armazenar os resultados temporários na memória do servidor. Isso esgotaria a capacidade disponível ou exigiria alguns recursos do sistema operacional para despejar a memória no disco. Em resumo, isso causaria um desempenho ruim do banco de dados. Portanto, se você souber de antemão que os resultados de uma subconsulta serão muito grandes, é melhor usar tabelas temporárias em vez de CTEs.

Geração de índices

Como qualquer outra tabela em um banco de dados, as tabelas temporárias suportam a criação de índices para acelerar a execução da consulta. Quando os resultados intermediários exigirem um grande número de linhas (e precisarem ser usados repetidamente), armazene esses resultados em uma tabela temporária com um ou mais índices. A criação dos índices pode levar algum tempo, mas esse tempo será mais do que recuperado se várias consultas precisarem ser executadas no mesmo conjunto de dados.

Em geral, os melhores candidatos para indexação são as colunas usadas em JOINs, WHERE ou GROUP BY.

Encerrando o debate sobre CTE vs. tabela temporária

A escolha entre CTEs e tabelas temporárias nem sempre é fácil. Discutimos a sintaxe e os casos de uso típicos dessas duas soluções.

Lembre-se de explorar nosso Consultas Recursivas que ensinará a você como usar expressões de tabela comuns. O curso é destinado a analistas de banco de dados iniciantes, estudantes e desenvolvedores que desejam aprofundar seus conhecimentos de SQL. Para fazer o curso, você só precisa de um computador com conexão à Internet e um navegador da Web.

Agora é hora de aplicar sua sabedoria! Se você souber quando usar CTEs em vez de tabelas temporárias, sempre obterá o máximo de eficiência ao trabalhar com dados temporários.