Correlated Subqueries

Last Updated: June 12, 2026 • 15 min read

A correlated subquery references columns from the outer query, making it re-execute once for every row the outer query produces. This row-by-row dependency is incredibly expressive — you can compare each employee against their own department's average — but it demands careful attention to performance on large tables.

1. Introduction

Non-correlated subqueries compute a single value and apply it to all rows. Correlated subqueries go further: they compute a different value for each row based on that row's context. This makes them the natural tool for "above your group's average" style questions — but also a potential performance bottleneck when the outer query returns thousands of rows.

2. Why It Matters

Row-specific comparisons: "Show products priced above their category average" — each product compares against a different benchmark.
Latest-per-group queries: "Find the most recent order for each customer" — the correlation links each outer row to its related records.
Absence detection: Correlated NOT EXISTS finds entities with no related records in a time window (churn detection, orphan cleanup).
Interview favorite: "Rewrite this correlated subquery as a JOIN or window function" tests your ability to think in multiple SQL paradigms.

3. Real-World Analogy

Imagine a national exam. A non-correlated approach: "Did you score above the national average?" (one number for everyone). A correlated approach: "Did you score above your state's average?" — the benchmark changes depending on which state you're from. The correlated subquery recomputes the benchmark for each person's group.

4. How It Works

Correlation via outer alias: The subquery references the outer table's alias (e.g., e1.department), creating a dependency that forces re-execution per row.
Cannot run standalone: Unlike non-correlated subqueries, you can't copy-paste and execute the inner query independently — it depends on the outer context.
Executions = outer row count: 10,000 outer rows = 10,000 subquery executions. Index the correlated column to keep each execution fast.
PostgreSQL may decorrelate: The query planner can sometimes convert correlated subqueries into JOINs internally. Check with EXPLAIN.

5. Internal Architecture

6. Visual Explanation

7. Practical Example

Employees earning more than their department average:

Latest order per customer:

8. Common Mistakes

Missing table aliases

When inner and outer queries use the same table, you must alias both (e1 outer, e2 inner) and qualify every column. Forgetting aliases creates ambiguous reference errors.

Performance on large tables

100K outer rows = 100K subquery executions. Always check with EXPLAIN and consider rewriting as a CTE + JOIN or window function for better performance.

Not indexing the correlation column

If the subquery filters by department, ensure an index exists. Without it, each of the N executions does a full table scan — O(N×M) complexity.

Interview Insight

"Rewrite this correlated subquery as a window function." — WHERE salary > AVG(salary) OVER (PARTITION BY department) is almost always faster and more readable. Knowing multiple approaches (subquery, CTE+JOIN, window function) demonstrates SQL fluency.

9. Quick Quiz

Q1: Can you run a correlated subquery by itself (without the outer query)?

Answer: No. A correlated subquery references the outer table's alias, which doesn't exist in isolation. Attempting to run it standalone produces a "column does not exist" error.

Q2: How many times does a correlated subquery execute for a query returning 5,000 rows?

Answer: 5,000 times (once per outer row). This is why indexing the correlation column and considering JOIN rewrites is critical for performance.

10. Scenario-Based Challenge

Challenge: Customer Churn Risk Report

Build a query that finds customers who placed orders in the past but have NOT ordered in the last 90 days. For each, show their name, total orders, and the date of their last order.

Requirements:

Use a correlated EXISTS to confirm they have ordered before.
Use a correlated NOT EXISTS to confirm no orders in the last 90 days.
Use correlated scalar subqueries in SELECT for total_orders and last_order_date.

Then rewrite using a single CTE with aggregates + JOIN. Compare readability and performance.

11. Debugging Exercise

Find the bugs in this correlated subquery:

Issues:

Missing inner alias: WHERE category = category compares the inner table's category to itself (always true). Fix: FROM products p2 WHERE p2.category = p1.category.
No outer alias qualification: The outer query should reference p1.price and p1.category for clarity.
Performance: Without an index on category, each subquery execution scans the entire table. Add CREATE INDEX ON products(category).

12. Interview Questions

Q1: What's the difference between correlated and non-correlated subqueries?

A: A correlated subquery references outer table columns and re-executes per row. A non-correlated subquery is independent, runs once, and caches the result. You can test by trying to run the inner query standalone — if it works, it's non-correlated.

Q2: Rewrite "employees above department average" using a window function.

A: SELECT * FROM (SELECT *, AVG(salary) OVER (PARTITION BY department) AS dept_avg FROM employees) sub WHERE salary > dept_avg. The window function computes department averages in a single pass — no row-by-row re-execution.

Q3: When is a correlated subquery the best choice?

A: When the result set is small, readability matters more than micro-optimization, or the logic is inherently row-specific (e.g., NOT EXISTS for absence checks). For reporting dashboards with complex absence conditions, correlated NOT EXISTS is often the clearest approach.

13. Production Considerations

Always check EXPLAIN: PostgreSQL may decorrelate the subquery into a JOIN. If it doesn't, and the outer table is large, rewrite manually.
Index the correlation column: Without an index on the joined column, each subquery execution does a sequential scan. The performance impact multiplies: N outer rows × M inner rows = O(N×M).
Prefer window functions for per-group calculations: AVG() OVER (PARTITION BY ...) computes in a single pass vs. N passes for correlated subqueries.
Use CTEs for complex logic: Pre-compute aggregates in a CTE, then JOIN. This is usually faster and more readable than nested correlated subqueries.
Limit result sets: When correlated subqueries are unavoidable, filter the outer query aggressively (WHERE, LIMIT) to minimize executions.