Skip to content
Tutorials Center
My learning

Topics

Lesson 44 of 125 ยท Subqueries and Set Operations

Correlated Subqueries

Learn SQL correlated subqueries that run once per outer row. Compare rows with their group, find the latest row per group, avoid scoping bugs and keep them fast.

The subqueries in the previous lesson were self-contained: you could cut the inner query out, run it on its own, and get the same answer. A correlated subquery is different. It refers to a column of the outer query, so its result depends on which outer row is being looked at. Conceptually, it runs again for every row.

That one change unlocks a whole class of questions: "employees who earn more than the average of their own department", "each customer's latest order", "products that are among the two most expensive in their category". Each of these compares a row with a group that the row itself defines.

This lesson shows how correlated subqueries work, the patterns you'll use most, a scoping bug that can silently break them, and how to keep them fast.

From self-contained to correlated

Start with a familiar query: employees who earn more than the company average. Then change one thing: compare each employee with the average of their own department:

SQL Above their department's average ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The inner query mentions e.department_id, and e is the alias of the outer table. That's the correlation. For each employee, the inner query computes the average salary of the colleagues in that employee's department, and the outer WHERE compares the two. Ten employees beat their own department's average, and each department contributes at least one, usually its head.

Notice the two aliases for the same table, e and colleague, just like in a self join. They are what make the query readable and, as you'll see below, correct.

The CEO and the contractor are missing from the result. Their department_id is NULL, so colleague.department_id = NULL matches nobody, the inner AVG is NULL, and salary > NULL is not true.

How a correlated subquery runs

The logical model is a loop:

  1. Take the next row of the outer query.
  2. Plug its values (here e.department_id) into the inner query.
  3. Run the inner query and use its result for this row.
  4. Repeat for every outer row.

For the query above, the first few steps look like this:

Outer row e.department_id Inner query computes Kept?
Yara Demir, 157,000 1 average of Engineering = 87,500 yes
Yusuf Novak, 73,000 1 average of Engineering = 87,500 no
Farah Tanaka, 138,000 2 average of Design = 83,000 yes
Wen Kaya, 56,000 2 average of Design = 83,000 no

The database doesn't have to execute it literally that way. Optimizers can cache results for repeated values or rewrite the query as a join. But thinking of it as "once per outer row" always gives the right answer, and it reminds you where the cost can come from.

Per-row values in SELECT

A correlated scalar subquery in the SELECT list adds a computed value to each row. This is a clean way to attach counts or latest dates without a GROUP BY on the outer query:

SQL Order count and last order per customer ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Each Australian customer gets their own order count and most recent order. Yara Usman has never ordered, so her count is 0 and her last_order is NULL. With COUNT(*) that comes for free: an inner query over zero rows still returns one row with the count 0. The equivalent LEFT JOIN with GROUP BY needs more care (you must count o.id, not *).

Correlated SELECT subqueries also never cause fan-out: each one returns one value per outer row, so you can add several of them, each counting a different child table, without the rows multiplying.

The latest row per group

"Show each customer's most recent order" is one of the most common requests in SQL. A correlated subquery expresses it directly: keep an order if its date equals the maximum date for the same customer:

SQL Each customer's latest order ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Without the LIMIT, you'd get 34 rows: one for every customer who has ordered. That's only guaranteed because no customer placed two orders at exactly the same minute; with ties, both orders would be kept. If you need exactly one row per group even with ties, add a tie-breaker (for example compare o.id with the largest id among the latest ones) or use ROW_NUMBER, which you'll meet in the window functions chapter.

Top N per group

Correlated subqueries can count, too. A product is among the two most expensive in its category if fewer than two products in the same category cost more:

SQL Two most expensive per category ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Twelve rows, two per category. For the most expensive product the inner count is 0, for the runner-up it's 1, and everything else has at least 2 pricier products. Change < 2 to < 3 for the top three. If two products tied on price, both would be included, which is usually what "top N" should mean. This is the classic way to write top-N-per-group in databases without window functions; today RANK and DENSE_RANK are usually clearer and faster.

Correlated subqueries in UPDATE

A correlated subquery is also how you update each row with a value computed from related rows. Here the departments table gets a headcount column, filled in per department. The changes only affect this run:

SQL Store each department's headcount ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Inside an UPDATE, the outer table is referred to by its name (departments.id), because standard UPDATE doesn't take an alias. SQLite, PostgreSQL, MySQL and SQL Server all accept this form. Storing derived values like headcount means keeping them in sync; triggers can do that automatically.

The scoping trap

How does the database decide whether a column name belongs to the inner or the outer query? The rule: an unqualified name is looked up in the innermost query first, and only if no table there has such a column does the search move outward. This rule creates two bugs that produce no error at all.

Bug 1: forgetting the alias. Write the department comparison without qualifying the columns, and both sides of the = refer to the inner table:

SQL A correlation that isn't ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

It returns 13 instead of the correct 10, because the subquery is no longer correlated: it averages everyone who has a department. Nothing warns you.

Bug 2: a column that doesn't exist in the inner table. If the inner table has no such column, the name silently resolves to the outer table, and a subquery you meant to be self-contained becomes correlated:

SQL A typo that matches everything ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

All 120 orders come back, because for each order the subquery returns that order's own customer_id once per category, and a value is always "in" a list of itself. Run the inner query on its own and it fails with no such column: customer_id, which reveals the mistake.

Qualify every column in a subquery with a table alias (colleague.salary, e.department_id). Then a typo becomes an error instead of a wrong answer, and readers can see at a glance which columns come from the outer query.

Performance

Because a correlated subquery logically runs once per outer row, its cost is roughly outer rows ร— cost of one inner run. On small tables that's nothing. On a million-row table with an inner query that scans another big table, it can be painfully slow. SQLite's query plan labels such subqueries clearly:

SQL A correlated subquery in the query plan ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The plan shows SCAN e, then CORRELATED SCALAR SUBQUERY 1 containing another SCAN colleague: for each employee, SQLite scans the employees table again. (Reading Query Plans explains the output in detail.) Three ways to make it fast:

  1. Index the correlated column. With CREATE INDEX idx_employees_department ON employees (department_id); the inner query becomes a quick SEARCH instead of a full SCAN. Add that line before EXPLAIN QUERY PLAN above and run it again to see the change.

  2. Rewrite as a join to a derived table that aggregates once:

    SQL
    SELECT e.first_name, e.salary, dept.avg_salary
    FROM employees AS e
    JOIN (
      SELECT department_id, AVG(salary) AS avg_salary
      FROM employees
      GROUP BY department_id
    ) AS dept
      ON dept.department_id = e.department_id
    WHERE e.salary > dept.avg_salary;
    
  3. Use a window function such as AVG(salary) OVER (PARTITION BY department_id), covered in Window Functions, which computes all the group averages in one pass.

Modern optimizers (PostgreSQL, SQL Server, Oracle, and to a lesser degree MySQL and SQLite) often do rewrite 2 automatically, a process called decorrelation. Still, measure before you assume. A correlated subquery with a good index is often perfectly fast, and it's frequently the most readable version of the query.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What makes a subquery correlated?

    A correlated subquery depends on the current outer row through a reference like e.department_id, so it can't run on its own.
  2. 2. For a customer with no orders, what does (SELECT COUNT(*) FROM orders AS o WHERE o.customer_id = c.id) return?

    COUNT(*) over zero rows still returns one row with the value 0. MAX or SUM over zero rows would return NULL.
  3. 3. Inside (SELECT AVG(salary) FROM employees WHERE department_id = department_id), what does department_id = department_id compare?

    Unqualified names resolve to the innermost table first, so both sides are the inner column. Qualify with aliases to correlate correctly.
  4. 4. A product's inner count of pricier products in the same category is 1. With the condition < 2, is it kept?

    Exactly one product costs more, so this is the runner-up, and 1 < 2 keeps it.
  5. 5. Which change usually helps most when a correlated subquery on a large table is slow?

    An index turns each inner run into a quick search, and a join to a pre-aggregated table computes each group once instead of once per row.

Summary

  • A correlated subquery refers to the outer query's current row and logically runs once per outer row.
  • It compares a row with its own group: above the department average, latest order per customer, top N per category.
  • Correlated scalar subqueries in SELECT attach per-row counts and dates without GROUP BY and without fan-out.
  • In UPDATE, they compute each row's new value from related rows.
  • Unqualified columns resolve to the innermost table first; a missing or unqualified column can silently change the meaning. Qualify everything.
  • Cost grows with outer rows ร— inner work: index the correlated column, or rewrite with a derived table or a window function when it matters.

Next: EXISTS, the correlated subquery that only asks "is there at least one matching row?".