Correlated Subqueries
A correlated subquery references columns from the outer query. It runs once for EACH row of the outer query - making it powerful but potentially slow on large tables. Correlated subqueries express row-by-row conditions impossible to express otherwise.
Correlated Subquery Characteristics
- References outer query table with a column from the outer query
- Executes once per row of the outer query (N executions for N rows)
- Can be slow - O(N) subquery executions
- Cannot be flattened into a simple list like non-correlated subqueries
- Common use: compare each row to rows in the same or related table
Correlated Subquery Examples
-- Orders where amount > average for THAT user
-- The subquery references o1.user_id from the outer query
SELECT o1.id, o1.user_id, o1.total_amount
FROM orders o1
WHERE o1.total_amount > (
SELECT AVG(o2.total_amount)
FROM orders o2
WHERE o2.user_id = o1.user_id -- ← correlated reference!
);
-- Products priced above average for their category
-- (using hypothetical category column)
SELECT p1.name, p1.price
FROM products p1
WHERE p1.price > (
SELECT AVG(p2.price) FROM products p2
);
-- (simplified - scalar subquery runs once since no outer reference here)
-- Correlated: for each user, get their most recent order date
SELECT
u.name,
(SELECT MAX(o.created_at)
FROM orders o
WHERE o.user_id = u.id) AS last_order_date
FROM users u;
-- Anti-correlated: users with no orders (EXISTS pattern)
SELECT u.name FROM users u
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id
);Quick Quiz
Tip
Tip
Practice Correlated Subqueries in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Inner query runs first, results used by outer query. CTEs are often more readable.
Common Mistake
Warning
A common mistake with Correlated Subqueries is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of Correlated Subqueries from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- A correlated subquery references columns from the outer query.
- References outer query table with a column from the outer query
- Executes once per row of the outer query (N executions for N rows)
- Can be slow - O(N) subquery executions