Usando expressões de tabela comuns
Podemos pensar em uma expressão de tabela comum (CTE) como sendo um conjunto de resultados temporário definido no escopo de execução de uma única instrução SELECT, INSERT, UPDATE, DELETE ou CREATE VIEW. Uma CTE é semelhante a uma tabela derivada que não é armazenada como um objeto e permanece apenas durante a duração de uma consulta. Ao contrário de uma tabela derivada, uma CTE pode ser auto-referenciada e pode ser referenciada várias vezes na mesma consulta.
Uma CTE pode ser usada para:
Criar uma consulta recursiva. Para obter mais informações, consulte Consultas recursivas que usam expressões de tabelas comuns.
Substituir uma exibição quando o uso geral da exibição não for necessário, ou seja, você não precisa armazenar a definição nos metadados.
Habilitar o agrupamento por uma coluna que seja derivada de uma subseleção escalar ou uma função que não seja determinística ou tenha acesso externo.
Referenciar a tabela resultante várias vezes na mesma instrução.
Usar uma CTE oferece as vantagens de legibilidade aprimorada e facilidade de manutenção de consultas complexas. A consulta pode ser dividida em blocos de construção lógicos simples e separados. Esses blocos simples podem ser usados para criar CTEs interinas mais complexas, até que o resultado final seja gerado.
As CTEs podem ser definidas em rotinas definidas pelo usuário, como funções, procedimentos armazenados, gatilhos ou exibições.
Estrutura de uma CTE
Uma CTE é constituída de um nome de expressão representando a CTE, uma lista de colunas opcional e uma consulta que define a CTE. Após a definição da CTE, ela poderá ser referenciada como uma tabela ou exibição em uma instrução SELECT, INSERT, UPDATE ou DELETE. Uma CTE também pode ser usada em uma instrução CREATE VIEW como parte da instrução SELECT que a define.
A estrutura de sintaxe básica de uma CTE é:
WITH expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )
A lista de nomes de colunas será opcional somente se forem fornecidos nomes distintos para todas as colunas resultantes na definição da consulta.
A instrução para executar a CTE é:
SELECT <column_list>
FROM expression_name;
Exemplo
O exemplo a seguir mostra os componentes da estrutura da CTE: nome da expressão, lista de colunas e consulta. A expressão da CTE Sales_CTE tem três colunas (SalesPersonID, NumberOfOrders e MaxDate) e está definida como o número total de ordens de venda e data da ordem de venda mais recente na tabela SalesOrderHeader para cada vendedor. Quando a instrução é executada, a CTE é referenciada duas vezes: uma vez para retornar as colunas selecionadas para o vendedor e novamente para recuperar detalhes semelhantes para o gerente do vendedor. Os dados para o vendedor e o gerente são retornados em uma única linha.
USE AdventureWorks;
GO
WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
AS
(
SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID
)
SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
E.ManagerID, OM.NumberOfOrders, OM.MaxDate
FROM HumanResources.Employee AS E
JOIN Sales_CTE AS OS
ON E.EmployeeID = OS.SalesPersonID
LEFT OUTER JOIN Sales_CTE AS OM
ON E.ManagerID = OM.SalesPersonID
ORDER BY E.EmployeeID;
GO
Eis um conjunto parcial de resultados:
EmployeeID NumberOfOrders MaxDate ManagerID NumberOfOrders MaxDate
----------- -------------- ---------- --------- -------------- ----------
268 48 2004-06-01 273 NULL NULL
275 450 2004-06-01 268 48 2004-06-01
276 418 2004-06-01 268 48 2004-06-01
277 473 2004-06-01 268 48 2004-06-01