Personal,
Someone has any books or tutorial on sub-query correlates and ordinary!?!?!
Thanks
Sub-Query Correlata and Ordinary
-
- 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 ...)
· Subquery Correlata In the WHER filter: Only requests are listed whose total amount of items exceeds 250 units.
---------------------------------------- -----------------------------
· 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.
· Subquery Correlata In the Update command: The VLR_Total column, created in the Orders table will be updated from the totalization of the items.
----------------------------------- --------------------------------- (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
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
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
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
(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.
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.
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
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
-
- Information
-
Who is online
Users browsing this forum: No registered users and 18 guests