انتقال للمقال
فهرس المحتويات
وقت القراءة: ≈ 15 دقيقة

شرح وتطبيق على الـ Subquery في الـ SQL

السلام عليكم ورحمة الله وبركاته

يمكنك متابعة السلسلة بالترتيب أو الانتقال مباشرة إلى أي مقال:

المقدمة

في هذه المقالة سنتحدث عن أحد مميزات الـ SQL القوية جدًا وهي الـ Subquery
وهى القدرة على وضع Query داخل Query أخرى لكي نحصل على نتائج معقدة وتعتمد على نتائح من Query أخرى

ذكرنا في مقالة سابقة سؤال وهو إيجاد اسم الطالب صاحب أعلى درجة
الحل كان بسيط فقط باستخدام ORDER BY و LIMIT

SELECT name, grade
FROM Students
ORDER BY grade DESC
LIMIT 1;

الاجابة كانت:

+----------------+-------+
| name           | grade |
+----------------+-------+
| Ismail Khaled  |  95.0 |
+----------------+-------+

لكن لنغير السؤال قليلاً هذه المرة ونقول نريد جميع أسماء الطلاب الذين حصلوا على أعلى درجة وهو لنتخيل أن هناك أكثر من طالب حصل على نفس الدرجة 95.0 مثلاً وكانت هذه أعلى درجة
الـ Query السابقة لن تعمل هنا لأنها تُرجع صفًا واحدًا فقط بسبب استخدام LIMIT 1
ولا نستطيع تغير LIMIT ليرجع أكثر من صف لأننا لا نعرف كم عدد الطلاب الذين حصلوا على أعلى درجة
ولا يمكننا كتابة شرط يدويًا مثل WHERE grade = 95.0 لأننا لا نعرف ما هي أعلى درجة من الأساس
فممكن تكون أعلى درجة هي 98.0 أو 100.0 أو أي رقم آخر حسب البيانات وممكن البيانات تتغير بمرور الوقت

للإجابة على هذا نحتاج لكي نرتب أفكارنا قليلاً ونحلل المشكلة
السؤال كان يقول ما هي أسماء الطلاب الذين حصلوا على أعلى درجة ؟
لذا أول شيء نحتاج إلى معرفته هو ما هي أعلى درجة ؟
ثم بعد ذلك نستخدم هذه المعلومة في السؤال الأساسي وهو من هم الطلاب الذين حصلوا على هذه الدرجة ؟

حسنًا بالنسبة للسؤال الأول فنحن نستطيع عمل Query لإيجاد أعلى درجة باستخدام دالة MAX():

SELECT MAX(grade) FROM Students;

النتيجة ستكون:

+------------+
| MAX(grade) |
+------------+
| 95.0       |
+------------+

الآن بعد أن عرفنا أن أعلى درجة هي 95.0، يمكننا استخدام هذه المعلومة في السؤال الأساسي:

SELECT name, grade
FROM Students
WHERE grade = 95.0;

النتيجة هنا ستحضر جميع الطلاب الذين حصلوا على درجة 95.0
لكننا هنا نواجه نفس المشكلة السابقة وهي أننا كتبنا الرقم يدويًا في شرط الـ WHERE
ماذا لو تغيرت البيانات وأصبح هناك طالب جديد حصل على درجة 98.0 ؟

نحتاج لطريقة نستطيع دمج الـ Query التي تحسب أعلى درجة مع الـ Query التي تجلب أسماء الطلاب
وهنا يأتي دور الـ Subquery

تجهيز الجداول للأمثلة العملية

كالعادة قبل أن نبدأ، دعنا نتذكر شكل الجداول التي كنا نعمل عليها في المقالات السابقة

لدينا ثلاث جداول وهم الـ: Students و Instructors و Courses

لنرى شكل جدول الـ Students:

+----+-----------------+------+--------------+-------+-------+--------------------------+------------+
| id | name            | age  | level        | grade | city  | email                    | deleted_at |
+----+-----------------+------+--------------+-------+-------+--------------------------+------------+
| 1  | Ahmed Moustafa  | 21   | Intermediate | 85.5  | Cairo | ahmed@university.edu     | NULL       |
| 2  | Osama Ali       | 21   | Intermediate | 88.0  | Cairo | osama.ali@university.edu | NULL       |
| 3  | Mohamed Adel    | 23   | Advanced     | 92.0  | Alex  | mohamed@university.edu   | NULL       |
| 4  | Kamal Mahmoud   | 22   | Advanced     | 90.0  | Alex  | kamal@university.edu     | NULL       |
| 5  | Ayman Hassan    | 21   | Advanced     | 87.5  | Giza  | ayman@university.edu     | NULL       |
| 6  | Adam Ibrahim    | NULL | Beginner     | 75.0  | Giza  | adam@university.edu      | NULL       |
| 7  | Ismail Khaled   | 23   | Advanced     | 95.0  | Cairo | ismail@university.edu    | NULL       |
+----+-----------------+------+--------------+-------+-------+--------------------------+------------+

وجدول الـ Instructors:

+----+-------------+------------------------+
| id | name        | department             |
+----+-------------+------------------------+
| 1  | Dr. Ahmed   | Computer Science       |
| 2  | Dr. Kamal   | Information Technology |
| 3  | Dr. Mohamed | Software Engineering   |
| 4  | Dr. Khaled  | Computer Science       |
| 5  | Dr. Ali     | Data Science           |
+----+-------------+------------------------+

وأخيرًا جدول الـ Courses:

+----+-------------------------+---------------+----------------+
| id | name                    | instructor_id | duration_hours |
+----+-------------------------+---------------+----------------+
| 1  | Database Fundamentals   | 1             | 40             |
| 2  | Web Development         | 2             | 60             |
| 3  | Python Programming      | 3             | 45             |
| 4  | Data Structures         | 4             | 50             |
| 5  | Artificial Intelligence | 1             | 55             |
| 6  | Machine Learning        | 3             | 55             |
| 7  | Data Science Basics     | NULL          | 30             |
+----+-------------------------+---------------+----------------+

هذه الجداول التي سنستخدمها في الأمثلة العملية للـ Subquery في هذه المقالة

ما هو الـ Subquery ؟

الـ Subquery هو Query موجودة داخل Query أخرى
بعض الأسئلة والمشاكل التي ستواجهها تحتاج إلى أكثر من خطوة لحلها
تحتاج إلى تنفيذ Query أولاً للحصول على نتيجة معينة، ثم استخدام هذه النتيجة في Query أخرى
الـ Subquery يسمح لك بفعل ذلك بسهولة

مثل ما سألنا سابقًا وقلنا ما هي أسماء الطلاب الذين حصلوا على أعلى درجة ؟
قلنا أننا لدينا سؤالين هنا وهما ما هي أعلى درجة ؟ و من هم الطلاب الذين حصلوا على هذه الدرجة ؟
وكلا السؤالين يحتاجان ل تنفيذ Query منفصلة
في هذه الحالة، الـ Subquery هو الحل الأمثل

هل تتذكر الـ Query التي حسبت أعلى درجة ؟

SELECT MAX(grade) FROM Students;

ثم كانت النتيجة 95.0 ثم كتبناها يدويًا في الـ Query الثانية بهذا الشكل WHERE grade = 95.0
ما رأيك بأننا بدلًا من أن نكتب الرقم يدويًا في الـ WHERE، نستخدم الـ Query التي حسبت أعلى درجة مباشرة هناك داخل شرط الـ WHERE
بهذا الشكل WHERE grade = (SELECT MAX(grade) FROM Students)
لاحظ أننا كل ما فعلناه هو أننا استبدلنا الرقم 95.0 بالـ Query التي تحسب أعلى درجة

ليكون الـ Query النهائية بهذا الشكل:

SELECT name, grade
FROM Students
WHERE grade = (SELECT MAX(grade) FROM Students);

لاحظ أن الجزء (SELECT MAX(grade) FROM Students) هو الـ Subquery
وهى موجودة داخل الـ Query الرئيسية التي تجلب أسماء الطلاب

هكذا الـ Subquery التي في الـ WHERE ستنفذ أولًا لتحسب أعلى درجة، أيً ما كانت تلك القيمة
ثم الناتج التي سترجعها الـ Subquery سيتم استخدامه في الـ WHERE في الـ Query الرئيسية

هكذا في حالة كانت هناك أكثر من طالب حصل على نفس أعلى درجة، فسيتم جلب جميع أسمائهم
ولاحظ أن شرط الـ WHERE أصبح شرطًا ديناميكيًا يعتمد على نتيجة الـ Subquery بدلاً من كتابة قيمة ثابتة يدويًا

الـ Subquery يمكن استخدامها في أماكن متعددة داخل الـ SQL سواء مع الـ WHERE أو مع الـ SELECT أو حتى مع الـ FROM
في هذه المقالة سنشرح بالتفصيل كيفية استخدام الـ Subquery في كل هذه الأماكن مع أمثلة عملية خطوة بخطوة

استخدام Subquery مع الـ WHERE

الاستخدام الأكثر شيوعًا للـ Subquery هو داخل أمر الـ WHERE
حيث نستخدم نتيجة الـ Subquery كشرط للتصفية في الـ Query الرئيسية
وهو ما فعلناه في المثال السابق

لنفترض أننا نريد معرفة الدورات التي مدتها أكبر من متوسط مدة جميع الدورات

أولاً، لنفكر في كيفية عمل Query لحساب متوسط مدة جميع الدورات من الأساس لنستخدما في الشرط:

SELECT AVG(duration_hours) FROM Courses;

النتيجة:

+---------------------+
| AVG(duration_hours) |
+---------------------+
| 47.8571             |
+---------------------+

حسنًا، متوسط مدة الدورات هو تقريبًا 47.86 ساعة
لاحظ أنه بسبب أننا نستخدم دوال الـ Aggregation مثل AVG()، النتيجة هي قيمة واحدة فقط في صف واحد
بالتالي يمكننا استخدام هذه القيمة في شرط الـ WHERE مع أي عملية مقارنة مثل > أو < أو = وغيرها

الآن نريد الدورات التي مدتها أكبر من هذا المتوسط، وهنا نستخدم الـ Query التي حسبت المتوسط داخل الـ WHERE بهذا الشكل:

SELECT name, duration_hours
FROM Courses
WHERE duration_hours > (SELECT AVG(duration_hours) FROM Courses);

لاحظ كيف وضعنا Query حساب المتوسط داخل أقواس في شرط الـ WHERE
الـ SQL سيقوم أولاً بتنفيذ الـ Subquery للحصول على المتوسط
ثم يستخدم هذه القيمة في الـ Query الخارجية للتصفية

النتيجة:

+-------------------------+----------------+
| name                    | duration_hours |
+-------------------------+----------------+
| Web Development         | 60             |
| Data Structures         | 50             |
| Artificial Intelligence | 55             |
| Machine Learning        | 55             |
+-------------------------+----------------+

الآن حصلنا على جميع الدورات التي مدتها أكبر من متوسط مدة الدورات
وهذا بفضل استخدام الـ Subquery في الـ WHERE

عندما تستخدم الـ Subquery في الـ WHERE مع عمليات مقارنة مثل = أو > أو < وغيرها
تأكد من أن الـ Subquery يرجع قيمة واحدة فقط في صف واحد فقط
لأنه في حالة إذا رجع أكثر من صف أو أكثر من عمود فستواجه خطأ في الـ SQL لأنه لا يمكن مقارنة قيمة واحدة مع عدة قيم في نفس الوقت

SQL Error: Subquery returns more than 1 row

استخدام IN و NOT IN في الـ Subquery

لاحظ أننا في الأمثلة السابقة كنا نستخدم دوال Aggregation التي ترجع قيمة واحدة فقط
وكان من السهل استخدام هذه القيمة في شرط الـ WHERE مع عمليات مقارنة مثل = أو > أو < وغيرها
لكن ماذا لو كانت الـ Subquery ترجع أكثر من قيمة واحدة ؟

عندما يرجع الـ Subquery أكثر من قيمة واحدة، لا يمكننا استخدام = للمقارنة
بل نستخدم الـ IN للتحقق من وجود القيمة ضمن مجموعة القيم

لنفترض أننا نريد معرفة أسماء المحاضرين الذين يشرفون على دورة واحدة على الأقل

تذكر أن جدول الـ Courses يحتوي على عمود instructor_id الذي يشير إلى المحاضر المسؤول عن كل دورة
ولدينا جدول الـ Instructors الذي يحتوي على أسماء المحاضرين

إذا رجعت لجدول الـ Instructors، ستجد أن هناك محاضر اسمه Dr. Ali ليس لديه أي دورة مرتبطة به في جدول الـ Courses
لذا علينا استثنائه من النتيجة
وأيضًا علينا التأكد أن جميع المحاضرين الذين نريد جلب أسمائهم لديهم دورات مرتبطة بهم في جدول الـ Courses

لذا نحتاج أولاً إلى Query تجلب لنا جميع أرقام المحاضرين الذين لديهم دورات:

SELECT instructor_id
FROM Courses
WHERE instructor_id IS NOT NULL;

بما أن جدول الـ Courses يحتفظ بالفعل بالـ instructor_id فسنتطيع جلب هذه القيم بسهولة بحيث لا تكون NULL

النتيجة ستكون:

+---------------+
| instructor_id |
+---------------+
| 1             |
| 2             |
| 3             |
| 4             |
| 1             |
| 3             |
+---------------+

الآن نحن لدينا قائمة بأرقام المحاضرين الذين لديهم دورات
نستطيع استخدام هذه القائمة في الـ Query الرئيسية لجلب أسماء هؤلاء المحاضرين

SELECT name
FROM Instructors
WHERE id IN (
    SELECT instructor_id
    FROM Courses
    WHERE instructor_id IS NOT NULL
);

لاحظ أنه بما أن الـ Subquery ترجع عدة قيم في عمود واحد فلا يمكننا استخدام = بل يمكننا استخدام IN للتحقق من أن id للمحاضر موجود ضمن القيم التي ترجعها الـ Subquery

النتيجة:

+-------------+
| name        |
+-------------+
| Dr. Ahmed   |
| Dr. Kamal   |
| Dr. Mohamed |
| Dr. Khaled  |
+-------------+

لاحظ أن Dr. Ali لم يظهر لأنه ليس لديه أي دورة مرتبطة به
وإذا أردت العكس، أي جلب المحاضرين الذين ليس لديهم دورات، يمكننا استخدام NOT IN بدلاً من IN

SELECT name
FROM Instructors
WHERE id NOT IN (
    SELECT instructor_id
    FROM Courses
    WHERE instructor_id IS NOT NULL
);

النتيجة ستكون:

+---------+
| name    |
+---------+
| Dr. Ali |
+---------+

هكذا حصلنا على المحاضر الذي ليس لديه أي دورة مرتبطة به

عندما تتعامل مع Subquery باستخدام IN أو NOT IN تأكد من أن الـ Subquery يرجع عمود واحد فقط
لأنه عندما يرجع أكثر من عمود ستواجه خطأ في الـ SQL لأنه لا يمكننا تنفيذ IN أو NOT IN مع عدة أعمدة في نفس الوقت

SQL Error: Operand should contain 1 column(s)

استخدام EXISTS و NOT EXISTS في الـ Subquery

الـ EXISTS يتحقق من وجود صفوف في نتيجة الـ Subquery بغض النظر عن القيم التي ترجعها
إذا كان هناك صف واحد على الأقل فسوف يرجع TRUE وإذا لم يكن هناك أي صفوف فسيرجع FALSE

في المثال السابق، عندما أردنا جلب أسماء المحاضرين الذين لديهم دورات، كان بإمكاننا استخدام EXISTS بدلاً من IN
بهذا الشكل

SELECT name
FROM Instructors AS i
WHERE EXISTS (
    SELECT 1
    FROM Courses AS c
    WHERE c.instructor_id = i.id
);

هنا ستلاحظ أولًا أننا استخدمنا اسم مستعار i لجدول الـ Instructors و c لجدول الـ Courses
ثم في الـ Subquery لدينا شرط WHERE c.instructor_id = i.id بمعنى أننا نتحقق من وجود دورة في جدول الـ Courses حيث يكون instructor_id يساوي id للمحاضر الحالي من جدول الـ Instructors
هنا مع كل صف في جدول Instructors سيتم تنفيذ الـ Subquery للتحقق من وجود دورات مرتبطة بهذا المحاضر أم لا
ولاحظ أننا داخل الـ Subquery نستطيع الوصول إلى أعمدة من الجدول الخارجي

وأخيرًا تتذكر حينا قلنا أن EXISTS لا يهتم بالقيم التي ترجعها الـ Subquery بل فقط يهتم هل يوجد صفوف أم لا
لذا استخدمنا SELECT 1 في الـ Subquery لأننا لا نريد عمود معين بل فقط نريد التحقق من وجود صفوف لذا SELECT 1 تعني أن ترجع قيمة ثابتة 1 لكل صف موجود
وهذا يقلل من المساحة ويحسن الأداء قليلاً مقارنة بـ SELECT * التي ترجع كل الأعمدة

النتيجة ستكون نفس النتيجة السابقة:

+-------------+
| name        |
+-------------+
| Dr. Ahmed   |
| Dr. Kamal   |
| Dr. Mohamed |
| Dr. Khaled  |
+-------------+

وبالطبع إذا أردنا جلب المحاضرين الذين ليس لديهم دورات، يمكننا استخدام NOT EXISTS بدلاً من EXISTS:

SELECT name
FROM Instructors AS i
WHERE NOT EXISTS (
    SELECT 1
    FROM Courses AS c
    WHERE c.instructor_id = i.id
);

النتيجة:

+---------+
| name    |
+---------+
| Dr. Ali |
+---------+

ملحوظة: تذكر أننا نستخدم SELECT 1 في الـ Subquery لأننا لا نهتم بالقيم المُرجعة
نحن فقط نريد معرفة هل يوجد صفوف أم لا
يمكنك استخدام SELECT * أو SELECT id أو SELECT 'I LOVE THE BATATA' ولن يؤثر ذلك على النتيجة النهائية

استخدام ANY و ALL في الـ Subquery

أحيانًا نصادف أسئلة تحتاج منا أن نتحقق من أن القيمة أكبر من أي قيمة في المجموعة أو أكبر من كل القيم في المجموعة

الـ ANY تعني أي قيمة من القيم التي ترجعها الـ Subquery
بينما الـ ALL تعني كل القيم التي ترجعها الـ Subquery

لنفترض أننا نريد معرفة الطلاب الذين درجتهم أعلى من أي طالب في مدينة Giza

أولاً، دعنا نرى ما هي درجات الطلاب في مدينة Giza:

SELECT grade
FROM Students
WHERE city = 'Giza';

النتيجة:

+-------+
| grade |
+-------+
| 87.5  |
| 75.0  |
+-------+

الآن نريد جلب الطلاب الذين درجتهم أعلى من أي درجة من هاتين الدرجتين
بمعنى أن درجتهم أكبر من 75.0 أو أكبر من 87.5 أو أكبر من كليهما

هنا نستخدم ANY بهذا الشكل:

SELECT name, grade
FROM Students
WHERE grade > ANY (
    SELECT grade
    FROM Students
    WHERE city = 'Giza'
);

لاحظ أننا استخدمنا > ANY بمعنى أن الدرجة يجب أن تكون أكبر من أي قيمة من القيم التي ترجعها الـ Subquery
بالتالي يجب أن تكون الدرجة أكبر من 75.0 أو 87.5 على الأقل
أو بمعنى آخر، الدرجة يجب أن تكون أكبر من 75.0 لأنها أصغر قيمة في المجموعة

النتيجة:

+-----------------+-------+
| name            | grade |
+-----------------+-------+
| Ahmed Moustafa  | 85.5  |
| Osama Ali       | 88.0  |
| Mohamed Adel    | 92.0  |
| Kamal Mahmoud   | 90.0  |
| Ayman Hassan    | 87.5  |
| Ismail Khaled   | 95.0  |
+-----------------+-------+

الآن ماذا لو أردنا الطلاب الذين درجتهم أعلى من كل طلاب مدينة Giza ؟
هنا نحتاج أن تكون درجة الطالب أكبر من جميع القيم في المجموعة وليس فقط أكبر من أي قيمة
هنا نستخدم ALL بدلاً من ANY:

SELECT name, grade
FROM Students
WHERE grade > ALL (
    SELECT grade
    FROM Students
    WHERE city = 'Giza'
);

الآن الـ > ALL تعني هنا أن الدرجة يجب أن تكون أكبر من كل القيم التي ترجعها الـ Subquery
بالتالي يجب أن تكون الدرجة أكبر من 87.5 و 75.0 في نفس الوقت
بمعنى أن الدرجة يجب أن تكون أكبر من 87.5 لأنها أكبر قيمة في المجموعة

النتيجة:

+----------------+-------+
| name           | grade |
+----------------+-------+
| Osama Ali      | 88.0  |
| Mohamed Adel   | 92.0  |
| Kamal Mahmoud  | 90.0  |
| Ismail Khaled  | 95.0  |
+----------------+-------+

لاحظ الطالبين Ahmed Moustafa و Ayman Hassan لم يظهروا هذه المرة في النتيجة لأن درجاتهم ليست أكبر من 87.5

يمكننا استخدام ANY و ALL مع أي عملية مقارنة مثل > أو < أو = أو >= أو <= أو <>

ملحوظة: استخدام = ANY يعادل تمامًا استخدام IN
وبالمثل استخدام <> ALL يعادل تمامًا استخدام NOT IN
ولاحظ أن Subquery المستخدمة مع ANY و ALL يجب أن ترجع عمود واحد فقط
وفي حالة إذا رجعت أكثر من عمود ستواجه خطأ في الـ SQL مثل ما حصلنا سابقًا مع IN و NOT IN

استخدام Subquery مع الـ FROM

أي ناتج من أي Query في الـ SQL هو في النهاية جدول
بالتالي يمكننا استخدام الـ Subquery لعمل جدول مؤقت ونجعل الـ FROM تشير إلى هذا الجدول

قبل أن نسأل السؤال، دعنا تتذكر جدول الـ Students:

+----+-----------------+------+--------------+-------+-------+--------------------------+------------+
| id | name            | age  | level        | grade | city  | email                    | deleted_at |
+----+-----------------+------+--------------+-------+-------+--------------------------+------------+
| 1  | Ahmed Moustafa  | 21   | Intermediate | 85.5  | Cairo | ahmed@university.edu     | NULL       |
| 2  | Osama Ali       | 21   | Intermediate | 88.0  | Cairo | osama.ali@university.edu | NULL       |
| 3  | Mohamed Adel    | 23   | Advanced     | 92.0  | Alex  | mohamed@university.edu   | NULL       |
| 4  | Kamal Mahmoud   | 22   | Advanced     | 90.0  | Alex  | kamal@university.edu     | NULL       |
| 5  | Ayman Hassan    | 21   | Advanced     | 87.5  | Giza  | ayman@university.edu     | NULL       |
| 6  | Adam Ibrahim    | NULL | Beginner     | 75.0  | Giza  | adam@university.edu      | NULL       |
| 7  | Ismail Khaled   | 23   | Advanced     | 95.0  | Cairo | ismail@university.edu    | NULL       |
+----+-----------------+------+--------------+-------+-------+--------------------------+------------+

الآن لنسأل سؤال بسيط وهو أننا نريد متوسط الدرجات لكل مستوى تعليمي
مثلًا نريد متوسط درجات جميع الطلاب في مستوى Beginner، ومتوسط درجات جميع الطلاب في مستوى Intermediate، وهكذا

هنا نستخدم دالة AVG() مع GROUP BY لحساب متوسط الدرجات لكل مستوى تعليمي

SELECT level, AVG(grade) AS average_grade
FROM Students
GROUP BY level;

النتيجة ستكون:

+--------------+---------------+
| level        | average_grade |
+--------------+---------------+
| Intermediate | 86.75         |
| Advanced     | 91.125        |
| Beginner     | 75.0          |
+--------------+---------------+

هذا كان سؤال بسيط ولم نحتاج إلى Subquery فيه
لكن ماذا لو أردنا أعلى متوسط بين كل المستويات التعليمية ؟ هنا نحتاج إلى حساب متوسط الدرجات لكل مستوى تعليمي أولاً كما فعلنا للتو في الـ Query السابقة
ثم بعد ذلك نحتاج إلى إيجاد أعلى متوسط من بين هذه النتائج

لكن كيف نفعل ذلك ؟

هنا الجزء الأول من الحل هو ما فعلناه في الـ Query السابقة باستخدام GROUP BY و AVG() لحساب متوسط الدرجات لكل مستوى تعليمي
الناتج من هذه الـ Query كان جدول يحتوي على عمودين level و average_grade كما رأينا
الآن نحتاج لإجراء Query أخرى على الجدول الناتج من الـ Query ونستخدم دالة MAX() لإيجاد أعلى متوسط درجات

لذا كل ما سنقوم به هو أننا سنضع الـ Query السابقة كـ Subquery في الـ FROM بهذا الشكل:

SELECT MAX(average_grade) AS highest_average
FROM (
    SELECT level, AVG(grade) AS average_grade
    FROM Students
    GROUP BY level
) AS avg_grades;

لاحظ أننا وضعنا الـ Query التي تحسب متوسط الدرجات لكل مستوى تعليمي داخل أقواس في الـ FROM
هكذا الـ FROM ستتعامل مع الناتج من هذه الـ Subquery كأنه جدول مستقل كأي جدول آخر
وفي هذا الجدول المؤقت، لدينا عمودين level و average_grade
لذا سنستخدم دالة MAX() على عمود average_grade لإيجاد أعلى متوسط

النتيجة ستكون:

+-----------------+
| highest_average |
+-----------------+
| 91.125          |
+-----------------+

هكذا استخدمنا الـ Subquery في الـ FROM لإنشاء جدول مؤقت نستخدمه في الـ Query الرئيسية

ملحوظة: عند استخدام الـ Subquery في الـ FROM يجب دائمًا إعطاؤها اسم مستعار للجدول الخاص بالـ Subquery
لاحظ أننا استخدمنا AS avg_grades في المثال السابق في نهاية الـ Subquery
في حالة عدم إعطاء اسم مستعار، ستواجه خطأ في الـ SQL يقول أنه يجب إعطاء اسم للجدول

SQL Error: Every derived table must have its own alias

ماذا لو أردنا اسم المستوى التعليمي الذي لديه أعلى متوسط درجات ؟

نحن نعرف كيف نحسب متوسط الدرجات لكل مستوى تعليمي عن طرق GROUP BY و AVG() كما فعلنا سابقًا

SELECT level, AVG(grade) AS average_grade
FROM Students
GROUP BY level;

نحتاج فقط لاستخدام الـ HAVING لتصفية النتائج بناءً على أعلى متوسط درجات

SELECT level, AVG(grade) AS average_grade
FROM Students
GROUP BY level;
HAVING average_grade = (أعلى متوسط درجات);

نحتاج فقط لاستبدال الجزء (أعلى متوسط درجات) بالـ Query التي تحسب أعلى متوسط درجات
ونحن قمنا بالفعل بعمل Query لحساب أعلى متوسط درجات باستخدام MAX()

SELECT MAX(average_grade) AS highest_average
FROM (
    SELECT level, AVG(grade) AS average_grade
    FROM Students
    GROUP BY level
) AS avg_grades;

وبما أن الـ Query التي تجلب لنا قيمة أعلى متوسط درجات ترجع قيمة واحدة فقط في صف واحد
فيمكننا استخدام هذه القيمة كشرط تصفية في الـ HAVING لذا يمكننا دمجهما معًا واستخدام الـ Subquery التي تحسب أعلى متوسط درجات داخل الـ HAVING بهذا الشكل:

SELECT level, AVG(grade) AS average_grade
FROM Students
GROUP BY level
HAVING average_grade = (
    SELECT MAX(inner_average_grade)
    FROM (
        SELECT level, AVG(grade) AS inner_average_grade
        FROM Students
        GROUP BY level
    ) AS avg_grades
);

هنا نحن فقط دمجنا الـ Subquery التي تحسب أعلى متوسط درجات داخل الـ HAVING في الـ Query التي تحسب متوسط الدرجات لكل مستوى تعليمي
وهكذا نحصل على المستوى التعليمي الذي لديه أعلى متوسط درجات

لاحظ أننا نستخدم HAVING بدلاً من WHERE لأننا نريد التصفية بناءً على نتيجة دالة AVG() التي تم حسابها بعد عملية الـ GROUP BY
ونحن بطبيعة الحال لا يمكننا استخدام WHERE مع دوال الـ Aggregation مثل AVG() و MAX() وغيرها

لاحظ أننا استخدمنا Subquery مع الـ HAVING لكي نتحقق من أن average_grade يساوي أعلى متوسط درجات
وبما أن الـ Subquery التي استخدمناها مع HAVING ترجع قيمة واحدة فقط في صف واحد، فلا مشكلة في استخدام = للمقارنة

لاحظ أيضًا أنني تخلصت من AS highest_average في الـ Subquery لأننا لا نحتاج إلى اسم العمود هنا
وغيرت اسم العمود average_grade في الـ Subquery إلى inner_average_grade لكي لا يحدث تعارض في الأسماء بين العمود في الـ Query الخارجية والعمود في الـ Subquery

النتيجة ستكون:

+----------+---------------+
| level    | average_grade |
+----------+---------------+
| Advanced | 91.125        |
+----------+---------------+

لاحظ أننا في المثال السابق استخدمنا Subquery داخل Subquery أخرى داخل الـ HAVING
وهذا يعني أننا يمكننا وضع Subquery داخل Subquery داخل Subquery أخرى وهكذا سواء في الـ WHERE أو الـ FROM أو الـ SELECT أو الـ HAVING

استخدام Subquery مع الـ SELECT

يمكننا أيضًا استخدام الـ Subquery مع الـ SELECT في حالة أننا نريد إضافة عمود جديد تكون قيمة محسوبة من Query
على سبيل المثال، لنفترض أننا نريد جلب أسماء المحاضرين مع عدد الدورات التي يشرفون عليها

لنحلل السؤال أولاً نريد أسماء المحاضرين من جدول الـ Instructors

SELECT name
FROM Instructors;

الآن نريد مع كل محاضر، نريد جلب عدد الدورات التي يشرف عليها
بمعنى أننا نريد إضافة عمود جديد بجوار الـ name يحتوي على عدد الدورات التي يشرف عليها كل محاضر
ستلاحظ أن لحساب عدد الدورات التي يشرف عليها محاضر معين، نحتاج إلى Query أخرى على جدول الـ Courses حيث نحسب عدد الصفوف التي يكون فيها instructor_id يساوي id للمحاضر الحالي
بالتالي هذا العمود الجديد الذي نريد إضافته يعتمد على نتيجة Query أخرى
هنا سندرك أننا نحتاج لعمل Subquery داخل الـ SELECT لحساب عدد الدورات لكل محاضر

SELECT
    name,
    (
        SELECT COUNT(*)
        FROM Courses AS c
        WHERE c.instructor_id = i.id
    ) AS courses_count
FROM Instructors AS i;

لاحظ أن العمود الذي بجوار name هو Subquery
هذه الـ Subquery يتم تنفيذها لكل صف في جدول الـ Instructors
بمعنى لكل محاضر سيتم تنفيذ هذه الـ Subquery لحساب عدد الدورات التي يشرف عليها هذا المحاضر لاحظ أننا في هذه الـ Subquery نقوم بحساب عدد الصفوف في جدول الـ Courses حيث يكون instructor_id يساوي id للمحاضر الحالي من جدول الـ Instructors
لاحظ أننا استخدمنا اسم مستعار i لجدول الـ Instructors و c لجدول الـ Courses
ولاحظ أننا أعطينا اسمًا مستعارًا للعمود الجديد الذي يحسب عدد الدورات وهو courses_count

النتيجة:

+-------------+---------------+
| name        | courses_count |
+-------------+---------------+
| Dr. Ahmed   | 2             |
| Dr. Kamal   | 1             |
| Dr. Mohamed | 2             |
| Dr. Khaled  | 1             |
| Dr. Ali     | 0             |
+-------------+---------------+

ملخص

في هذه المقالة تعرفنا على الـ Subquery في الـ SQL وهي وضع Query داخل Query أخرى
تعلمنا كيفية استخدامها في أماكن متعددة:

  • مع الـ WHERE: لاستخدام نتيجة الـ Subquery كشرط للتصفية، سواء باستخدام عمليات المقارنة مثل = و > و <
    أو باستخدام IN و NOT IN أو مع EXISTS و NOT EXISTS للتحقق من وجود صفوف في نتيجة الـ Subquery بغض النظر عن القيم أو مع ANY و ALL لعمل مقارنات مع مجموعة قيم
  • مع الـ FROM: عن طريق إنشاء جدول مؤقت من نتيجة الـ Subquery ونستخدم مع الـ FROM كأي جدول آخر
  • مع الـ SELECT: لإضافة عمود جديد قيمته محسوبة من Query أخرى

الـ Subquery تساعدنا في حل مشاكل معقدة تحتاج إلى أكثر من خطوة للوصول إلى النتيجة النهائية
لاحظ كيف أننا كنا نجزء المشكلة إلى أجزاء أصغر ونحل كل جزء باستخدام Query منفصلة
ثم ندمجهم في Query واحدة باستخدام الـ Subquery سواء في الـ WHERE أو الـ FROM أو الـ SELECT بحسب الحاجة

تذكر دائمًا أن:

  • الـ Subquery مع عمليات المقارنة مثل = و > يجب أن ترجع قيمة واحدة فقط
  • الـ Subquery مع IN و NOT IN و ANY و ALL يجب أن ترجع عمود واحد فقط
  • الـ Subquery مع EXISTS لا تهتم بالقيم الراجعة بل فقط تهتم بوجود صفوف أم لا لذا يمكننا استخدام أي شيء في الـ SELECT مثل SELECT 1 أو حتى SELECT 'I LOVE THE BATATA'
  • الـ Subquery في الـ FROM يجب إعطاؤها اسم مستعار دائمًا

أتمنى أن تكون قد استفدت من هذه المقالة وفهمت كيفية استخدام الـ Subquery في الـ SQL
في المقالة القادمة سنشرح عن الـ CTE أو الـ Common Table Expressions والتي تعتبر بديلاً متقدمًا للـ Subquery في بعض الحالات