Correlated Subquery

A correlated subquery references a column from the outer query, so it can't run independently — the database re-evaluates it once for every row the outer query considers, unlike a normal subquery which runs a single time.

WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id) is correlated: the inner query's filter depends on e.department_id from the outer row, so it must be recomputed per row. This makes correlated subqueries powerful for row-specific comparisons, but potentially slow on large tables compared to an equivalent JOIN or window function.

EXISTS/NOT EXISTS checks are the most common legitimate use — for most other cases, a join or window function is usually faster.