Course contentsShow
← Computer Science
Lesson 1606 of 2,87236. Database FundamentalsFree lesson

Correlated Subqueries

Writing subqueries that reference columns from the outer query and understanding their row-by-row evaluation.

Correlated Subqueries

You'll learn how subqueries can reference columns from the outer query and why they evaluate row-by-row instead of once.

What Makes a Subquery "Correlated"?

Until now, the subqueries you've seen execute independently—they run once, produce a result, and the outer query uses that result. A correlated subquery is different: it references columns from the outer query, which means it must be re-evaluated for each row processed by the outer query.

Think of it like a nested loop in programming. For every row the outer query examines, the inner (correlated) subquery runs again with values from that specific row.

How It Works

SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = e.department_id
);

Notice how the subquery references e.department_id from the outer query's table alias e. For each employee row, the database:

  1. Takes that employee's department_id
  2. Runs the subquery to find the average salary for that department
  3. Compares the employee's salary to that average

This row-by-row evaluation is what distinguishes correlated from uncorrelated subqueries.

When to Use Them

Correlated subqueries shine when you need comparisons that depend on the current row's values—like comparing each employee to their department's average, or finding customers whose orders exceed their own personal average order value.

They work naturally with EXISTS because you're checking conditions specific to each outer row.

Key Takeaway: Correlated subqueries reference outer query columns and execute once per outer row, enabling row-specific comparisons but requiring careful attention to performance.