O que é um CTE em T-SQL? Um guia detalhado para iniciantes com 7 exemplos

Uma expressão de tabela comum (CTE) é um recurso poderoso do T-SQL que simplifica a criação de consultas no SQL Server. As CTEs funcionam como tabelas virtuais (com registros e colunas) que são criadas em tempo real durante a execução de uma consulta. Eles são consumidos pela consulta e destruídos após a execução da consulta.

Em alguns casos - como quando a consulta espera dados em um formato específico e as tabelas de origem têm os dados em outro formato - um CTE pode atuar como uma ponte para transformar os dados nas tabelas de origem no formato esperado pela consulta.

Neste artigo, abordaremos como você pode criar um CTE em T-SQL, o dialeto SQL usado pelo Microsoft SQL Server. Para saber ainda mais sobre CTEs em T-SQL, recomendo nosso curso interativo Consultas Recursivas no MS SQL Server. Ele contém mais de 100 exercícios práticos sobre CTEs em T-SQL. Você praticará tudo o que foi abordado neste artigo por conta própria!

Expressões comuns de tabela (CTEs) em T-SQL

Durante a execução de uma consulta, você pode consultar o CTE como faria com qualquer tabela normal. Ele pode ser usado nas declarações SELECT, INSERT, UPDATE e DELETE. Os CTEs não faziam parte da linguagem SQL original. Eles foram introduzidos como um novo recurso na definição do padrão SQL em 1999 (SQL 3). Em 2005, eles foram disponibilizados no SQL Server.

Os exemplos de consultas SQL mostrados neste artigo serão baseados na tabela olympic_games. A tabela contém os resultados de todos os jogos olímpicos, incluindo 3 registros (ouro, prata e bronze) para cada esporte.

Exemplo 1: um CTE simples

Para explicar a sintaxe de CTE em T-SQL, vamos escrever uma consulta simples. Suponha que queiramos um relatório com os países e o número de vezes que cada país ganhou uma medalha de ouro em uma maratona. Podemos criar primeiro um CTE chamado gold_in_marathon para retornar essas linhas de medalhas de ouro em maratonas masculinas e femininas. No CTE gold_in_marathon, precisamos apenas das colunas city, year e country.

Observe que todos os CTEs começam com a palavra-chave WITH, seguida de parênteses. Entre parênteses, você escreve a consulta que deseja que o CTE retorne. Abaixo está a consulta completa. Com o CTE mostrado em negrito:

WITH gold_in_marathon AS
(
  SELECT 
    city, 
    year, 
    country
  FROM olympic_games
  WHERE medal_type = 'Gold' 
  AND sport IN ('Marathon Men', 'Marathon Women')
)
SELECT 
  country, 
  count(*) AS gold_medals_in_marathon 
FROM gold_in_marathon
GROUP BY country
ORDER BY gold_medals_in_marathon DESC;

Na consulta T-SQL acima, você pode identificar duas consultas diferentes. A primeira é definida pela cláusula WITH entre parênteses: essa é a consulta que define o conteúdo do CTE. Essa é uma consulta SQL normal: você pode inserir todos os diferentes recursos SQL (WHERE, GROUP BY, HAVING, UNION, etc.). O banco de dados usa o resultado para criar uma tabela virtual chamada gold_in_marathon. Você pode ver o nome do CTE (gold_in_marathon) após a cláusula WITH.

A segunda consulta é a consulta externa. Ela faz referência ao CTE gold_in_marathon como qualquer outra tabela. Quando a execução da consulta externa estiver concluída, o CTE gold_in_marathon será destruído e você não poderá mais fazer referência a ele.

Observe que o CTE tem apenas as colunas city, year e country da tabela olympic_games. Depois de criar o CTE, o banco de dados executa a consulta externa, que, por sua vez, lê o CTE gold_in_marathon, agrupando as linhas por country e usando a função COUNT() para obter quantas medalhas de ouro cada país tem. Abaixo estão os resultados da consulta:

Exemplo 2: uso de CTEs com colunas renomeadas (com alias) em T-SQL

Na próxima consulta, renomearemos explicitamente uma coluna no CTE usando um alias. Vamos supor que queremos um relatório com uma classificação de países para resultados de maratona (mulheres e homens). Cada atleta receberá 3 pontos por cada medalha de ouro, 2 pontos por cada medalha de prata e 1 ponto por cada medalha de bronze. Criaremos um CTE chamado player_points para calcular os pontos de cada jogador. Como usaremos uma coluna calculada para os pontos, precisaremos atribuir um nome a essa coluna usando um alias. Vamos ver a consulta completa:

WITH player_points AS
(
  SELECT 
    country,
    winner_name, 
    SUM(
      CASE medal_type 
        WHEN 'Gold' THEN 3
	  WHEN 'Silver' THEN 2
	  WHEN 'Bronze' THEN 1
	END
	)  AS player_total
    FROM   olympic_games
    WHERE sport in ('Marathon Men', 'Marathon Women')
    GROUP BY country,winner_name
)
SELECT 
  country, 
  SUM(player_total) AS country_points 
FROM player_points
GROUP BY country
ORDER BY country_points DESC;

No CTE player_points, calculamos os pontos de cada jogador usando uma coluna calculada, que não tem um nome. Precisamos definir um nome para essa coluna a fim de podermos nos referir a ela na consulta externa.

Uma maneira de definir um nome é usar um alias (mais tarde veremos outra maneira) usando a cláusula AS. Você pode ver que a coluna foi nomeada player_total na definição do CTE. Na consulta externa, agrupamos as linhas por país para calcular o total de pontos de cada país. Observe que usamos a expressão SUM(player_total) e novamente usamos um alias para renomear a coluna para country_points. Os resultados da consulta estão abaixo:

Antes de encerrar esta seção, gostaria de sugerir o artigo What Is a CTE in SQL Server?, no qual é possível encontrar muitos exemplos de consultas que usam CTEs em T-SQL. O artigo What Is a Common Table Expression (CTE) in SQL? aborda o tópico de CTEs no SQL padrão; ambos os artigos são um bom complemento para este. Se você quiser um curso sobre CTEs e consultas recursivas, sugiro novamente Consultas Recursivas no MS SQL Server.

Exemplo 3: Uso de vários CTEs na mesma consulta T-SQL

Nesta seção, mostraremos dois exemplos de consultas que usam mais de um CTE. No primeiro exemplo, usaremos dois CTEs independentes e a consulta principal acessará ambos.

Vamos supor que queremos um relatório com os nomes dos atletas olímpicos que ganharam pelo menos uma medalha de ouro e uma de prata. O primeiro CTE chama-se gold. Após o nome do CTE, você pode ver os nomes das colunas (winner_name e gold_medals) explicitamente definidos entre parênteses. Essa é a outra maneira de renomear uma coluna no CTE.

O nome do segundo CTE é silver e ele tem duas colunas: winner_name e silver_medals. Observe que não colocamos a cláusula WITH antes do segundo CTE. A cláusula WITH é usada apenas uma vez antes da definição do primeiro CTE. Se tivermos mais CTEs para definir, precisaremos apenas de uma vírgula antes de iniciar a definição do(s) próximo(s) CTE(s).

WITH gold(winner_name,gold_medals) AS
(
  SELECT 
    winner_name, 
    count(*)
  FROM olympic_games
  WHERE medal_type = 'Gold'
  GROUP BY winner_name
), 
silver(winner_name,silver_medals) AS
(
  SELECT 
    winner_name,
    count(*)
  FROM olympic_games
  WHERE medal_type = 'Silver'
  GROUP BY winner_name
)
SELECT  
  g.winner_name, 
  g.gold_medals, 
  s.silver_medals
FROM gold g 
JOIN silver s 
ON g.winner_name = s.winner_name;

Na consulta T-SQL anterior, criamos dois CTEs: gold e silver com o winner_name e a quantidade de medalhas (ouro ou prata) conquistadas por cada jogador. Em seguida, na consulta principal, juntamos os dois CTEs como se fossem tabelas regulares usando uma cláusula JOIN. Como o JOIN sem palavras-chave funciona como um INNER JOIN, somente os registros para o mesmo winner_name em ambas as tabelas serão mostrados no resultado da consulta. A saída é mostrada abaixo:

Exemplo 4: Uma consulta do SQL Server com um CTE baseado em outro CTE

Em seguida, criaremos dois CTEs em uma consulta; no entanto, o segundo CTE será baseado no primeiro CTE. Suponha que queiramos que uma consulta obtenha os 3 principais países em relação à quantidade de medalhas conquistadas em um jogo olímpico. Não queremos repetir países, portanto, se a primeira e a segunda posições forem para o mesmo país, queremos mostrar esse país apenas uma vez. A consulta será a seguinte:

WITH medals AS
(
  SELECT 
    city, 
    year, 
    country, 
    COUNT(*) AS quantity_of_medals
  FROM olympic_games
  GROUP BY city, year, country
), 
country_best_game AS
(
  SELECT  
    country, 
    city, 
    year, 
    quantity_of_medals 
  FROM medals m1
  WHERE quantity_of_medals = ( 
    SELECT max(quantity_of_medals) 
    FROM medals m2
    WHERE  m1.country = m2.country
  )
)
SELECT TOP 3 country, 
  city, 
  year, 
  quantity_of_medals 
FROM country_best_game 
ORDER BY quantity_of_medals DESC;

Nessa consulta, criamos um CTE chamado medals com a quantidade de medalhas obtidas por cada país em cada jogo olímpico em que o país ganhou pelo menos uma medalha. Depois disso, calcularemos um segundo CTE chamado country_best_game (com base no primeiro CTE); ele tem uma linha para cada país com o número máximo de medalhas obtidas em um único jogo. Por fim, na consulta principal, selecionamos apenas os três países com o maior número de medalhas conquistadas em um único jogo. Os resultados da consulta estão abaixo:

Neste ponto, gostaria de sugerir o artigo How to Learn SQL Common Table Expressions (Como aprender expressões de tabela comuns do SQL), onde você pode ler sobre diferentes abordagens para aprender CTEs. Você também verá vários exemplos de consultas usando CTEs no SQL padrão.

Exemplo 5: uso de um CTE em um INSERT T-SQL

No T-SQL, os CTEs também podem ser usados nas instruções UPDATE, INSERT e DELETE. Como regra geral, qualquer comando SQL que permita um SELECT incorporado (por exemplo, uma instrução CREATE VIEW ) pode suportar um CTE. Vejamos um exemplo de um INSERT usando uma expressão de tabela comum em T-SQL.

Vamos supor que tenhamos uma tabela chamada country_medals_by_game com as colunas country, city, game, number_of_medals, e delta_with_previous_game. O conteúdo de cada coluna é claro, exceto pela coluna delta_with_previous_game. No entanto, ainda não usaremos essa coluna, por isso a explicaremos mais tarde. O INSERT para preencher a tabela é o seguinte:

WITH medals AS
(
  SELECT 
    city, 
    year, 
    country, 
    COUNT(*) AS medals_won
  FROM olympic_games
  GROUP BY city, year, country
)
INSERT INTO country_medals_by_game(city,year,country,quantity_of_medals)
SELECT city, year, country, medals_won
FROM medals;

No INSERT anterior, usamos um CTE chamado medals. Observe que o INSERT começa com a definição do CTE (você pode definir vários CTEs, se necessário). Depois que o CTE é definido, a instrução regular INSERT é iniciada. Neste exemplo, o INSERT usa uma instrução SELECT, que, por sua vez, acessa o CTE medals definido anteriormente.

Exemplo 6: Uso de um CTE em um SQL Server UPDATE

A coluna delta_with_previous_game armazena a diferença de medalhas conquistadas por um país em dois jogos olímpicos consecutivos. Se o país aumentou o número de medalhas conquistadas, essa coluna terá a diferença no número de medalhas como um valor positivo. Se o país ganhou menos medalhas do que no jogo olímpico anterior, a coluna terá um valor negativo. O UPDATE para preencher a coluna é:

WITH medals AS
(
  SELECT 
    city, 
    year, 
    country, 
    COUNT(*) AS medals_won
  FROM olympic_games
  GROUP BY city, year, country
)
UPDATE country_medals_by_game 
SET delta_with_previous_game = (
 SELECT COALESCE(quantity_of_medals - COALESCE(prevgame.medals_won,0),0)
 FROM medals prevgame
 WHERE country_medals_by_game.year = prevgame.year + 4
  AND country_medals_by_game.country = prevgame.country
);

Neste UPDATE, começamos com uma cláusula WITH para definir o mesmo medals CTE que estamos usando. Quando a seção de definição do CTE termina, a instrução UPDATE começa. Na cláusula SET, usamos uma subconsulta para calcular a diferença de medalhas conquistadas em dois jogos olímpicos consecutivos. Observe que a subconsulta acessa o CTE medals e a condição ...

country_medals_by_game.year = prevgame.year + 4

... é fazer a correspondência de uma linha em country_medals_by_game com a linha de medalhas do jogo olímpico anterior (que ocorreu quatro anos antes). Um aspecto interessante a ser observado é o seguinte: Para os países que não participaram de dois jogos contíguos, definimos a coluna delta_with_previous_game como NULL. Isso indica que não podemos calcular a diferença; usar um zero para essa coluna é incorreto porque significaria que o país participou do jogo anterior, o que não aconteceu.

Exemplo 7: Uso de CTEs para Consultas Recursivas em T-SQL

No SQL Server, é comum ter tabelas que representam hierarquias de dados (como funcionário-gerente, parte-subparte ou pai-filho). Para percorrer essas hierarquias em qualquer direção - de cima para baixo ou de baixo para cima - o SQL Server usa uma construção chamada CTEs recursivos.

Para ter uma hierarquia de dados em nosso banco de dados Olympic, adicionaremos um par de colunas à tabela olympic_games. Vamos supor que queiramos identificar as medalhas que representam um recorde mundial. Podemos adicionar uma coluna de texto chamada record e defini-la com true quando uma medalha for associada a um novo recorde mundial.

Além disso, sabemos que todo recorde quebra um recorde anterior, portanto, adicionaremos outra coluna chamada previous_record_medal onde colocaremos o medal_id do recorde anterior. Agora temos uma hierarquia de dados para ler com uma consulta recursiva; vamos mostrar uma visualização parcial da tabela olympic_games com suas novas colunas:

Digamos que queiramos obter um relatório com a lista de recordes mundiais na maratona masculina. Podemos começar mostrando o recorde mundial atual e, em seguida, o recorde imediatamente anterior, e assim por diante. Precisaremos de um CTE recursivo para percorrer a hierarquia dos recordes da maratona masculina. Ele terá a seguinte aparência:

WITH record_history (medal_id, year, winner, country, prev_record_medal_id) AS
(
  SELECT 
    medal_id, 
    year, 
    winner_name, 
    country, 
    previous_record_medal
  FROM olympic_games
  WHERE sport = 'Marathon Men' AND record = 'true'

  UNION ALL

  SELECT 
    og.medal_id,
    og.year,
    og.winner_name,
    og.country, 
    og.previous_record_medal
  FROM olympic_games og 
  JOIN record_history mrh 
  ON og.medal_id = mrh.prev_record_medal_id
)
SELECT * 
FROM record_history;

O CTE record_history é obtido como resultado de um UNION ALL. A primeira consulta no UNION é para obter o recorde mundial atual; observe a condição record = true. Após o UNION ALL, temos outra consulta que obtém todos os recordes anteriores da maratona masculina. A chave para conectar uma linha de registro de medalha com a linha de registro de medalha anterior é a condição:

og.medal_id = mrh.prev_record_medal_id

Os resultados da consulta estão abaixo:

Para evitar um loop infinito em um CTE recursivo, há um limite para o número de invocações permitidas. No SQL Server, esse limite é definido por padrão como 100. No entanto, você pode alterar esse limite usando o parâmetro MAXRECURSION no final da consulta recursiva.

Se você quiser se aprofundar mais nas consultas recursivas, sugiro os artigos How to Write a Recursive CTE in SQL Server e Do it in SQL: Recursive SQL Tree Traversal. Você encontrará vários exemplos e abordagens diferentes para explicar as consultas recursivas.

Os CTEs são um recurso poderoso da linguagem T-SQL

Neste artigo, abordamos como usar CTEs T-SQL para simplificar consultas complexas para bancos de dados do SQL Server. Antes de encerrar, gostaria de sugerir alguns artigos relacionados ao SQL Server. O primeiro é Top 5 SQL CTE Interview Questions, no qual você pode encontrar dicas sobre como gerenciar uma entrevista para um emprego na área de SQL. Outro artigo interessante é How to Install Microsoft SQL Server 2019 and SQL Server Management Studio, onde você pode encontrar ajuda para instalar o SQL Server.

Por fim, gostaria de incentivá-lo a conferir o curso Consultas Recursivas no MS SQL Server. Você pode aprender como processar árvores e gráficos no T-SQL e como organizar suas consultas de forma eficaz. Desenvolva suas habilidades e aumente seus ativos!