Correlated Subquery appears to use circular logic

Question

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?

Answer 1

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?

Just like with the equivalent join, the optimizer may defer the subquery until after some where conditions have filtered some source rows if possible (or even reverse the order of the join); here that is not possible.