Tabelas Derivadas - Derived Tables

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 (
select
OrderId,
OrderDate,
CustomerId,
sum(freight) as Freight
from orders
group by OrderId,
OrderDate,
CustomerId
) as dados
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
Customers.CustomerID, Customers.CompanyName,
count(Orders.OrderID) as TotalOrders
from
Customers
left outer join Orders on Customers.CustomerID = Orders.CustomerID
where
year(Orders.OrderDate) = 1996
group by
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ÃOrealizaram 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 Isnullresolveria o problema, está enganado. Veja a implementação abaixo:

select
Customers.CustomerID, Customers.CompanyName,
count(Orders.OrderID) as TotalOrders
from
Customers

left outer join Orders on Customers.CustomerID = Orders.CustomerID

where
(
year(Orders.OrderDate) = 1996
or
Orders.OrderDate is null)

group by

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 este artigo é exemplificar o desenvolvimento de tabelas derivadas, vamos implementar um script utilizando-as.

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

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

Até a próxima!