SQL Correlated Subqueries Practice Exercises

What is a correlated subquery?

A correlated subquery references a column from the outer query, which means it can't run on its own — it runs once for every row the outer query evaluates, using that row's values each time. This is different from a regular subquery, which runs once and returns a fixed result.

-- Orders priced above the average price for their own genre
SELECT o1.order_id, o1.book_genre, o1.price_usd
FROM online_bookstore_orders o1
WHERE o1.price_usd > (
    SELECT AVG(o2.price_usd)
    FROM online_bookstore_orders o2
    WHERE o2.book_genre = o1.book_genre
);

The inner query references o1.book_genre — a column from the outer query. For every order, the subquery recalculates the average price for just that order's genre, then compares the order against it.

Why you need table aliases

Because a correlated subquery uses the same table as the outer query, both need an alias so SQL can tell which reference belongs to which copy of the table. Without aliases, the column names would be ambiguous.

-- The two aliases keep the outer and inner rows distinct
FROM employee_records e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employee_records e2
    WHERE e2.department = e1.department
);

e1 is the current row from the outer query; e2 is the copy of the table used inside the subquery. e2.department = e1.department is what makes the subquery correlated — it ties the inner calculation back to the outer row.

Correlated subqueries beyond WHERE

Correlated subqueries most commonly appear in a WHERE clause, but they can also appear in the SELECT list to pull in a per-row calculated value, similar to a window function but evaluated as a nested query.

-- Show each booking alongside the average stay for its own room type
SELECT b1.booking_id, b1.room_type, b1.nights_stayed,
       (SELECT AVG(b2.nights_stayed)
        FROM hotel_room_bookings b2
        WHERE b2.room_type = b1.room_type) AS avg_for_room_type
FROM hotel_room_bookings b1;

Common mistakes

Forgetting to correlate the subquery — if the inner WHERE clause doesn't reference the outer alias, it isn't a correlated subquery anymore — it just recalculates the same fixed value for every row.

Missing or reused aliases — both the outer and inner reference to the table need their own alias. Without them, SQL can't tell which table instance a column belongs to.

Performance — because the subquery runs once per outer row, correlated subqueries can be slower than an equivalent JOIN or window function on large tables. They're best used when the logic is clearest written this way.

Confusing this with a plain grouped subquery — a subquery that hardcodes one group (e.g. WHERE department = 'Marketing') is not correlated — it doesn't change per outer row. Correlation means the comparison value is different for every row being checked.