Correlated Subqueries

Correlated Subqueries

A correlated subquery references a column from the outer query. Unlike a regular subquery that executes once, a correlated subquery executes once for each row processed by the outer query. This makes it powerful but potentially slow on large datasets.

-- Find employees who earn more than the average salary in their department
SELECT employee_name, department_id, salary
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id
);

In this example, e1.department_id references the outer query. The subquery recalculates the average for each employee's department as the outer query processes each row.

-- Find the most recent order for each customer
SELECT customer_id, order_id, order_date, total_amount
FROM orders o1
WHERE order_date = (
    SELECT MAX(order_date)
    FROM orders o2
    WHERE o2.customer_id = o1.customer_id
);

Correlated subqueries are essential when the subquery logic depends on the current row of the outer query. They cannot be executed independently, which is the key distinction from non-correlated subqueries. Understanding this distinction is critical for query optimization and debugging.

EXISTS with Correlated Subqueries

The EXISTS operator is the most common way to use correlated subqueries. It returns TRUE if the subquery returns any rows, without examining the actual data. The database can stop searching as soon as it finds the first matching row.

-- Find customers who have placed at least one order over $500
SELECT customer_id, customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
    AND o.total_amount > 500
);

The subquery correlates with c.customer_id from the outer query. The SELECT 1 is a convention; you could select any column since EXISTS only checks for row existence.

-- Find products that have been reviewed by at least one premium customer
SELECT p.product_id, p.product_name
FROM products p
WHERE EXISTS (
    SELECT 1
    FROM reviews r
    JOIN customers c ON r.customer_id = c.customer_id
    WHERE r.product_id = p.product_id
    AND c.customer_type = 'premium'
);

EXISTS is generally more efficient than IN for correlated subqueries because the database optimizer can short-circuit evaluation. Use EXISTS when you need to check for the existence of related rows rather than matching specific values.

Performance Implications

Correlated subqueries can be performance killers because they execute once per outer row. If the outer query returns 10,000 rows, the subquery executes 10,000 times. This is known as the N+1 problem.

-- Slow: correlated subquery in SELECT
SELECT 
    order_id,
    customer_id,
    (
        SELECT COUNT(*)
        FROM orders o2
        WHERE o2.customer_id = o1.customer_id
    ) AS total_orders
FROM orders o1;

The equivalent JOIN-based approach is often much faster:

-- Faster: using a JOIN
SELECT 
    o1.order_id,
    o1.customer_id,
    c.total_orders
FROM orders o1
JOIN (
    SELECT customer_id, COUNT(*) AS total_orders
    FROM orders
    GROUP BY customer_id
) c ON o1.customer_id = c.customer_id;

To improve correlated subquery performance:

1. Ensure indexes exist on the columns used in the correlation
2. Consider rewriting as a JOIN or window function
3. Use EXISTS instead of IN for existence checks
4. Limit the outer query result set when possible

-- Add an index to speed up the correlation
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

Window functions like COUNT(*) OVER (PARTITION BY customer_id) are typically the fastest alternative to correlated subqueries in SELECT clauses.

Correlated vs Non-Correlated Subqueries

The key difference is independence. A non-correlated subquery can run independently and produces a result that the outer query uses. A correlated subquery depends on the outer query's current row.

-- Non-correlated: subquery runs once
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- Correlated: subquery runs per row
SELECT * FROM products p1
WHERE price > (SELECT AVG(price) FROM products p2 WHERE p2.category = p1.category);

Non-correlated subqueries are generally optimized better because the database can execute them once and cache the result. Correlated subqueries cannot be cached since the result changes for each outer row.

Feature	Non-Correlated	Correlated
Execution	Once	Once per outer row
Independence	Can run alone	Depends on outer query
Optimization	Easily cached	Harder to optimize
Use case	Lookup, aggregation	Row-by-row comparison

When you find a correlated subquery, always ask: can this be rewritten as a JOIN or window function? In most cases, the answer is yes, and the performance improvement is significant.