CTE: o que é e para que serve no SQL

Introdução: CTE — o que é e para que serve no SQL

Às vezes montar uma consulta SQL pode se tornar uma tarefa complexa, principalmente quando é preciso juntar várias demandas em um só código. É bem comum quando construímos uma consulta ter que unir tabelas, fazer agregações, renomear colunas, ordenar e agrupar tudo em uma única consulta mais robusta.

Para a realização de uma conferência de uma query em SQL, por exemplo, a gigantesca quantidade de informações possíveis de serem manipuladas pode gerar nesse processo um ambiente desafiador para o profissional, a ponto de ter aquela sensação de não saber bem por onde começar.

Se você já passou por isso, ou por algo parecido, fica nesse artigo para aprender um recurso que te ajuda a dividir a consulta em etapas para melhorar a conferência e a organização de ideias. Nesse artigo, vamos explorar uma estrutura de código usando a linguagem SQL para a integração de um maior desempenho pela simplificação de consultas complexas de dados.

O que é CTE?

Sigla para Common Table Expression, em tradução livre podemos dizer que CTE é uma expressão de tabela comum. Assim, podemos defini-la como um resultado temporário. Ou seja, depois de criada, estruturada e utilizada, a CTE é apagada.

Para estabelecer a CTE no SQL Server podemos utilizar a estrutura do código seguinte:

WITH nome_expressão [( nome_colunas [,...n] )]
AS
(CTE_definição_da_consulta)

Nessa sintaxe, temos o WITH no início do código para que se possa nomear a CTE. Diante disso, a inclusão do nome é realizada por intermédio donome_expressão, onde logo em seguida é declarado o nome das colunas desta tabela temporária. Após nomeado, é necessário escrever AS e declarar a consulta, pois essas informações são essencialmente utilizadas no preenchimento da CTE.

Para que serve?

Se você nunca ouviu falar em CTE, pode ter se questionado: “Se sua principal característica é ser temporária, para que serve então a CTE no meu código dentro do banco de dados?”

Usada para simplificar a escrita de consultas complexas, ajuda a dividir a lógica em partes menores tornando-as mais fáceis para o entendimento.

Aplicando em exemplo, vamos considerar a consulta abaixo que tem como objetivo calcular o total de vendas por mês e ano.

SELECT MONTH(data_pedido) AS mes, YEAR(data_pedido) AS ano, SUM(preco_total) AS total_vendas
FROM pedidos
GROUP BY MONTH(data_pedido), YEAR(data_pedido)
ORDER BY ano, mes; 

Nesse cenário desenhado, podemos separar essa consulta em duas partes:

(1) fazer agregação e agrupar os campos, ou seja, extrair o mês, ano e soma dos preços agrupados pelos lapsos temporais;

(2) aplicar a ordenação por ano e mês para facilitar a legibilidade da tabela.

Para separar a lógica de construção da consulta, é possível atribuir a primeira parte a uma CTE e a segunda para fazer uma consulta da CTE criada no passo 1. Assim, temos a possibilidade de codar da seguinte maneira:

WITH venda_por_mes (mes, ano, total_vendas) AS (
    SELECT MONTH(data_pedido), YEAR(data_pedido), SUM(preco_total)
    FROM pedidos
    GROUP BY MONTH(data_pedido), YEAR(data_pedido)
)
SELECT mes, ano, total_vendas
FROM venda_por_mes
ORDER BY mes, ano;

Nesse último exemplo, a CTE foi nomeada como venda_por_mes e referenciada na query seguinte, logo após o SELECT. Escrevendo dessa forma, foi possível dividir a lógica da consulta, tornando o código mais legível, organizado e mais fácil de ser mantido.

Ainda pensando no ditado “dividir para conquistar”, no exemplo acima conseguimos pensar em duas etapas para construção da consulta. Embora possa parecer simples, ainda sim em algumas situações a modelagem do banco de dados pode tornar a construção da consulta mais desafiadora, onde teríamos que inclusive dividir a lógica em outras partes. De todo modo, não se desespere! Para esse caso, podemos usufruir das CTE aninhadas.

As CTEs são aninhadas quando podem ser definidas uma seguida da outra, o que permite construir consultas complexas com várias etapas. Baseada na CTE anterior, cada CTE aninhada é também usada como base para a próxima etapa da consulta.

Diante disso, vamos agora comparar e explorar toda a integração dos comandos a seguir, para que então possamos verificar detalhadamente as informações que nela são dispostas.

WITH
departamentos (id, nome) AS (
    SELECT id, nome
    FROM departamento
),
colaboradores (id, nome, id_depto) AS (
    SELECT id, nome, id_depto
    FROM colaborador
    JOIN departamentos
	ON colaborador.id_depto = departamentos.id
),
vendas (id_colaborador, venda_total) AS (
    SELECT id_colaborador, SUM(preco_total)
    FROM pedidos
    JOIN colaboradores
	ON pedidos.id_colaborador = empregados.id
    GROUP BY id_colaborador
)
SELECT nome, venda_total
FROM vendas
JOIN colaboradores
ON vendas.id_colaborador = colaboradores.id;

Como se pode analisar, o código acima tem como principal objetivo mostrar cada pessoa colaboradora de uma empresa e o total de vendas realizadas por elas. Um exemplo de saída dessa consulta seria:

Nessa construção, a consulta foi feita em etapas sequenciais, organizando em três tabelas diferentes (departamentos, colaboradores e vendas) na qual uma faz referência a outra utilizando seu apelido (alias - AS). Na última etapa, por exemplo, a consulta seleciona as colunas “nome”, “venda_total” , de modo que a utilização do JOIN faz referência a tabela “colaboradores”. Construída baseada nas etapas aninhadas anteriormente, essas ações intencionais são realizadas para alcançar o nosso propósito estipulado.

Desse modo, além de ser um ótimo recurso para ajudar a construir e testar consultas complexas, usa característica temporária como uma tabela virtual. Ou seja, não são armazenados dados permanentemente, somente enquanto a query estiver sendo executada.

Onde ter acesso?

O recurso de Common Table Expressions, CTE, foi adicionado ao SQL com a implementação da cláusula WITH no SQL-1999 (SQL3). Ele se tornou disponível em vários SGBDs, Sistemas Gerenciadores de Banco de Dados, como o SQL Server, PostgreSQL, Oracle, MySQL e outros. No entanto, a disponibilidade e suporte de CTE variam de acordo com a versão do SGBD, e é comum que a maioria das versões mais recentes suporte essas funcionalidades.

Conclusão

E aí, Curtiu essa dica? Aqui na Alura temos muitos outros conteúdos para te ajudar a estruturar suas consultas em SQL! Vou deixar aqui embaixo algumas sugestões:

• Não dá para escrever uma consulta sem entender como as tabelas se relacionam? Para isso indico a Formação de modelagem para explorar e ficar por dentro desse assunto.
• Prefere se aprofundar em um SGBD, então as indicações são a Formação de SQL Server e a Formação de PostegreSQL.

🤿 Bora mergulhar em tecnologia? Venha estudar conosco!

Créditos:

• Redação e conteúdo: Ana Duarte
• Revisão técnica: David Neves
• Revisão didática: Cláudia Machado