Part 73: Correlated Subqueries: Row-by-Row Evaluation Mechanics

A Correlated Subquery depends on values from the outer query, evaluating once for every candidate row.

1. Example: Employees Earning More Than Their Department Average

SELECT e.first_name, e.department, e.salary
FROM employees e
WHERE e.salary > (
  -- Subquery references outer table 'e'!
  SELECT AVG(sub.salary) 
  FROM employees sub 
  WHERE sub.department = e.department
);

Key Takeaways

• Correlated subqueries reference outer row values.
• If not indexed properly, they can cause N+1 query performance degradation.

In Part 74, we explore The EXISTS and NOT EXISTS Operators!