Correlated Subqueries in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this Subqueries in WHERE (IN, EXISTS, ANY, ALL), Self-Joins
Builds toward LATERAL Joins, Query Structure Patterns for Performance, Choosing Between Subqueries, CTEs, and Joins
What are Correlated Subqueries in SQL?
A correlated subquery re-runs for each row the outer query processes, using that row's values as input. The subquery's result is different for every outer row — it's not a fixed value computed once and reused.
The clearest use case: find every employee earning above the average salary for their own department. The challenge is that "their department" changes for every employee. Computing one average for the whole table isn't useful — you need a per-department average, evaluated row by row.
How do you compare a row to its own group average?
With a correlated subquery, you compute the average once per employee, dynamically, using that employee's department:
SELECT
e.employee_id,
e.name,
e.salary,
e.department_id
FROM employees e
WHERE e.salary > (
SELECT AVG(salary)
FROM employees dept_avg
WHERE dept_avg.department_id = e.department_id
)For each employee row, the subquery runs with that employee's department_id. Employee 12 in department 3 triggers the subquery with department_id = 3. Employee 45 in department 7 triggers it with department_id = 7. The outer WHERE uses whichever result corresponds to the current row.
Can a correlated subquery go in the SELECT list?
Correlated subqueries also work in the SELECT list to compute a value per outer row:
SELECT
d.department_id,
d.department_name,
(
SELECT COUNT(*)
FROM employees e
WHERE e.department_id = d.department_id
) AS headcount
FROM departments dFor each department, the subquery counts its employees and attaches the result as a column. This is equivalent to a LEFT JOIN with GROUP BY — same output, different structure. The correlated form is sometimes cleaner when you only need one computed value per outer row and the join version would add complexity.
Here's the SELECT-list form on real data:
SELECT c.id AS customer_id, c.name, (SELECT MAX(ordered_at)::date FROM orders o WHERE o.customer_id = c.id) AS last_order_date FROM customers c LIMIT 10
How does EXISTS use a correlated subquery?
EXISTS and correlated subqueries
EXISTS — which you've already seen — relies on this same mechanism. The subquery inside EXISTS references the outer row to check whether any matching rows exist at all:
SELECT customer_id
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
)For each customer, the subquery checks whether any orders reference that customer. EXISTS stops as soon as it finds one match — it doesn't need to count them all. This makes it efficient for "does at least one match exist?" questions.
Why are correlated subqueries slow on large tables?
The one thing that trips people up
Correlated subqueries can be slow on large tables because the subquery executes once per outer row. A query with 500,000 outer rows runs the subquery 500,000 times. PostgreSQL's planner can sometimes rewrite them as joins internally, but it's not guaranteed.
When a correlated subquery can be expressed as a window function or a join, those alternatives are usually faster. Correlated subqueries are most appropriate when the per-row computation is genuinely unique and can't be cleanly expressed as a join without significantly complicating the query.
Practice Correlated Subqueries in SQL
Brightlane's CRM team needs a complete customer activity view that shows the number of orders each customer has placed.
Write a query to return every customer's ID, name, and total order count.
Assumptions:
- A customer's order count is the number of orders linked to that
customer_id. - Every customer must appear, including customers with no orders. Customers with no orders should show an order count of
0.
Output:
- One row per customer, with columns
id,name, andorder_count.
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solutionStation Zero, our free browser SQL game, teaches this concept inside a story. No signup.
correlate a subquery in Station Zero9 Correlated Subqueries practice problems
Write a query to return every customer's ID, name, and total order count.
Write a query to return every customer's ID, name, and highest order amount.
Write a query to return the ID and name of every customer who has placed at least one order.
Write a query to return the order ID, customer ID, and total amount for every order whose total_amount exceeds that same customer's average order amount across all their orders.
Write a query to return the ID and name of every customer who has placed more than 3 orders on record.
Write a query to return every department's ID, name, and total employee count.
Write a query to return every order's ID, customer ID, total amount, and the highest unit price among the items in that order.
Write a query to return the order ID, customer ID, and total amount for every order whose total_amount equals that same customer's highest order amount.
Write a query to return the product ID, category ID, name, and price for every product whose price equals the highest price within its own category.
Start learning to practice all 9 Correlated Subqueries problems, with instant grading and mastery tracking.
Common questions about Correlated Subqueries
Can a correlated subquery reference more than one outer column?
Yes, as many as the comparison needs. Matching on both the customer and the status is ordinary, and each extra reference simply narrows what the inner query computes for that row.
Can you always rewrite a correlated subquery as a join?
Usually, by aggregating the inner query once per key and joining to that result. The rewritten form returns the same rows and does the aggregation once instead of once per outer row, which is why it is the standard move when a plan shows the subquery running too often.
Is EXISTS a correlated subquery?
Almost always, yes. The subquery inside EXISTS references the outer row to decide whether any match exists for it, which is what correlation means. EXISTS is simply the form that asks whether anything came back rather than what it was.
