Sub-related query and share

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc

Poston Wed, 06 Jun 2007 10:28 am

Guys, does anyone have any book or tutorial on correlated sub-query and shares!?!?!

Thank you
Location: Barretos-SP

Poston Mon, 02 Jul 2007 3:12 pm

Mr. Paul speaks, buenas tardes amigo, Follows the information: Subqueries: part II related Queries in the previous article-Subqueries part I – we have seen that a subquery is a SELECT command that was \"built\" in another command SELECT, UPDATE, DELETE, or inside another subquery. We also saw that the purpose of the subquery is to return a rowset to the main query.

Correlated Subqueries When a subquery references columns in the main query, gets its name from Correlated Subquery.

Unlike conventional subqueries, Correlated Subquery will be performed as often as the output lines of the main query, into a process of Nested Loop Join.

Examples of use Correlated Subqueries of the following examples were executed in the NorthWind sample database based on the Orders and Order Details tables.

· Correlated Subquery in the SELECT line: in query below will be listed, along with the data from the header of the request (= Orders table), the aggregation of items (= Order Details table).

PS: Note the dependency relationship with the main query in the filter of the subquery (... where od. orderId = orderId. ...)
Code: Select all
  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

· Correlated Subquery in the WHERE filter: will be listed only those orders whose total amount of items exceeding 250 units.
Code: Select all
  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


Code: 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)

· Correlated Subquery in the HAVING clause: in the following batch total sales/year is confronted with a value previously stored in a table of sales targets (= projection). Note that the comparison happens in HAVING cláusule, comparing the totalizer with the result of the subquery.

Code: 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)

· Correlated Subquery in the UPDATE command: the vlr_total column, created in the Orders table will be refreshed from the aggregation of items.
Code: Select all

alter table orders drop column vlr_total
alter table orders add vlr_total dec(10,2)
update orders
   set vlr_total=
   ( select sum(unitprice * quantity)
     from [order details] od
     where od.OrderId = orders.orderId
     group by od.orderId

--------------------------------------------------------------------(830 row (s) affected) correlated Subqueries aggregate Completion versatility to queries, allowing complex joins are resolved with just one command, without the creation of temporary tables. A single detail: correlated subqueries can often be performed as a join conventional; in these cases it is worth looking at the execution plan of the two formats (correlated subquery X join), opting for more efficient.

Well, that was the tip of today.

Until next time!

Read the first part of this article at: ... ?comp=2835 any questions, post here in the forum?

Your friend:) Norry Abs-m
Location: São Paulo

O mundo gira muito!!

Return to SQL

Who is online

Users browsing this forum: No registered users and 7 guests