What is Correlated subquery

ensameer · New User Joined: 15 Mar 2005 Posts: 4 Location: Chennai

What is Correlated Subquery ?
-Sameer

i413678 · Posted: Wed Mar 16, 2005 2:42 pm Post subject:

hi sameer,

in correlated subquery the outer query is executed first and depending on the result of it for each and every record the inner query is executed.

Cheers............

pavan

ovreddy · Posted: Wed Mar 16, 2005 6:28 pm Post subject: Re: correlated subquery

Hi,

Correlated sub query is a type of sub query in which Outer Query is executed at once (fetches one row) and by taking that values inner query will be executed for all rows. Then outer query fetches 2nd row then inner query will be executed for all rows and so on.

To clearly under stand see the following example which finds nth max sal

SELECT SAL FROM EMP A WHERE N = (SELECT COUNT(*) FROM EMP B WHERE A.SAL<=B.SAL);

Outer Query fetches 1 row and inner query compares it with all salaries. Then outer query fetches 2nd row and so on.

Thanks,
Reddy.