Correlated Subquery in MySQL

Correlation Explained

A correlated subquery reaches outside its own query block and uses a value from the current outer row. The outer alias gives that reference a precise scope.

SELECT o.student_name
FROM sq_students AS o
WHERE o.marks > (
  SELECT AVG(i.marks)
  FROM sq_students AS i
  WHERE i.class_name = o.class_name
);

Shared Verified Dataset

X-A average is 88.75. X-B average is 81.25 because AVG ignores NULL. Keep those two checkpoints visible while verifying the next outputs.

Students Above Their Own Class Average

SELECT o.student_name, o.class_name, o.marks
FROM sq_students AS o
WHERE o.marks > (
  SELECT AVG(i.marks)
  FROM sq_students AS i
  WHERE i.class_name = o.class_name
)
ORDER BY o.class_name, o.student_id;

This is different from comparing with the overall 85.00 average. Aarav is above the overall average but below X-A's stronger average, so the correlated result correctly excludes Aarav.

Top Known Mark in Each Class

SELECT o.student_name, o.class_name, o.marks
FROM sq_students AS o
WHERE o.marks = (
  SELECT MAX(i.marks)
  FROM sq_students AS i
  WHERE i.class_name = o.class_name
)
ORDER BY o.class_name;

If two students tie for the maximum, both are returned. That is usually correct for “all top scorers.” Use a window-function tie-breaking rule only when the requirement demands exactly one row.

Correlated Scalar Count

SELECT o.student_name, o.class_name,
       (
         SELECT COUNT(*)
         FROM sq_students AS i
         WHERE i.class_name = o.class_name
       ) AS class_strength
FROM sq_students AS o
ORDER BY o.student_id;

COUNT(*) counts rows, not known marks. To count assessed students, use COUNT(i.marks), which would return 2 for X-B.

Rewrite with an Aggregated Derived Table

SELECT s.student_name, s.class_name, s.marks
FROM sq_students AS s
JOIN (
  SELECT class_name, AVG(marks) AS class_average
  FROM sq_students
  GROUP BY class_name
) AS a
  ON a.class_name = s.class_name
WHERE s.marks > a.class_average
ORDER BY s.class_name, s.student_id;

The result remains Meera and Sana. This form calculates one aggregate row per class and makes the relationship visible. It may be easier to extend when the report needs the average column too. Do not claim it is always faster; compare actual plans.

Alias Scope, NULL and Performance

• Use different aliases for outer and inner instances of the same table.
• Inner name resolution searches the nearest query block first; qualify every repeated column.
• NULL outer marks fail ordinary comparisons; aggregate functions ignore NULL inputs according to their rules.
• Index the correlation key when useful, but small tables may still be scanned efficiently.
• Inspect EXPLAIN and representative timings. The optimizer may decorrelate eligible queries.

Practice

1. Return students below their own class average.
2. Show each row with the count of known marks in its class.
3. Add another student tied with Sana and verify that the top-per-class query returns both.
4. Rewrite the top query using a grouped derived table.

For every answer, write X-A and X-B checkpoints first. Correlated logic becomes reliable when the per-group expected result is known before execution.

Official References

• MySQL 8.4: Correlated Subqueries
• MySQL 8.4: Subquery Optimization
• MySQL 8.4: EXPLAIN Statement

Syntax and behavior were checked against the official MySQL 8.4 manual. Test every query on a disposable copy before production use.