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().
- Utilizando uma subquery como "alvo" da cláusula FROM
- Utilizando uma subquery para substituir uma expressão
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 |
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 |
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 ocustomerId 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 3customerId 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.
