I googled correlated subquery, and it gave me the below example. However, it appears in the outer query, that department id will be determined by a dynamic salary, which in turn, is determined by a relationship between department_id in the outer query and department_id in the inner query. This seems like very circular logic. How can both department_ids have a relationship when salary is not yet determined?
>Solution :
You are confusing the order of operations. The correlated subquery is essentially a join; in principle it will be performed for all rows (though in practice there is often some optimization) before the where conditions are applied. And joins (again, in principal) execute first and find all the combined source rows that then are filtered by where conditions, then grouped by group bys, etc.
Essentially that’s what a correlated subquery is: a subquery that doesn’t resolve without information from the outer rows.
Does the equivalent
select e.salary, e.department_id
from employees e
join (
select department_id, avg(salary) avg_salary
from employees
group by department_id
) department_average
using (department_id)
where e.salary > department_average.salary
also confuse you?