DocsSubqueriesCorrelated Subqueries

Correlated Subqueries

Subqueries

Concept

A correlated subquery references columns from the outer query, creating a dependency between the inner and outer queries. Unlike a regular subquery that runs once, a correlated subquery is conceptually re-evaluated for every row in the outer query. This makes it powerful but potentially expensive.

Correlated subqueries are essential when the inner query's logic depends on the current outer row. Common patterns include finding the most recent order per customer, comparing each row to its group average, or checking existence of related records. The EXISTS operator frequently uses correlated subqueries.

Modern database optimizers can often transform correlated subqueries into joins or semi-joins internally, so the actual execution may be more efficient than the conceptual row-by-row model suggests. However, complex correlated subqueries can still be performance bottlenecks on large datasets.

Syntax

-- Correlated subquery in WHERE
SELECT p.name, p.price, p.category
FROM products p
WHERE p.price = (
  SELECT MAX(p2.price)
  FROM products p2
  WHERE p2.category = p.category
);

-- Correlated subquery in SELECT
SELECT
  c.first_name,
  c.last_name,
  (SELECT COUNT(*)
   FROM orders o
   WHERE o.customer_id = c.customer_id
  ) AS order_count
FROM customers c;

Practical Example

Using the ecommerce schema (customers, orders, order_items, products)

-- Most expensive product in each category
SELECT p.name, p.price, p.category
FROM products p
WHERE p.price = (
  SELECT MAX(p2.price)
  FROM products p2
  WHERE p2.category = p.category
)
ORDER BY p.category;

-- Customers whose latest order is over $200
SELECT
  c.customer_id,
  c.first_name || ' ' || c.last_name AS customer
FROM customers c
WHERE (
  SELECT o.total_amount
  FROM orders o
  WHERE o.customer_id = c.customer_id
  ORDER BY o.order_date DESC
  LIMIT 1
) > 200;

Common Pitfalls & Tips

1Correlated subqueries run (conceptually) once per outer row, which can be very slow on large tables. Test performance and consider rewriting as a JOIN.
2If the correlated subquery returns no rows, it yields NULL. This can cause unexpected filtering behavior in WHERE.
3Aliasing is critical in correlated subqueries. Without clear aliases the inner and outer column references become ambiguous.

Practice correlated subqueries