Re: Correlated subquery

"Imen Eladham" <eladhami_at_eircom.net> wrote in message news:B3K17.14542$Fk7.130150_at_news.indigo.ie...
> Hi there,
> I am studying SQL at a moment,
> And can not understand Correlated subquery
> can any body guide me to a free tutorial
> or explain with example, what is Correlated sub query.
>
>
>
>

find departments without employees

select *
from dept d
where not exists
(select 'x'
  from emp e
  where e.deptno = d.deptno
)

select the latest price of a product

select *
from price p
where begin_date =
(select max(begin_date)
 from price p1
 where p1.productid = p.productid
)

find duplicates
select * from foo x
where x.rowid >
(select min(rowid)
 from foo y
where y.<primary key> = x.<primary key>)

every subquery above has conditions which refer to the main query. That's why they are correlated subqueries.

Hth,

Sybrand Bakker, Senior Oracle DBA Received on Sat Jul 07 2001 - 17:25:34 CDT