What is a Correlated Subquery?
A subquery that uses values from the outer query.
Simple analogy: For each student, count their own enrollments.
Basic Example
Find students with more than 2 enrollments:
SELECT name
FROM students s
WHERE (
SELECT COUNT(*)
FROM enrollments e
WHERE e.student_id = s.id
) > 2;How it works:
- For each student, count their enrollments
- Show only students with count > 2
Real Example
Find products that sold more than average for their category:
SELECT product_name, sales
FROM products p
WHERE sales > (
SELECT AVG(sales)
FROM products
WHERE category = p.category
);Summary
Correlated = Inner query references outer query Runs once for each row More complex but powerful