Correlated Subqueries
intermediatesubqueries
2 min read
What is a Correlated Subquery?
A correlated subquery references one or more columns from the outer query. Unlike a regular subquery that runs once, a correlated subquery is conceptually evaluated once for each row of the outer query.
Syntax
SELECT *
FROM table_a a
WHERE column > (
SELECT AVG(column) FROM table_a b WHERE b.category = a.category
);When to Use
- Comparing a row's value to an aggregate of its group
- Finding rows that are above/below their group's average
- Existence checks with EXISTS
Key Points
- References Outer Query — The inner SELECT uses columns from the outer query (e.g., a.category).
- Conceptual Per-Row Execution — Logically runs for each outer row, though the optimizer may find shortcuts.
- EXISTS Pattern — The most common correlated subquery uses EXISTS.
- Performance — Can be slower than equivalent joins for large datasets.
- Alternatives — Window functions and lateral joins can often replace correlated subqueries more efficiently.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
Correlated Subqueries Challenge
Write a query that find students older than the average age in their grade group.
Expected result
Students whose age exceeds the average age of students in the same grade.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What is a Correlated Subquery?
Initializing database...Each run starts from fresh sample data.
More Examples
Correlated EXISTS
Find courses that have at least one enrollment.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
Are correlated subqueries always slow?
Not necessarily. PostgreSQL's optimizer can often flatten them into joins. Use EXPLAIN ANALYZE to check the actual performance.
Can I avoid correlated subqueries?
Often yes. Window functions (e.g., AVG() OVER(PARTITION BY category)) or LATERAL joins can replace many correlated subqueries more efficiently.