Correlated subqueries are the key to answering row-level questions that depend on the surrounding data context — category averages, prior-period comparisons, existence checks. This deep-dive lesson teaches you the execution model, aliasing rules, and performance trade-offs through real analytical scenarios.
Picture this: you're analyzing sales performance for a retail chain, and you need to find every product that sells above the average price for its own category. Not the global average — the category-specific average. A regular subquery can't do this, because a regular subquery runs once and returns a fixed result. It has no way of knowing which category the outer query is currently evaluating. You need a subquery that can look over its shoulder, grab a value from the outer row, and use it as a filter — re-executing every single time the outer query moves to a new row.
That's a correlated subquery. It's one of the most powerful and frequently misunderstood constructs in SQL, and once you genuinely understand how it works, it changes the way you think about filtering. You stop asking "what does my result set look like?" and start asking "what is true about this particular row in the context of everything else in the database?" That shift in perspective unlocks an entire class of business questions that would otherwise require multiple round trips, application-layer logic, or awkward self-joins.
By the end of this lesson, you'll be able to write, debug, and optimize correlated subqueries with confidence. You'll understand their execution model, recognize when they're the right tool versus when they're a performance trap, and know how to refactor them when scale demands it.
What you'll learn:
You should be comfortable writing standard SELECT queries with WHERE clauses and aggregates before tackling this topic. Familiarity with understanding SQL subqueries: filtering and looking up data with nested SELECT statements is strongly recommended — this lesson builds directly on that foundation. You should also know how SQL JOINs work, because we'll be comparing correlated subqueries to JOIN-based alternatives throughout.
Before we write any correlated subqueries, let's get precise about what separates them from their non-correlated cousins.
A non-correlated subquery is self-contained. It doesn't reference anything from the outer query. The database can evaluate it once, cache the result, and use that result as a fixed value throughout the outer query's execution. Here's an example:
-- Non-correlated: runs once, returns a single value
SELECT product_name, unit_price
FROM products
WHERE unit_price > (
SELECT AVG(unit_price)
FROM products
);
The inner query has no idea what the outer query is doing. It produces a number — say, 42.75 — and the outer query uses that number to filter every row. Clean, simple, efficient.
A correlated subquery reaches out into the parent query and grabs a value from the current row being evaluated. That reference creates a dependency: the inner query can't run until the outer query hands it something to work with. And because it needs a fresh value for every row, it runs once per row of the outer query.
-- Correlated: runs once per row, references outer.category_id
SELECT p.product_name, p.unit_price, p.category_id
FROM products p
WHERE p.unit_price > (
SELECT AVG(p2.unit_price)
FROM products p2
WHERE p2.category_id = p.category_id -- <-- this is the correlation
);
Notice the reference p.category_id inside the inner query. That p alias belongs to the outer query. For each row the outer query considers, it passes the category_id value into the inner query, which recalculates the average for that specific category. The outer query then decides whether the current row passes the filter.
Key insight
The correlation isn't magic syntax — it's just a WHERE clause in the inner query that references a column from the outer query's table alias. The database engine recognizes that this reference is unresolvable within the inner query's own scope, so it resolves it from the outer scope instead. This is called outer reference resolution.
Understanding how the SQL engine actually processes a correlated subquery will save you from endless confusion about performance and behavior.
Conceptually (and in many naive implementations, literally), the database does this:
If your outer query returns 100,000 rows, the inner query runs 100,000 times. That's the performance reality you need to keep in mind — but it doesn't mean correlated subqueries are always wrong. It means you need to know what you're trading.
Modern query optimizers (PostgreSQL, SQL Server, Oracle, even MySQL 8+) will often transform correlated subqueries into equivalent JOIN operations internally, but you can't always count on it. Knowing the logical execution model helps you reason about correctness even when the optimizer surprises you.
Note
The term "correlated" refers specifically to the logical dependency between inner and outer queries. Some databases use the term "dependent subquery" in their query plans and documentation. These are the same thing.
The most common correlated subquery error is scope ambiguity — when the inner query accidentally resolves a column reference to its own table instead of the outer table, or when column names collide between scopes.
The solution is always: use explicit table aliases, and make them distinct between the outer and inner queries.
Let's work through this with a realistic dataset. Suppose we have an e-commerce order system:
-- orders table
-- order_id, customer_id, order_date, status, region, total_amount
-- order_items table
-- item_id, order_id, product_id, quantity, unit_price, discount_pct
Here's a query with a dangerous alias collision:
-- WRONG: both queries use 'o', causing ambiguity
SELECT o.order_id, o.total_amount
FROM orders o
WHERE o.total_amount > (
SELECT AVG(o.total_amount) -- which 'o' is this??
FROM orders o -- inner 'o' shadows outer 'o'
WHERE o.region = o.region -- this compares region to itself! Always true.
);
This query will compile and run, but it produces nonsense. The inner query's o.region = o.region always evaluates to true (or NULL = NULL, which is false — but let's not go there yet). You're computing the average across all regions and comparing every order to that global average.
Here's the correct version with distinct aliases:
-- CORRECT: outer uses 'o', inner uses 'o2'
SELECT o.order_id, o.total_amount, o.region
FROM orders o
WHERE o.total_amount > (
SELECT AVG(o2.total_amount)
FROM orders o2
WHERE o2.region = o.region -- now unambiguous: o2 is inner, o is outer
);
The reference o.region inside the inner query's WHERE clause is an outer reference. The database resolves it by reaching up to the outer scope. The o2.region is resolved locally within the inner query. These are now two different, clearly distinguishable things.
Warning
This type of alias collision is one of the most insidious SQL bugs because the query runs without error and returns results that look plausible. Always use meaningfully different aliases — o and o2, or better yet, descriptive names like outer_orders and region_orders when clarity matters more than brevity.
The most common pattern is comparing a column from the outer query against an aggregate computed from related rows in the inner query. We've seen the basic shape — now let's build something real.
Business question: Find all employees whose salary is above the average salary for their department.
SELECT
e.employee_id,
e.first_name,
e.last_name,
e.department_id,
e.salary
FROM employees e
WHERE e.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.department_id = e.department_id
)
ORDER BY e.department_id, e.salary DESC;
This query does exactly what you'd want. For each employee row, the inner query computes the average salary for that specific department, and the outer query compares the employee's salary to that benchmark. Employees below or at their department average don't make it through.
Let's extend this. What if you also want to see the department average in the output?
SELECT
e.employee_id,
e.first_name,
e.last_name,
e.department_id,
e.salary,
(
SELECT ROUND(AVG(e2.salary), 2)
FROM employees e2
WHERE e2.department_id = e.department_id
) AS dept_avg_salary,
ROUND(e.salary - (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.department_id = e.department_id
), 2) AS salary_above_avg
FROM employees e
WHERE e.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.department_id = e.department_id
)
ORDER BY e.department_id, salary_above_avg DESC;
This works, but notice the inner query now appears three times — once in WHERE, once in SELECT for the display value, and once in SELECT for the calculation. The database may execute it up to 3N times (where N is the number of rows). This is where you'd typically refactor to a CTE or derived table, which we'll cover later.
Tip
When you find yourself writing the same correlated subquery in multiple places within a single outer query, that's a strong signal to pull it out into a CTE. The correlated subquery is great for expressing the logic, but a CTE makes it maintainable and potentially more performant. See advanced subqueries and CTEs: mastering complex SQL query architecture for the full refactoring playbook.
A correlated subquery doesn't have to live in WHERE. You can use it in the SELECT clause to compute a per-row value based on related data. This is sometimes called a lateral scalar subquery.
Business question: For each customer, show their total number of orders and the date of their most recent order.
SELECT
c.customer_id,
c.company_name,
c.country,
(
SELECT COUNT(*)
FROM orders o
WHERE o.customer_id = c.customer_id
) AS total_orders,
(
SELECT MAX(o.order_date)
FROM orders o
WHERE o.customer_id = c.customer_id
) AS last_order_date
FROM customers c
ORDER BY total_orders DESC;
Each subquery in the SELECT clause runs once per customer row. The result is a clean, readable output with no GROUP BY required — because we're not grouping the outer query at all. The aggregation happens inside the subquery, scoped to each customer.
This pattern is genuinely useful when:
But be aware of the trade-off: if customers has 50,000 rows and you have two correlated subqueries in SELECT, you're potentially running 100,000 subquery executions. For reporting on moderate datasets this is fine. For high-frequency operational queries, you'd want to profile it.
The equivalent JOIN version for comparison:
SELECT
c.customer_id,
c.company_name,
c.country,
COUNT(o.order_id) AS total_orders,
MAX(o.order_date) AS last_order_date
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.company_name, c.country
ORDER BY total_orders DESC;
Both produce the same result. The JOIN version is typically faster at scale because it's a single pass. But the correlated subquery version is more readable when you have many different aggregates from different related tables, since each subquery is self-contained and you don't need to manage a complex GROUP BY.
EXISTS is one of the most elegant uses of correlated subqueries. Instead of returning a value to compare, the inner query simply checks whether any matching rows exist. The moment it finds one, it stops — it doesn't need to scan the whole related table. This makes it highly efficient when the relationship has good selectivity.
You can read a deep treatment of this pattern in mastering SQL EXISTS and NOT EXISTS: correlated subquery patterns for filtering with related data, but let's walk through the mechanics here.
Business question: Find all products that have been ordered at least once in the past 90 days.
SELECT
p.product_id,
p.product_name,
p.category_id,
p.unit_price
FROM products p
WHERE EXISTS (
SELECT 1
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
WHERE oi.product_id = p.product_id
AND o.order_date >= CURRENT_DATE - INTERVAL '90 days'
);
Notice the SELECT 1 inside EXISTS — we don't care what the subquery returns, only whether it returns anything. The database optimizes this: it short-circuits as soon as it finds the first matching row. This is why EXISTS is often faster than IN for large related tables.
The correlation point is oi.product_id = p.product_id. For each product in the outer query, the inner query checks whether any order item in the last 90 days references that product. If yes, the product passes the filter.
NOT EXISTS flips this:
Business question: Find all customers who have never placed an order.
SELECT
c.customer_id,
c.company_name,
c.email,
c.created_date
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);
This is cleaner and often more correct than the classic LEFT JOIN ... WHERE o.customer_id IS NULL pattern, especially when NULLs in other columns could cause unexpected behavior. NOT EXISTS has unambiguous semantics: it returns true only when zero matching rows exist.
Key insight
EXISTS with a correlated subquery is semantically "does this row have at least one match?" It's a membership test, not a value comparison. This makes it perfect for filtering based on the existence of a relationship rather than an attribute of that relationship.
You can also correlate a subquery inside a HAVING clause, filtering groups based on related data from another table. This is an advanced pattern but it's real and useful.
Business question: Find product categories where the average order item quantity is higher than the overall average order item quantity across all categories.
SELECT
p.category_id,
AVG(oi.quantity) AS avg_qty_ordered
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id
GROUP BY p.category_id
HAVING AVG(oi.quantity) > (
SELECT AVG(oi2.quantity)
FROM order_items oi2
);
Technically this last example isn't correlated — the inner query doesn't reference the outer. Let's make it genuinely correlated:
Business question: For each sales region, find the months where the region's average order value exceeded 120% of the previous month's average for that same region.
SELECT
o.region,
DATE_TRUNC('month', o.order_date) AS order_month,
AVG(o.total_amount) AS avg_order_value
FROM orders o
GROUP BY o.region, DATE_TRUNC('month', o.order_date)
HAVING AVG(o.total_amount) > 1.2 * (
SELECT AVG(o2.total_amount)
FROM orders o2
WHERE o2.region = o.region
AND DATE_TRUNC('month', o2.order_date) =
DATE_TRUNC('month', o.order_date) - INTERVAL '1 month'
);
Here, the HAVING clause contains a correlated subquery that references o.region and o.order_date from the outer query's grouping context. For each (region, month) group, the inner query finds the average order value for the previous month in the same region and checks whether the current month exceeds it by 20%.
This is genuinely hard to write any other way without window functions (which is often the better tool for period-over-period comparisons, but that's a different lesson). The correlated subquery approach makes the business logic explicit and readable.
SQL allows you to nest subqueries multiple levels deep, and correlation can span multiple levels. This is where things get truly complex — and where most SQL authors reach their limits.
Business question: For each sales manager, find their top-performing salesperson (by total revenue), but only include managers whose team's total revenue exceeds $1,000,000.
SELECT
mgr.employee_id AS manager_id,
mgr.first_name || ' ' || mgr.last_name AS manager_name,
(
-- Level 2: find the name of the top salesperson for this manager
SELECT e.first_name || ' ' || e.last_name
FROM employees e
WHERE e.manager_id = mgr.employee_id
AND (
-- Level 3: correlated to BOTH outer levels
SELECT SUM(o.total_amount)
FROM orders o
WHERE o.salesperson_id = e.employee_id
) = (
SELECT MAX(team_revenue)
FROM (
SELECT SUM(o2.total_amount) AS team_revenue
FROM orders o2
JOIN employees e2 ON e2.employee_id = o2.salesperson_id
WHERE e2.manager_id = mgr.employee_id -- references outermost scope
GROUP BY o2.salesperson_id
) AS revenue_by_rep
)
LIMIT 1
) AS top_performer
FROM employees mgr
WHERE mgr.is_manager = TRUE
AND (
-- Filter managers by team revenue
SELECT SUM(o.total_amount)
FROM orders o
JOIN employees e ON e.employee_id = o.salesperson_id
WHERE e.manager_id = mgr.employee_id
) > 1000000;
This is complex, but every piece is traceable. The innermost derived table references mgr.employee_id from the outermost scope — a two-level outer reference. Most databases support this, but it's notoriously hard to debug.
Warning
Multi-level correlated subqueries are often a sign that you should step back and use a CTE-based approach. The logic becomes nearly impossible to reason about, and the performance characteristics are opaque. Reserve nested correlated subqueries for cases where the alternative is substantially more complex or where you're prototyping logic before refactoring.
Let's build something a working data analyst would actually write. You're analyzing a subscription platform and need to answer: "For each customer, what was their running purchase total at the time they first upgraded to a premium plan?"
This requires finding, for each customer, the sum of all orders before a specific event. That event date is customer-specific, which makes it a perfect correlated subquery problem.
-- Assume: customers, orders, subscription_events tables
-- subscription_events: customer_id, event_type, event_date
SELECT
c.customer_id,
c.company_name,
c.acquisition_channel,
upgrade_events.upgrade_date,
(
SELECT COALESCE(SUM(o.total_amount), 0)
FROM orders o
WHERE o.customer_id = c.customer_id
AND o.order_date < upgrade_events.upgrade_date
) AS revenue_before_upgrade,
(
SELECT COUNT(*)
FROM orders o
WHERE o.customer_id = c.customer_id
AND o.order_date < upgrade_events.upgrade_date
) AS orders_before_upgrade
FROM customers c
JOIN (
-- Get each customer's first upgrade event
SELECT
se.customer_id,
MIN(se.event_date) AS upgrade_date
FROM subscription_events se
WHERE se.event_type = 'upgrade_to_premium'
GROUP BY se.customer_id
) upgrade_events ON upgrade_events.customer_id = c.customer_id;
The correlated subqueries reference both c.customer_id (from the outermost FROM) and upgrade_events.upgrade_date (from the derived table in the JOIN). This is a join between the outer scope and a derived table, with subqueries in SELECT that reference both scopes. It's a pattern that genuinely earns its complexity — it's answering a question that would require significant application-layer work otherwise.
Notice the COALESCE(SUM(...), 0) wrapper — if a customer made no orders before upgrading (possible for customers who upgraded immediately), the subquery returns NULL rather than 0. Handling NULLs correctly in correlated subqueries is a detail that bites people in production. For a deeper dive on this, see NULL handling in SQL: IS NULL, COALESCE, and NULLIF.
So far we've mostly correlated a table to itself. But correlation is even more useful when the outer and inner queries work with entirely different tables, and the connection between them is the business logic.
Business question: Find all orders where the discount applied is higher than the average discount the customer has received across all their previous orders.
SELECT
oi.item_id,
oi.order_id,
o.customer_id,
p.product_name,
oi.discount_pct,
(
SELECT AVG(oi2.discount_pct)
FROM order_items oi2
JOIN orders o2 ON o2.order_id = oi2.order_id
WHERE o2.customer_id = o.customer_id -- reference outer orders table
AND o2.order_date < o.order_date -- only prior orders
) AS customer_avg_prior_discount
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE oi.discount_pct > (
SELECT AVG(oi2.discount_pct)
FROM order_items oi2
JOIN orders o2 ON o2.order_id = oi2.order_id
WHERE o2.customer_id = o.customer_id -- reference outer orders table
AND o2.order_date < o.order_date -- only prior orders
);
The outer query joins order_items, orders, and products. The inner query also joins order_items and orders, but uses o.customer_id and o.order_date from the outer scope to define "prior orders for this customer." This is correlation across a multi-table outer query — entirely valid, just requires careful aliasing.
Tip
When your outer query already involves JOINs, make sure every table alias in the inner query is completely distinct from every alias in the outer query. A good naming convention is to suffix inner aliases with _sub or _inner: oi_sub, o_sub, etc. This makes the outer references immediately obvious.
We've established that correlated subqueries run once per row. Now let's quantify what that means and when it matters.
Scenario: You have 500,000 order rows. Each correlated subquery execution touches an average of 50 rows in the related table. Without any optimization, that's 25,000,000 row reads just for the subquery. With a good index on the correlation column (customer_id, department_id, etc.), each execution might take 0.1ms. Total: 50 seconds for a single query. That's not acceptable for most use cases.
The critical optimization levers are:
1. Index the correlation column in the inner query's table
The WHERE clause in the inner query almost always looks like inner_table.fk_column = outer_table.pk_column. The fk_column in the inner table needs an index. If it's already a foreign key, it's probably indexed. If not, you're doing full table scans on the inner table for every outer row. You can learn how this works mechanically in SQL indexes explained: how they work and when to create them.
-- Make sure this index exists:
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
2. Rewrite as a JOIN when possible
Many correlated subqueries in WHERE can be rewritten as a join against an aggregated derived table:
-- Correlated subquery version (runs N times):
SELECT e.employee_id, e.salary
FROM employees e
WHERE e.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.department_id = e.department_id
);
-- JOIN version (runs once, then joins):
SELECT e.employee_id, e.salary
FROM employees e
JOIN (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
) dept_avgs ON dept_avgs.department_id = e.department_id
WHERE e.salary > dept_avgs.avg_salary;
The JOIN version computes department averages once (not once per employee), then joins the result to filter employees. For large tables, this is dramatically faster. The correlated subquery version might be fine for 1,000 employees. For 100,000 employees, the JOIN version could be 100x faster.
3. Use CTEs to materialize intermediate results
When you need the same correlated lookup in multiple places:
-- Instead of three correlated subqueries:
WITH dept_stats AS (
SELECT
department_id,
AVG(salary) AS avg_salary,
MAX(salary) AS max_salary,
COUNT(*) AS headcount
FROM employees
GROUP BY department_id
)
SELECT
e.employee_id,
e.first_name,
e.salary,
ds.avg_salary,
ds.max_salary,
ds.headcount
FROM employees e
JOIN dept_stats ds ON ds.department_id = e.department_id
WHERE e.salary > ds.avg_salary;
This is cleaner, faster, and more maintainable. The CTE runs once and its results are available everywhere in the query.
Warning
Don't assume your query optimizer will automatically transform your correlated subquery into a JOIN. While modern optimizers (especially PostgreSQL 12+ and SQL Server 2019+) do this reliably for simple cases, complex correlated subqueries with multiple joins, LIMIT clauses, or aggregates in the inner query often remain as loop executions. Always check the query plan with EXPLAIN (or EXPLAIN ANALYZE in PostgreSQL) before assuming optimization happened.
For a significant class of problems that correlated subqueries solve, window functions are a superior alternative — both in readability and performance. Understanding where the line falls will make you a much more effective SQL author.
Window functions give you access to aggregated context without a subquery and without collapsing rows. They're computed in a single pass over the data with partitioning logic.
-- Correlated subquery approach: employees above department average
SELECT
e.employee_id,
e.salary,
e.department_id
FROM employees e
WHERE e.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.department_id = e.department_id
);
-- Window function approach:
SELECT employee_id, salary, department_id
FROM (
SELECT
employee_id,
salary,
department_id,
AVG(salary) OVER (PARTITION BY department_id) AS dept_avg
FROM employees
) ranked
WHERE salary > dept_avg;
The window function version scans the employees table exactly once. The correlated subquery version scans it once per employee for the inner query plus once for the outer query. For this pattern specifically, the window function is almost always better.
Use correlated subqueries over window functions when:
Use window functions over correlated subqueries when:
Work through these three problems. Each one requires a correlated subquery. Write your solution before reading the hints.
Setup (you can run this on any PostgreSQL or SQLite instance):
CREATE TABLE departments (
dept_id INTEGER PRIMARY KEY,
dept_name TEXT,
budget NUMERIC(12, 2)
);
CREATE TABLE employees (
emp_id INTEGER PRIMARY KEY,
dept_id INTEGER,
full_name TEXT,
hire_date DATE,
salary NUMERIC(10, 2),
is_active BOOLEAN DEFAULT TRUE
);
CREATE TABLE projects (
project_id INTEGER PRIMARY KEY,
dept_id INTEGER,
project_name TEXT,
start_date DATE,
end_date DATE,
budget NUMERIC(12, 2)
);
CREATE TABLE project_assignments (
assignment_id INTEGER PRIMARY KEY,
project_id INTEGER,
emp_id INTEGER,
hours_logged NUMERIC(8, 2),
role TEXT
);
Exercise 1 — Scalar comparison: Find all employees whose salary is greater than the average salary of everyone hired in the same calendar year as them. Show employee name, salary, hire year, and the year average.
Hint: Use EXTRACT(YEAR FROM hire_date) or DATE_PART('year', hire_date) to group by year.
-- Your solution here
SELECT
e.full_name,
e.salary,
EXTRACT(YEAR FROM e.hire_date) AS hire_year,
(
SELECT ROUND(AVG(e2.salary), 2)
FROM employees e2
WHERE EXTRACT(YEAR FROM e2.hire_date) = EXTRACT(YEAR FROM e.hire_date)
) AS cohort_avg_salary
FROM employees e
WHERE e.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE EXTRACT(YEAR FROM e2.hire_date) = EXTRACT(YEAR FROM e.hire_date)
)
ORDER BY hire_year, e.salary DESC;
Exercise 2 — EXISTS filter: Find all departments that have at least one active project with a budget exceeding the department's own total headcount cost (sum of salaries of active employees).
-- Your solution here
SELECT d.dept_id, d.dept_name, d.budget
FROM departments d
WHERE EXISTS (
SELECT 1
FROM projects p
WHERE p.dept_id = d.dept_id
AND p.end_date >= CURRENT_DATE -- active project
AND p.budget > (
SELECT COALESCE(SUM(e.salary), 0)
FROM employees e
WHERE e.dept_id = d.dept_id
AND e.is_active = TRUE
)
);
Notice the two-level correlation: the innermost subquery references d.dept_id from the outermost scope.
Exercise 3 — Correlated subquery in SELECT with NOT EXISTS: List all employees along with a flag indicating whether they've been assigned to any project that is still active (end_date >= today). Don't use a JOIN — use only correlated subqueries.
-- Your solution here
SELECT
e.emp_id,
e.full_name,
e.dept_id,
CASE
WHEN EXISTS (
SELECT 1
FROM project_assignments pa
JOIN projects p ON p.project_id = pa.project_id
WHERE pa.emp_id = e.emp_id
AND p.end_date >= CURRENT_DATE
) THEN 'Active Project'
ELSE 'No Active Project'
END AS project_status,
(
SELECT COUNT(DISTINCT pa.project_id)
FROM project_assignments pa
WHERE pa.emp_id = e.emp_id
) AS total_projects_ever
FROM employees e
WHERE e.is_active = TRUE
ORDER BY e.dept_id, e.full_name;
Mistake 1: Forgetting to alias tables in the inner query
-- Problem: no alias on inner query table, column is ambiguous
SELECT p.product_name
FROM products p
WHERE p.unit_price > (
SELECT AVG(unit_price) -- which table's unit_price?
FROM products -- no alias, same table name as outer
WHERE category_id = p.category_id -- this actually works in most databases
-- but is ambiguous and fragile
);
Always add an alias. Always.
Mistake 2: Correlated subquery that returns more than one row in a scalar context
-- This will error in most databases: subquery returns multiple rows
SELECT o.order_id
FROM orders o
WHERE o.customer_id = (
SELECT customer_id -- returns multiple customers!
FROM customers
WHERE country = o.ship_country
);
If the inner query might return multiple rows and you need a scalar comparison, use IN instead of =, or add a LIMIT 1 / TOP 1 with an ORDER BY.
-- Fixed with IN:
SELECT o.order_id
FROM orders o
WHERE o.customer_id IN (
SELECT customer_id
FROM customers
WHERE country = o.ship_country
);
Mistake 3: NULL comparisons collapsing result sets
If the inner query returns NULL (because of an empty result set and an aggregate with no matching rows), the comparison outer_value > NULL evaluates to UNKNOWN, not FALSE. The row is excluded.
-- If no orders exist for a customer, AVG returns NULL
-- And unit_price > NULL is UNKNOWN (excluded from results)
WHERE p.unit_price > (
SELECT AVG(oi.discount_pct)
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
WHERE o.customer_id = c.customer_id
)
Fix with COALESCE:
WHERE p.unit_price > COALESCE((
SELECT AVG(oi.discount_pct)
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
WHERE o.customer_id = c.customer_id
), 0)
Mistake 4: Not reading the query plan
If a correlated subquery is running slowly, your first step is always EXPLAIN ANALYZE. Look for "Seq Scan" inside "Loop" nodes — that's the signature of an un-indexed correlated subquery loop. The fix is almost always an index on the foreign key in the inner table.
Mistake 5: Using correlated subqueries where window functions are more appropriate
If you're computing running totals, period-over-period comparisons, or rank within groups, window functions are the right tool. Correlated subqueries for these cases will be dramatically slower and harder to read. If you're on a learning path that covers window functions next, writing multi-step analytical queries covers where subqueries and window functions intersect.
You've covered substantial ground in this lesson. Let's consolidate what you now know.
The mental model: A correlated subquery creates a bidirectional communication channel between the outer and inner query. The outer query passes row values to the inner query; the inner query returns a computed result that the outer query uses to make a decision. This happens once per row — which is powerful and expensive at the same time.
The four main patterns:
The golden rules:
Correlated subqueries represent a kind of expressive power that SQL doesn't make obvious at first. Once you internalize the execution model — outer row hands a value to the inner query, inner query runs, result comes back — you'll start seeing correlated subquery opportunities everywhere. The discipline is knowing when to use that power and when to trade it for a cleaner, faster alternative.
For your next steps, explore advanced subqueries and CTEs: mastering complex SQL query architecture to see how correlated subqueries can be refactored into maintainable CTE-based solutions. If you're working on complex business reporting, translating business questions into SQL will show you how to systematically decompose requirements into the right query structures — including when correlated subqueries are the most natural translation of what the business is actually asking.