CORRELATED SUBQUERIES: Is evaluated for each row processed by the Main query. Execute the Inner query based on the value fetched by the Outer query. Continues till all the values returned by the main query are matched. The INNER Query is driven by the OUTER Query
Ex:
SELECT empno,fname,sal,deptid FROM emp e WHERE sal=(SELECT AVG(sal) FROM emp WHERE deptid=e.deptid)
The Correlated subquery specifically computes the AVG(sal)
for each department.
SUBQUERY: Runs first,executed once,returns values to be used by the MAIN Query. The OUTER Query is driven by the INNER QUERY