when a sub query executes for each row its called a corelated sub query in other words when a sub query takes the input from outer query for executation then execute and passes the result to outer query then outer query executes.

In correlated subqueries the information from the outer select statement participates as a condition in the inner select statement.

Eg : Select deptno ename sal

from emp a

where sal> (select avg(sal) from emp b where a.deptno b.deptno)

order by deptno;