Correlated Subquery

Most subqueries are resolved before Oracle moves on to handle its parent query. Sometimes, that's not possible since the subquery if based on one of the columns on the parent query.

An example is shown below. We want to find who makes the least in each department. So, in the subquery, determining the mininum salary is done based on the department number of the employee in the parent query.

Select Empno, Ename, DeptNo, Sal
  From Emp e1
 Where Sal = (
              Select Min(Sal)
                From Emp E2
               Where e2.DeptNo = e1.DeptNo
             )
 Order By DeptNo;