Correlated subquery

Question

Correlated subquery

Accepted Answer

A correlated subquery references a column from the outer query (e.dept) and re-executes for each outer row. For Alice (Eng), the subquery returns MAX(salary) where dept='Eng' = 90000. Alice's salary matches, so she's included. For Bob (Eng), the subquery still returns 90000 - Bob's 80000 doesn't match. For Carol (HR), the subquery returns MAX(salary) where dept='HR' = 70000. Carol matches and is included. Result: Alice and Carol - the top earner in each department.