correlated subquery
A correlated subquery is a way of reading values in each row
and comparing those values in each row with related data.
A correlated subquery is evaluated once for each row
processed by the parent query.
i.e. the subquery returns a value for each row
processed by the parent query unlike the normal subquery which executes
only once and returns the data to the parent query for processing.
SELECT last_name, salary, department_id
FROM employees outer
WHERE salary > (SELECT AVG(salary)
FROM employees
WHERE department_id = outer.department_id
);
Here the inner query (correlated subquery) generates
the avg salary for each department_id processed by the outer query.