From: "Sybrand Bakker" <postbus@sybrandb.demon.nl>
Newsgroups: comp.databases.oracle.misc
Subject: Re: Correlated subquery
Date: Sun, 8 Jul 2001 00:25:34 +0200
Message-ID: <tkf2v87675vn83@beta-news.demon.nl>
References: <B3K17.14542$Fk7.130150@news.indigo.ie>
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4522.1200
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200
X-Complaints-To: abuse@nl.demon.net
NNTP-Posting-Host: sybrandb.demon.nl
X-NNTP-Posting-Host: sybrandb.demon.nl:212.238.21.78
X-Trace: beta-news.demon.nl 994544616 beta-news:6390 NO-IDENT sybrandb.demon.nl:212.238.21.78
Lines: 50



"Imen Eladham" <eladhami@eircom.net> wrote in message
news:B3K17.14542$Fk7.130150@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