MySQL + SQL · Lesson 72

Correlated Subquery in MySQL

Correlated Subquery

A correlated subquery depends on the outer query — it runs once for each outer row, using a value from that row.

Example

-- students scoring above their own class average
SELECT name, class, marks FROM students s1
WHERE marks > (
  SELECT AVG(marks) FROM students s2
  WHERE s2.class = s1.class
);

The inner query re-runs per student, comparing to that student's class average.

Normal vs Correlated

Normal subquery: runs once, independent.
Correlated: runs per outer row, uses outer values (here s1.class).

Summary

A correlated subquery references the outer query and runs per row.
Useful for per-group comparisons like "above own class average".