Saturday, June 7, 2008

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;

No comments:

Post a Comment