Implementando tabelas derivadas no Sql Server

Tabelas
derivadas são tabelas virtuais montadas em tempo de execução dentro de
um statement SQL. Este tipo de implementação pode ser bastante útil a
nível de desenvolvimento em determinadas situações.

Para melhor entendermos esta implementação, vamos analisar o script abaixo, implementado no banco de dados Northwind, distribuído com a instalação do Microsoft SQL Server.

select dados.*,c.companyname from (<br />select<br />OrderId,<br />OrderDate,<br />CustomerId,<br />sum(freight) as Freight<br />from orders<br />group by OrderId,<br />OrderDate,<br />CustomerId<br />) as dados<br />left join customers c on (c.CustomerId=dados.CustomerId)

O
script acima mostra um rápido exemplo de implementação de uma tabela
derivada. Podemos verificar que a query é aninhada entre parênteses e
nomeada com um “pipe”, para ser referenciada na query principal. “Mas, como isso me ajuda?”, você deve estar se perguntando…

Vejamos a seguinte situação: vamos imaginar que a nossa empresa Northwind solicitou
um relatório com o total de pedidos realizados em 1996, agrupados por
cliente. Alguns desenvolvedores poderiam chegar ao seguinte exemplo
para implementar esta demanda:

select<br />Customers.CustomerID, Customers.CompanyName,<br />count(Orders.OrderID) as TotalOrders<br />from<br />Customers<br />left outer join Orders on Customers.CustomerID = Orders.CustomerID<br />where<br />year(Orders.OrderDate) = 1996<br />group by<br />Customers.CustomerID, Customers.CompanyName

A
princípio uma implementação bastante simples e sem nenhuma surpresa.
Porém, repare que os clientes que não realizaram pedidos em 1996 não
estão listados. Geralmente, os clientes que NÃO realizaram
pedidos são os que geram o maior interesse por quem solicita o
relatório e a questão é realmente como adicioná-los na listagem.

Se você está achando que apenas uma check com a função Isnull resolveria o problema, está enganado. Veja a implementação abaixo:

select<br />Customers.CustomerID, Customers.CompanyName,<br />count(Orders.OrderID) as TotalOrders<br />from<br />Customers<br /><br />left outer join Orders on Customers.CustomerID = Orders.CustomerID<br /><br />where<br />(<br />year(Orders.OrderDate) = 1996<br />or<br />Orders.OrderDate is null)<br /><br />group by<br /><br />Customers.CustomerID, Customers.CompanyName

Execute a query e perceba que os clientes que não realizaram
pedidos continuam fora da listagem. Existem várias soluções para este
problema e todas são válidas. Algumas mais simples e outras mais
complexas, porém, como o objetivo deste artigo é exemplificar o
desenvolvimento de tabelas derivadas, vamos implementar um script
utilizando-as.

select<br />Customers.CustomerID, Customers.CompanyName,<br />count(dOrders.OrderID) as TotalOrders<br />from<br />Customers<br />left outer join<br />/* início da tabela derivada */<br />(<br />select<br />*<br />from<br />Orders<br />where<br />year(Orders.OrderDate) = 1996<br />) as dOrders<br />/* fim da tabela derivada */<br />on<br />Customers.CustomerID = dOrders.CustomerID<br />group by<br />Customers.CustomerID, Customers.CompanyName

Nossa tabela virtual, nomeada de “dOrders”, retorna todos os
pedidos realizados em 1996, e nossa query principal seleciona todos os
nossos clientes do banco, realizando um “left outer join” na tabela
derivada. O resultado fica então correto, atendendo os requisitos do
relatório listando os clientes com zero pedidos em 1996.

Antes que alguém me corrija, informando maneiras melhores de
implementar este relatório sem o uso de tabelas derivadas, repito o que
foi dito anteriormente:

Existem várias soluções para este
problema, e todas são válidas. Algumas mais simples e outras mais
complexas, porém, como o objetivo deste artigo é exemplificar o
desenvolvimento de tabelas derivadas, decidi implementar este exemplo,
que me pareceu bastante correto para a finalidade desejada.

Mas, para registrar, a query abaixo retorna o mesmo resultado da query acima, sem o uso da tabela derivada.

<br />SELECT<br />Customers.CustomerID,<br />Customers.CompanyName,<br />ISNULL(COUNT(Orders.OrderID),0) as TotalOrders<br />FROM<br />Customers LEFT OUTER JOIN Orders ON (Customers.CustomerID = Orders.CustomerID)<br />AND (YEAR(Orders.OrderDate) = 1996)<br />GROUP BY<br />Customers.CustomerID,<br />Customers.CompanyName

Até a próxima!