Artigos Banco de Dados Subqueries – Parte I

Subquery é um comando SELECT que foi "embutido" noutro comando SELECT, UPDATE, DELETE ou dentro de outra subquery.

A finalidade da subquery é retornar um conjunto de linhas para a query principal

Utilização

Tipicamente utilizamos subqueries na filtragem de pesquisas (=cláusula WHERE) nas cláusulas IN() e EXISTS(), mas subqueries também podem aparecer também na cláusula FROM ou como substituto de expressões. Exemplos:

  • Utilizando uma subquery em conjunto com a cláusula IN(). 
    
	select *
	from [order details]
	where orderid IN (select orderid from orders where CustomerID='VINET')
    OrderID ProductID UnitPrice Quantity Discount
    10248 11 14.0000 12 0.0
    10248 42 9.8000 10 0.0
    10248 72 34.8000 5 0.0
    10274 71 17.2000 20 0.0
    10274 72 27.8000 7 0.0
    10295 56 30.4000 4 0.0
    10737 13 6.0000 4 0.0
    10737 41 9.6500 12 0.0
    10739 36 19.0000 6 0.0
    10739 52 7.0000 18 0.0
  • Utilizando uma subquery em conjunto com a cláusula EXISTS(). 
    • 
select *
from [order details] od
where EXISTS (select orderid from orders where orderid = od.orderid and CustomerId='VINET')
    OrderID ProductID UnitPrice Quantity Discount
    10248 11 14.0000 12 0.0
    10248 42 9.8000 10 0.0
    10248 72 34.8000 5 0.0
    10274 71 17.2000 20 0.0
    10274 72 27.8000 7 0.0
    10295 56 30.4000 4 0.0
    10737 13 6.0000 4 0.0
    10737 41 9.6500 12 0.0
    10739 36 19.0000 6 0.0
    10739 52 7.0000 18 0.0
  • Utilizando uma subquery como "alvo" da cláusula FROM 
    • 
select top 5 o.orderid,o.customerid,od.total_quantity
from
(
   select orderid,total_quantity=sum(quantity)
   from [order details]
   group by orderid
) od
inner join orders o
on o.orderid=od.orderid
    orderid customerid total_quantity
    10248 VINET 27
    10249 TOMSP 49
    10348 WANDK 40
    10349 SPLIR 24
    10350 LAMAI 33
  • Utilizando uma subquery para substituir uma expressão 
    • 
select top 5
	orderid, productid, unitprice, 
	avg_price=(select avg(unitprice) from [order details])
from [order details]
    orderid productid unitprice avg_price
    10248 11 14.0000 26.2185
    10248 42 9.8000 26.2185
    10248 72 34.8000 26.2185
    10249 14 18.6000 26.2185
    10249 51 42.4000 26.2185

Subqueries Correlatas

Quando uma subquery referencia colunas da query principal, recebe o nome de Subquery Correlata.

Diferentemente das subqueries convencionais, a Subquery Correlata será executada tantas vezes quantas forem as linhas de output da query principal, num processo de Nested Loop Join.

Exemplos de utilização de Subqueries Correlatas

Os exemplos a seguir foram executados no database-exemplo NorthWind tendo por base as tabelas Orders e Order Details.

  • Subquery Correlata na linha do SELECT: na query a seguir será listado, junto com os dados da header do pedido (=tabela Orders), a totalização de itens (=tabela Order Details).
    Nota: Note a relação de dependência com a query principal no filtro da subquery (... where od.orderId = o.orderId ...)
    
SELECT
customerId,
orderId,
qtde_itens_orderId = (select sum(quantity) from [order details] od     
whereod.orderId = o.orderId )
from Orders o
    customerId orderId qtde_itens_orderId
    ALFKI 10643 38
    ALFKI 10692 20
    ALFKI 10702 21
    ALFKI 10835 17
    ALFKI 10952 18
    ALFKI 11011 60
    ANATR 10308 6
    ANATR 10625 18
    ANATR 10759 10
  • Subquery Correlata no filtro WHERE: serão listados somente os pedidos cuja quantidade total de itens supere 250 unidades. 
    
SELECT
  customerId,
  orderId,
  qtde_itens_orderId = (select sum(quantity) from [order details] od     where
od.orderId = o.orderId )
from Orders owhere (select sum(quantity) from [order details] od   
where od.orderId = o.orderId ) > 250
order by 3
    customerId orderId qtde_itens_orderId
    QUICK 10658 255
    ERNSH 10990 256
    SAVEA 10612 263
    SAVEA 10678 280
    QUICK 10515 286
    SAVEA 10847 288
    SAVEA 11030 330
    ERNSH 10895 346
  • Subquery Correlata na cáusula HAVING: no batch a seguir o total de vendas/ano é confrontado com um valor previamente armazenado numa tabela de metas de venda (=projecao). Note que a comparação acontece na clausule HAVING, comparando o totalizador com o resultado da subquery. 
    
	create table projecao_
( 
  ano int,
  vlr_total_vendas dec(10,2)
)
insert into projecao_values (1996,250000)
insert into projecao_values (1997,630000)
insert into projecao_values (1998,500000)
select Ano=year(orderdate), vlr_total_vendas=sum(unitprice *   quantity)
from orders o
inner join
     [order details] od
on o.orderId = od.orderId
group by year(orderdate)
having sum(unitprice * quantity) >=

        ( select vlr_total_vendas from projecao_where
               ano=year(orderdate) )
    Ano vlr_total_vendas
    1997 658388.7500
  • Subquery Correlata no comando UPDATE: a coluna vlr_total, criada na tabela Orders será atualizada a partir da totalização dos itens.
    • 
alter table orders drop column vlr_total
go
alter table orders add vlr_total dec(10,2) 
go
update orders 
   set vlr_total=
   ( select sum(unitprice * quantity) 
     from [order details] od 
     where od.OrderId = orders.orderId 
     group by od.orderId 
   )
go

Conclusão

Subqueries correlatas agregam versatilidade às queries, permitindo que joins complexos sejam resolvidos com apenas um comando, sem a criação de tabelas temporárias. Um único detalhe: subqueries correlatas muitas vezes podem ser executadas como um join convencional; nesses casos vale a pena olhar o plano de execução dos dois formatos (subquery correlata X join), optando pelo mais eficiente.