Have you ever had a sub-select where you really needed to reference a value in the outer query? I know I have! The naive way would be to run the outer query and then loop over the results running the inner query on each one. Luckily, there’s a better way. The Correlated subquery. Check it out! The example given is
SELECT employee_number, name FROM employees AS Bob WHERE salary > ( SELECT AVG(salary) FROM employees WHERE department = Bob.department);
See how the sub-select references the outer query? It’s SQL magic.