Re: SQL QUESTION:How did correlated subquery process?

Nuno Souto wrote:
>
> On Tue, 21 Aug 2001 22:00:05 +0800, Cantonese Boy <waynewan_at_yahoo.com>
> wrote:
>
> > "A correlated subquery is evaluated once for each
> > row processed by the parent statement."
> >
> > I'm not very sure what does it exactly mean. Does it mean
> > every row selected from the parent statement will
> > pass to the subquery to process?
>
> No. It means the subquery will execute for every row returned from the
> parent statement. Slight diff.
>
> >
> > For example, if what I think was right ,why the follow DML
> > won't remove all the row in the emp table ?
> >
> > delete from emp e
> > where sal = (select max(sal) from emp
> > where deptno= e.deptno);
> >
> > Can someone tell me how oracle will process this statement.
> >
>
> Hehehe! Nice. Now, think of it this way:
>
> main statement reads row 1.
> subquery executes and finds that sal is not the max for dept of row 1.
> main statement reads row 2.
> subquery ... for dept of row 2.
> and so on....
>
> Note: row 1 is only accessed *ONCE* by main statement.
> If it wasn't found to be in the condition that allows the delete, then
> it doesn't get deleted. Clear?

If row 1 and row 2 have the same dept no, and if row 1 is the max and got deleted , when subquery processing with the deptno got from the row 2, does row 1 also be counted in this subquery?

Thank you

W.
>
> Cheers
> Nuno Souto
> nsouto_at_optushome.com.au.nospam
Received on Thu Aug 23 2001 - 07:35:46 CDT