MySQL में Correlated Subquery

Correlation समझें

Correlated subquery अपने query block से बाहर current outer row की value use करती है। Outer alias उस reference का scope precise बनाता है।

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 88.75 और X-B average 81.25 है क्योंकि AVG NULL ignore करता है। Next outputs verify करते समय ये checkpoints सामने रखें।

अपनी Class Average से ऊपर Students

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;

यह overall 85.00 average से अलग है। Aarav overall average से ऊपर लेकिन X-A के stronger average से नीचे है, इसलिए correlated result सही रूप से Aarav को exclude करता है।

हर Class का Top Known Mark

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;

Maximum पर two students tie करें तो दोनों return होंगे। “All top scorers” के लिए यही सही है। Exactly one row चाहिए तभी window-function tie-break rule लें।

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(*) rows count करता है, known marks नहीं। Assessed students count करने के लिए COUNT(i.marks) लें; X-B के लिए 2 मिलेगा।

Aggregated Derived Table से Rewrite

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;

Result Meera और Sana ही है। यह form one aggregate row per class बनाता और relationship visible रखता है। Report में average column भी चाहिए तो इसे extend करना आसान है। इसे always faster न मानें; actual plans compare करें।

Alias Scope, NULL और Performance

• Same table की outer/inner instances के लिए अलग aliases लें।
• Inner name resolution nearest query block पहले देखता है; repeated columns qualify करें।
• NULL outer marks ordinary comparison fail करते हैं; aggregates अपने rules से NULL inputs ignore करते हैं।
• Useful हो तो correlation key index करें; small tables efficiently scan भी हो सकती हैं।
• EXPLAIN और representative timings देखें; optimizer eligible query decorrelate कर सकता है।

अभ्यास

1. अपनी class average से नीचे students लौटाएँ।
2. हर row के साथ class का known-marks count दिखाएँ।
3. Sana के equal another student जोड़कर top-per-class query में दोनों verify करें।
4. Top query को grouped derived table से rewrite करें।

हर answer से पहले X-A और X-B checkpoints लिखें। Per-group expected result पहले known हो तो correlated logic reliable बनती है।

Official संदर्भ

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

Syntax और behavior official MySQL 8.4 manual से जाँचे गए हैं। Production से पहले हर query disposable copy पर test करें।