Skip to main content
Subqueries & CTEs
3 / 4
0%

Correlated subqueries

0/3 solved3 to unlock next
PrevLocked

Lesson 03

Correlated subqueries

Lesson Description

Write correlated subqueries that evaluate each row against its own context. This lesson helps you solve comparison-style problems while staying precise about row scope and performance tradeoffs.

Easy Project

Mini project: Turn this lesson into a real-world query artifact by writing one clean business report query and validating output quality.

When the inner query depends on the outer row

Every subquery in the first lesson of this course could run on its own. SELECT AVG(salary) FROM employees returns one number no matter where you paste it. But many real questions are relative to each row. "Who is the highest earner in their own department?" There's no single number to compare against here: the target is 24,000 for someone in Executive and 4,400 for someone in Administration. The comparison value changes with every row.

A correlated subquery handles this. It is a subquery that refers to a column from the outer query, so its result depends on which outer row is being checked. Conceptually, PostgreSQL takes the first employee, plugs their department_id into the inner query, gets that department's figure, makes the comparison, and then moves on to the next employee.

You've actually written one already. The NOT EXISTS query that found doctors with no appointments, WHERE a.doctor_id = d.id, was correlated through d.id. This lesson looks at the pattern properly.

Syntax

SELECT outer_cols
FROM table_a AS o
WHERE o.some_col <operator> (
  SELECT aggregate(i.col)
  FROM table_b AS i
  WHERE i.link_col = o.link_col   -- the correlation
);

The line that makes it correlated is the inner WHERE that mentions the outer alias (o.link_col). Because both queries often read the same table, aliases are not optional in practice: they're how you tell PostgreSQL which copy of the table each column belongs to.

Uncorrelated subqueryCorrelated subquery
References the outer queryNoYes
Can run on its ownYesNo, it needs a value from the outer row
Conceptual evaluationOnceOnce per outer row
Typical useCompare with a global figureCompare with a per-group figure, or test for related rows

Worked examples

1. Correlated subqueries in the SELECT list

Start with the column list, where the effect is easiest to see. In the HR database, show every department with its headcount and its top salary:

SELECT d.name,
       (SELECT COUNT(*)    FROM employees e WHERE e.department_id = d.id) AS headcount,
       (SELECT MAX(salary) FROM employees e WHERE e.department_id = d.id) AS top_salary
FROM departments d
ORDER BY headcount DESC, d.name;

This returns 10 rows, one per department. Sales leads with 9 people and a top salary of 14,000, then Shipping with 7 and 8,200. The last row is the interesting one: Accounting has a headcount of 0 and a top_salary of NULL. Because the subqueries run per department row, departments with no employees still show up, much like a LEFT JOIN. COUNT(*) over zero rows is 0, while MAX over zero rows is NULL.

2. The top earner in each department

Now the opening question. Keep an employee only if their salary equals the maximum in their department:

SELECT e.first_name, e.last_name, e.department_id, e.salary
FROM employees e
WHERE e.salary = (
  SELECT MAX(e2.salary)
  FROM employees e2
  WHERE e2.department_id = e.department_id
)
ORDER BY e.department_id;

The result has 9 rows, one per department that has staff. Jennifer Whalen tops Administration (department 10) at 4,400, John Russell tops Sales (80) at 14,000, and Steven King tops Executive (90) at 24,000. Read the inner query as a sentence: "the highest salary among employees e2 who work in the same department as this employee e". If two people tied for the top salary in a department, both would be returned, which is usually what you want.

3. Each patient's most recent appointment

The same shape works with dates. In the hospital database, find each patient's latest appointment:

SELECT p.first_name, p.last_name, a.appointment_date, a.status
FROM appointments a
JOIN patients p ON p.id = a.patient_id
WHERE a.appointment_date = (
  SELECT MAX(a2.appointment_date)
  FROM appointments a2
  WHERE a2.patient_id = a.patient_id
)
ORDER BY a.appointment_date DESC, p.last_name
LIMIT 5;

Without the LIMIT, this returns 30 rows, one for each of the 30 patients. The five most recent are William White (2025-05-20), Mark Mitchell (2025-05-15), Robert Davis and Anthony Nelson (both 2025-05-13) and Matthew Baker (2025-05-11), all with status scheduled. "Latest row per group" comes up constantly in reporting, and this is one of the clearest ways to express it.

4. Correlated EXISTS with an extra condition

EXISTS is almost always correlated, and you can add as many conditions as you need inside it. In the store database, "Which customers have had at least one order cancelled?"

SELECT c.first_name, c.last_name, c.country
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.id
    AND o.status = 'cancelled'
)
ORDER BY c.last_name;

Three customers come back: Noah Brown (Canada), Aiden Martin (Brazil) and Isabella Rodriguez (France). The correlation o.customer_id = c.id ties each order to the customer being checked, and o.status = 'cancelled' narrows which orders count.

5. The same answer without a correlated subquery

Correlated subqueries are readable, but you'll often see the top-earner question written two other ways. Both return the same 9 rows as example 2.

With a derived table you aggregate once, then join:

SELECT e.first_name, e.last_name, e.department_id, e.salary
FROM employees e
JOIN (
  SELECT department_id, MAX(salary) AS dept_max
  FROM employees
  GROUP BY department_id
) m ON m.department_id = e.department_id
WHERE e.salary = m.dept_max;

With a window function (covered in the next course), you compute the department maximum alongside every row:

SELECT first_name, last_name, department_id, salary
FROM (
  SELECT *, MAX(salary) OVER (PARTITION BY department_id) AS dept_max
  FROM employees
) t
WHERE salary = dept_max;

Pick whichever reads most naturally to you and your team. On tables this size, the difference is invisible.

Under the hood: does it really run once per row?

"Once per outer row" is the right way to reason about the result, but it isn't always what the planner does. Running EXPLAIN on the two main patterns in this lesson shows the difference:

  • For the scalar comparison in example 2, PostgreSQL produces a plan with a SubPlan. It really does evaluate the inner aggregate for each outer row, using the index on department_id to find each department's rows quickly.
  • For the EXISTS in example 4, there is no SubPlan. PostgreSQL rewrote it into a join: it collects the customer IDs of cancelled orders once, then looks up those customers by primary key.

So EXISTS and IN are usually cheap even when correlated. Scalar correlated subqueries are the ones to watch on big tables. If an outer table has a million rows and the inner query can't use an index, that's a million aggregate computations, and the derived-table or window version will be much faster. On large data, check with EXPLAIN ANALYZE rather than guessing.

Common mistakes

Forgetting the alias, so nothing is correlated

SELECT first_name, last_name, department_id, salary
FROM employees
WHERE salary = (
  SELECT MAX(salary)
  FROM employees
  WHERE department_id = department_id
);

This runs without error but returns 1 row: Steven King. Inside the subquery, an unqualified department_id resolves to the nearest table, which is the inner employees. So department_id = department_id compares each inner row with itself, is always true, and the subquery returns the company-wide maximum of 24,000. Fix it by aliasing both copies and writing e2.department_id = e.department_id, as in example 2, which returns the correct 9 rows.

A correlated scalar subquery that returns several rows

SELECT d.first_name, d.last_name,
       (SELECT a.appointment_date FROM appointments a WHERE a.doctor_id = d.id) AS appt
FROM doctors d;

This fails with ERROR: more than one row returned by a subquery used as an expression, because most doctors have several appointments. A scalar subquery must produce at most one value per outer row, so wrap the column in an aggregate such as MAX(a.appointment_date) or COUNT(*).

Comparing with a per-group figure that includes the row itself

When you compute "average of my department" with a correlated subquery, the current employee is part of that average. That's normally correct, but if the question is "compared with their colleagues", you need to exclude the row with an extra condition such as AND e2.id <> e.id. Read the business question carefully before you write the inner WHERE.

How other databases differ

Correlated subqueries are standard SQL and behave the same way in MySQL, SQL Server and SQLite, including the unqualified-column trap above. How well each optimizer rewrites them into joins varies by product and version, which is one more reason to check the execution plan on large tables instead of assuming.

Practice

The exercises below ask you to:

  • Find employees earning more than the average of their own department.
  • Find products priced above the average of their own category.
  • Find patients with more appointments than the average patient. This one needs two levels of thinking: a per-patient count that's correlated, compared with an overall average of per-patient counts that isn't.

For each, write the inner query with a hard-coded value first (say, department_id = 50), check the number, then replace the constant with the outer alias.

Key takeaways

  • A correlated subquery references a column from the outer query, so its value changes from row to row.
  • Always alias both the outer and inner tables, especially when they're the same table. Unqualified columns bind to the innermost table.
  • Correlated subqueries in SELECT keep rows with no matches (returning 0 for COUNT, NULL for other aggregates).
  • PostgreSQL turns correlated EXISTS and IN into joins, but correlated scalar comparisons run as a per-row SubPlan.
  • A derived table with GROUP BY, or a window function, can express the same logic and often scales better on large tables.

Correlated subqueries: in depth

A correlated subquery refers to the outer row, so it runs once per row. It answers "compared with their own group" questions, such as employees paid above their department's average or each customer's most recent order, without building a separate summary table.

Worked examples

Above their own department's average

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

For each employee, the inner query averages only their department. The outer alias e links the two.

Each customer's latest order date

SELECT c.first_name, c.last_name,
       (SELECT MAX(o.order_date)
        FROM orders o
        WHERE o.customer_id = c.id) AS last_order
FROM customers c
ORDER BY last_order DESC NULLS LAST;

A correlated scalar subquery in SELECT adds one computed value per customer; customers without orders get NULL.

Common mistakes

  • Using the same alias inside and outside (employees e in both), so the inner WHERE compares the inner table to itself.
  • Running a correlated subquery over a large table without an index on the correlated column, which can be slow.
  • Rewriting as a join without grouping first, which changes the result when the inner table has many rows per key.

Other databases

  • Correlated subqueries work in every major database; optimizers often turn them into joins automatically.
  • PostgreSQL and SQL Server also offer LATERAL / CROSS APPLY for correlated subqueries that return several columns.
Check yourself: What makes a subquery correlated?

It references a column from the outer query (such as e.department_id), so its result depends on the current outer row.

Concept check

Quick check: Correlated subqueries

3 quick questions. One at a time. Instant score at the end.

Exercise 1

Employees earning above their department average

Enterprise Workforce AnalyticseasyPostgreSQL

Return first_name, last_name, salary, and department_id for employees whose salary exceeds the average salary in their own department. Use a correlated subquery. Order by department_id, then salary descending.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 2

Products more expensive than their category average

Commerce Operations IntelligenceeasyPostgreSQL

From the store sandbox, return name, price, and category_id for products whose price exceeds the average price in their own category. Use a correlated subquery. Order by category_id, price descending.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 3

Patients with more appointments than the average patient

Clinical Care PerformanceeasyPostgreSQL

From the hospital sandbox, return first_name and last_name of patients whose appointment count exceeds the average number of appointments per patient. Use a correlated subquery. Order by last_name.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Subqueries & CTEs
3 / 4
0%

Correlated subqueries

0/3 solved3 to unlock next
PrevLocked