What is a Correlated Subquery?
A correlated subquery references the outer query - it runs once for EACH row!
Simple rule: Inner query uses outer query's values (like s.id)
How Correlated Subquery Works
Basic Syntax
SELECT name
FROM students s
WHERE (
SELECT COUNT(*) FROM orders
WHERE orders.student_id = s.id -- References outer!
) > 2;Key: s.id comes from outer query!
Example: Students with 2+ Orders
SELECT name
FROM students s
WHERE (
SELECT COUNT(*) FROM orders o
WHERE o.student_id = s.id
) >= 2;For each student: Count their orders, show if >= 2
Example: Above Category Average
SELECT name, sales, category
FROM products p
WHERE sales > (
SELECT AVG(sales) FROM products
WHERE category = p.category
);For each product: Compare to its own category average
Correlated vs Regular Subquery
- Regular → Runs once, returns fixed value
- Correlated → Runs per row, returns different value each time
Try It Below
Practice with pre-computed correlated results!
Congratulations!
You completed all Subquery types! 🎉