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.
Lesson 03
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.
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.
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 subquery | Correlated subquery | |
|---|---|---|
| References the outer query | No | Yes |
| Can run on its own | Yes | No, it needs a value from the outer row |
| Conceptual evaluation | Once | Once per outer row |
| Typical use | Compare with a global figure | Compare with a per-group figure, or test for related rows |
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.
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.
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.
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.
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.
"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:
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.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.
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.
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(*).
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.
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.
The exercises below ask you to:
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.
SELECT keep rows with no matches (returning 0 for COUNT, NULL for other aggregates).EXISTS and IN into joins, but correlated scalar comparisons run as a per-row SubPlan.GROUP BY, or a window function, can express the same logic and often scales better on large tables.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.
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.
It references a column from the outer query (such as e.department_id), so its result depends on the current outer row.
Concept check
3 quick questions. One at a time. Instant score at the end.
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.
No results yet
Run a query to see results here
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.
No results yet
Run a query to see results here
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.
No results yet
Run a query to see results here