Correlated Subquery in MySQL

Correlated Subquery

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
);

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".

Correlated Subquery

Example

-- अपनी class के average से ज़्यादा अंक वाले students
SELECT name, class, marks FROM students s1
WHERE marks > (
  SELECT AVG(marks) FROM students s2
  WHERE s2.class = s1.class
);

Normal बनाम Correlated

• Normal subquery: एक बार चलती है, स्वतंत्र।
• Correlated: हर outer row के लिए चलती है, outer values use करती है (यहाँ s1.class)।

सारांश

• Correlated subquery outer query को reference करती है और हर row के लिए चलती है।
• "अपनी class average से ऊपर" जैसे per-group comparisons के लिए उपयोगी।