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

subquery: ( subquery executes only once and gives the output ot outer query then outer query executes)

select * from emp where deptno in ( select deptno from dept);

co-related query : ( co-related subquery executes for each row returned by main query )

select a.* from emp e

where deptno ( select deptno from emp a

where a.empno e.empno);

2. select e.* from emp e
where 3 > ( select count(*) from emp
where sal > e.sal)
order by sal desc

With a normal nested subquery the inner SELECT query runs first and executes once returning values to be used by the main query. A correlated subquery however executes once for each candidate row considered by the outer query. In other words the inner query is driven by the outer query.