Using Correlated Subqueries

Find all employees who earn more than the average salary in their department.




SELECT last_name, salary, department_id
FROM employees outer
WHERE salary >
                      (SELECT AVG(salary)
                       FROM employees
                       WHERE department_id =  outer.department_id) ;


Each time a row from the outer query is processed, the inner query is evaluated.