Correlated Subqueries

Objectives

By the end of this lesson, you should be able to:

• Write a subquery that references the outer query’s current row
• Explain how a correlated subquery differs from the subqueries in the previous lesson
• Recognize when a correlated subquery is needed instead of a plain one

💡 Why this matters: “Employees earning more than their own department’s average” is a different question from “more than the company average.” The comparison value changes depending on which employee is being checked, that’s exactly what a correlated subquery does.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database, using the employees table from the previous lesson.

An Ordinary Subquery Runs Once

Every subquery from the previous lesson ran independently, computed once, then used by the outer query. (SELECT AVG(salary) FROM employees) doesn’t care which row the outer query is currently looking at, it’s the same single number every time.

A Correlated Subquery Runs Per Row

SELECT e.first_name, e.department, e.salary
FROM employees e
WHERE e.salary > (
  SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department = e.department
)
ORDER BY e.first_name;

 first_name | department  |  salary
------------+-------------+----------
 Alexis     | Marketing   | 71000.00
 Priya      | Engineering | 89000.00
 Sam        | Sales       | 63000.00
 Taylor     | Engineering | 95000.00

The inner query references e.department, a column from the outer query’s current row, this is what makes it correlated. PostgreSQL effectively re-runs the subquery for every row in employees, each time computing the average salary for that specific row’s department, then comparing that row’s own salary against it. Sam Whitfield (63000) makes the list here, above the Sales average, even though 63000 wouldn’t have qualified against the company-wide average from the previous lesson.

Two Aliases, One Table

Just like the self join in Module 10, this query uses two aliases (e and e2) for the same table, employees, one for the outer query’s row, one for the inner query’s comparison set. This is required for the same reason: PostgreSQL needs a way to distinguish “the current employee” from “every employee in that department” when both come from the same table.

Correlated vs Plain: Choosing Between Them

A plain subquery (previous lesson) computes one fixed value, used the same way for every outer row, faster to run since it only executes once. A correlated subquery recomputes its result for every outer row, more flexible, capable of “relative to this row’s own group” comparisons, but potentially slower on a large table since the inner query effectively runs once per outer row.

Try It

1. Write a correlated subquery for every employee earning less than their own department’s average.
2. Write a correlated subquery that shows each employee’s first_name and the highest salary in their own department (hint: MAX instead of AVG).
3. Explain, in your own words, why (SELECT AVG(salary) FROM employees WHERE department = 'Engineering') (a plain subquery, hardcoded) gives a different, less flexible result than this lesson’s correlated version.
4. Explain what would happen to this lesson’s main query if e2.department = e.department were changed to e2.department = 'Sales' (a fixed value instead of a correlation).

Recap

• A correlated subquery references a column from the outer query, recomputing its result separately for each outer row.
• This makes “relative to this row’s own group” comparisons possible, something a plain, one-time subquery can’t express.
• Correlated subqueries typically use two aliases for the same table, distinguishing the outer row from the inner comparison set.

Next lesson: Common Table Expressions, naming and reusing a subquery with WITH.