On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

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