Joined: 06 Dec 2004 Posts: 211 Location: Keane Inc., Minneapolis USA.
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.