Re: SQL QUESTION:How did correlated subquery process?

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?

Cheers
Nuno Souto
nsouto_at_optushome.com.au.nospam Received on Tue Aug 21 2001 - 09:20:16 CDT