O Que é a Cláusula WITH em SQL?

Neste artigo, você aprenderá sobre a cláusula SQL WITH, também conhecida como expressão de tabela comum (CTE). Analisaremos exemplos para demonstrar alguns de seus usos em relação aos benefícios.

Introdução à Cláusula SQL WITH

A cláusula WITH em SQL foi introduzida no SQL padrão para simplificar consultas longas e complexas, especialmente aquelas com JOINs e subconsultas. Frequentemente chamada de refatoração CTE ou subconsultas, uma cláusula WITH define um conjunto de dados temporários que podem ser referenciados em consultas subsequentes.

A melhor maneira de aprender a cláusula WITH em SQL é praticando. Para isso, recomendo o curso interativo da LearnSQL.com.br Consultas Recursivas. Ele contém mais de 100 exercícios que ensinam o uso da cláusula WITH, indo desde o básico até tópicos avançados como consultas recursivas usando WITH.

A cláusula WITH é considerada "temporária" porque o resultado não é armazenado permanentemente em nenhum lugar no esquema do banco de dados. Ela atua como uma visão temporária que só existe durante a execução da consulta, ou seja, só está disponível durante o escopo de execução das declarações SELECT, INSERT, UPDATE, DELETE e MERGE. Ela só é válida na consulta à qual pertence, possibilitando uma melhoria na estrutura de uma declaração sem poluir o namespace global.

A cláusula WITH é utilizada em consultas nas quais não seria adequado trabalhar com uma tabela derivada. Portanto, ela é considerada uma alternativa mais limpa às tabelas temporárias. Simplificando, a principal vantagem da cláusula WITH é que ela ajuda a organizar e simplificar as longas e complexas consultas hierárquicas, dividindo-as em pedaços menores e mais legíveis.

A cláusula WITH foi introduzida no padrão SQL pela primeira vez em 1999, e agora está disponível em todos os principais SGBDs relacionais. Algumas aplicações comuns das CTEs em SQL incluem:

• Fazer referência a uma tabela temporária várias vezes em uma única consulta.
• Realizar agregações multiníveis, como encontrar a média dos máximos.
• Realizar um cálculo idêntico várias vezes dentro do contexto de uma consulta maior.
• Servir como uma alternativa para criar uma visão no banco de dados.

Vamos ver um exemplo rápido e simples da cláusula WITH abaixo usando a tabela InformacoesPedido do conhecido banco de dados Northwind. O objetivo é retornar a quantidade média encomendada por ID_Produto:

CONSULTA:

WITH cte_Quantidade
AS
(SELECT
	SUM(Quantidade) as Total
FROM InformacoesPedido
GROUP BY ID_Produto)

SELECT
	AVG(Total) quantidade_media_produto
FROM cte_Quantidade;

RESULTADO:

Número de Registros: 1

Se você executasse a consulta sem a cláusula WITH e, em vez disso, usasse uma subconsulta, a consulta ficaria mais ou menos assim:

CONSULTA:

SELECT
	AVG(Total) quantidade_media_produto
FROM
(SELECT
SUM(Quantidade) as Total
FROM InformacoesPedido
GROUP BY ID_Produto)

Embora aparentemente não pareça haver muita diferença entre as duas consultas, a estrutura mais destrinchada que a cláusula WITH possibilita será muito útil à medida que suas consultas forem aumentando em tamanho e camadas hierárquicas. Veremos um exemplo disso abaixo, na forma de uma cláusula com um aninhamento. Você pode encontrar mais exemplos em um de nossos artigos anteriores sobre o tema - Explicando CTEs com Exemplos.

A Sintaxe da Cláusula WITH

A sequência geral de passos para executar uma cláusula WITH é:

1. Inicie com o WITH
2. Especifique o nome da expressão para a consulta a ser definida.
3. Opcional: Especifique os nomes das colunas separados por vírgulas.
4. Após atribuir o nome da expressão, digite o comando AS. As expressões, neste caso, são os conjuntos de resultados nomeados que você usará mais tarde na consulta principal para se referir à CTE.
5. Escreva a consulta necessária para produzir o conjunto de dados temporário desejado.
6. Se trabalhar com mais de uma CTE ou cláusula WITH, inicie cada um deles separado por uma vírgula e repita os passos 2-4. Tal arranjo também é conhecido como uma cláusula WITH aninhada.
7. Consulte as expressões definidas acima em uma consulta subsequente usando SELECT, INSERT, UPDATE, DELETE ou MERGE

A sintaxe para implementar uma cláusula WITH é mostrada no pseudocódigo abaixo:

--CTE
WITH nome_expressao_1 (coluna_1, coluna_2,…,coluna_n)
AS
(CTE definicao consulta 1),
nome_expressao_2 (coluna_1, coluna_2,…,coluna_n)
AS
(CTE definicao consulta 2)

--Consulta final usando CTE
SELECT expressao_A, expressao_B, ...
FROM nome_expressao_2

A cláusula WITH é uma substituta para as subconsultas normais. O ponto principal é que, diferente das subconsultas, ao usar a cláusula WITH para gerar uma CTE você pode reutilizar o mesmo resultado várias vezes em seu código.

Como vemos acima, os parâmetros principais para executar uma cláusula WITH são:

• WITH: Usada para criar uma CTE, ou o(s) conjunto(s) de dados temporário(s).
• nome_expressao (coluna_1, …, coluna_n): O nome do conjunto de dados temporário virtual que será usado na consulta principal, e coluna_1 a coluna_n são os nomes das colunas que podem ser usados nas etapas subsequentes da consulta.
• AS (....): Esta seção define a consulta que irá preencher a CTE nome_expressao. Se você implementar um CTE aninhado, a consulta dentro do segundo AS provavelmente se referirá à primeira CTE.
• SELECT expressao_A, expressao_B FROM nome_expressao: Esta seção especifica a principal consulta externa onde a declaração SELECT (ou as declarações INSERT, UPDATE, DELETE ou MERGE) é usada em uma ou mais das CTEs geradas para posteriormente gerar o resultado pretendido.

Todos os parâmetros mencionados acima são obrigatórios. Você pode escolher usar as cláusulas WHERE, GROUP BY, ORDER BY e/ou HAVING, conforme necessário.

Quando uma consulta com uma cláusula WITH é executada, primeiro a consulta mencionada dentro da cláusula é avaliada e a saída desta avaliação é armazenada dentro de uma relação temporária. Em seguida, a consulta principal associada à cláusula WITH é então executada, utilizando a relação temporária produzida.

No exemplo a seguir usaremos uma cláusula aninhada WITH com a tabela InformacoesPedido mostrada anteriormente. Uma cláusula aninhada WITH, ou CTEs aninhadas, envolve duas CTEs dentro da mesma consulta, a segunda fazendo referência à primeira.

OBJETIVO: Devolver o número médio de pedidos, ou vendas feitas, por ID_Funcionario para o ID_Fornecedor identificado pelo número 2 e o ID_Fornecedor número 3.

PERGUNTA:

--Primeira CTE
WITH cte_vendas
AS
(SELECT
	ID_Funcionario,
	COUNT(ID_Pedido) as Pedidos,
	ID_Fornecedor
FROM Pedidos
GROUP BY ID_Funcionario, ID_Fornecedor),

--Segunda CTE (aninhada)
fornecedor_cte
AS
(SELECT *
FROM cte_vendas
WHERE ID_Fornecedor=2 or ID_Fornecedor=3)

--Consulta usando CTE
SELECT
	ID_Fornecedor, AVG(Pedidos) pedido_medio_por_funcionario
FROM
fornecedor_cte
GROUP BY ID_Fornecedor;

RESULTADO:

Número de Registros: 2

Aqui, calculamos o número médio de pedidos por funcionário, mas apenas para o ID_Fornecedor identificado pelo número 2 e o ID_Fornecedor de número 3. Na primeira CTE, cte_vendas, o número de pedidos é contado e agrupado com base em ID_Funcionario e ID_Fornecedor. Na segunda CTE, fornecedor_cte, fazemos referência à primeira CTE e definimos as condições ID_Fornecedor usando uma cláusula WHERE. Em seguida, na consulta principal, nos referimos apenas à segunda CTE, fornecedor_cte, para calcular a média de pedidos por funcionário, com base no ID_Fornecedor.

Outras nuances da sintaxe associada às cláusulas SQL WITH e CTEs são detalhadas no Módulo #2 do curso Consultas Recursivas, que também contém diversos exemplos mais avançados em detalhes.

Usos da Cláusula SQL WITH

Então, quando é realmente necessário usar uma cláusula WITH? Bem, existem alguns exemplos de usos únicos. A maioria deles visa a conveniência e facilidade de desenvolvimento e manutenção de consultas.

As principais aplicações e benefícios associados ao uso de CTEs em SQL podem ser resumidos em:

• Melhora a legibilidade do código - A programação letrada ou alfabetizada é uma abordagem introduzida por Donald Kuth que visa organizar o código-fonte na ordem da lógica humana, de forma que ele possa ser entendido com o mínimo de esforço, sendo lido como um livro narrativo, de forma sequencial. É exatamente nesse ponto que a cláusula SQL WITH é útil, criando tabelas com nomes virtuais e dividindo computações maiores em partes menores, que podem então ser combinadas posteriormente na consulta final SELECT ou outra declaração, em vez de serem colocadas juntas em um grande bloco.
• Melhora a manutenção do código - A manutenção é uma questão de legibilidade. Conforme o tempo passa e suas consultas e bancos de dados aumentam, sempre haverá a necessidade de depurar e solucionar problemas - um código mais fácil de ler é mais fácil de manter!
• Alternativa a uma visualização - CTEs podem substituir as visualizações e podem selecionar (SELECT), inserir (INSERT), atualizar (UPDATE), deletar (DELETE) ou mesclar (MERGE). Isto pode ser particularmente útil se você não tiver autorização no sistema para criar um objeto de visualização ou se você não quiser criar uma visualização que será usada em apenas uma consulta.
• Superar limitações das declarações - As CTEs ajudam a transpor restrições como as limitações da declaração SELECT, por exemplo, realizando um agrupamento (GROUP BY) usando funções não determinísticas.
• Processamento de estruturas hierárquicas - Esta é uma das aplicações mais avançadas das CTEs, e é realizada através das chamadas CTEs recursivas. Consultas recursivas podem fazer referência a si mesmas, permitindo que você trabalhe com modelos hierárquicos complexos. Mais sobre isto abaixo.

Há mais alguns exemplos de uso das CTEs discutidos em um de nossos artigos anteriores "Quando usar CTEs". O curso sobre Consultas Recursivas da LearnSQL.com.br ajudará a destrinchar todo o assunto de CTEs com passos detalhados, para que você consiga dominar o assunto com exercícios práticos.

A Cláusula Recursiva WITH

Os exemplos acima usam cláusulas não-recursivas. As CTEs recursivas são uma das funcionalidades mais avançadas da cláusula WITH, permitindo que ela faça referência a si mesma dentro de uma CTE. Isto facilita consideravelmente a passagem dos usuários por estruturas hierárquicas de dados complexas, tais como lista de materiais e organogramas.

Se você já possui familiaridade com o atributo de recursividade na programação, a função recursiva da cláusula WITH também incorpora um caso base e a etapa recursiva.

Considerações Finais sobre a Cláusula SQL WITH

Neste artigo, falamos sobre a sintaxe básica e alguns exemplos de como e quando você pode definir e usar as cláusulas WITH ou CTEs. Para entender melhor como implementar essas cláusulas no seu próprio código, você precisa colocar o conhecimento em prática! Para isso, sugiro o curso Consultas Recursivas aqui da LearnSQL.com.br. No curso, você encontrará mais exemplos práticos e aplicações exclusivas da cláusula WITH, com tutoriais interativos sobre como utilizar CTEs (recursivas e não recursivas) no seu dia a dia de trabalho.

Uma vez que você tenha entendido bem o uso das cláusulas WITH, vai se surpreender com o quanto seus scripts SQL podem melhorar!