Oracle correlated sub-queries - Dec 03, 2008 at 15:00 PM by
Rajmeet Ghai
What is oracle correlated sub-queries? Explain with an example.
A query which uses values from the outer query is called as a correlated sub
query. The subquery is executed once and uses the results for all the
evaluations in the outer query.
Example:
Here, the sub query references the employee_id in outer query. The value of the
employee_id changes by row of the outer query, so the database must rerun the
subquery for each row comparison. The outer query knows nothing about the inner
query except its results.
select employee_id, appraisal_id, appraisal_amount From employee
where
appraisal_amount < (select max(appraisal_amount)
from employee e
where employee_id = e. employee_id);
|