Sub-Query Correlata and Ordinary

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Post Reply
Mr.Paulo
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 7
Joined: Sun, 03 Jun 2007 9:07 pm
Location: Barretos-SP

Personal,

Someone has any books or tutorial on sub-query correlates and ordinary!?!?!

Thanks
Hahu
Rank: Analista Sênior
Rank: Analista Sênior
Posts: 147
Joined: Thu, 16 Mar 2006 11:26 am
Location: São Paulo
O mundo gira muito!!

MR.Paulo,
Good afternoon friend,

follows the information:
Part II: Correlated Queries
Previous Article - Subcuries Part I - We have seen that a SubQuery is a SELECT command that was "built-in" on another command select, update, delete or inside another subquery. We also have seen that the purpose of the subquery is to return a set of lines to the main query.

Correlated subcubries

When a subquery reference speakers of the main query, you receive the name of Subquery Correlata.

Different from conventional sub-cases, the Correlata subquery will be performed as many times as the main query output lines in a Nested Loop Join process.

Examples of using correlated sub-cases

The following examples were executed in the Northwind database based on the Orders and Order Details tables.

· Subquery Correlata In the select line: Na Query below will be listed, along with the request header data (= Orders Table), the totalization of items (= Table Order Details).
PS: Note the dependency ratio with the main query in the subquery filter (... WHERE ODERID = O.ORDERID ...)

Select all

 
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 In the WHER filter: Only requests are listed whose total amount of items exceeds 250 units.

Select all

 
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 
---------------------------------------- -----------------------------

Select all

 
 
 
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 
 
(8 row(s) affected) 
 

· Subquery Correlata In Caucasula Having: No Batch Following Total Sales / Year is confronted with a previously stored value in a table of sale targets (= Project). Note that the comparison happens in the HAVING clause by comparing the totalizer with the result of the subquery.

Select all

 
 
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 
(1 row(s) affected) 

· Subquery Correlata In the Update command: The VLR_Total column, created in the Orders table will be updated from the totalization of the items.

Select all

 
 
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 
 
----------------------------------- --------------------------------- (830 row (s) affected)

Conclusion

Correlatory subcubers aggregate versatility to the queries, allowing complex joins to be resolved with only one command without the creation of temporary tables. A single detail: correlated subcursions can often be performed as a conventional join; In such cases it is worth looking at the implementation plan of the two formats (subquery correlata x join), opting for the most efficient.

Well, that was today's tip.

Until next time!

Read the first part of this article in: http://www.devmedia.com.br/articles/vie ... ?comp=2835
Any doubt, put here in the forum ..

ABS of your Friend hahu :) -m
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 18 guests