شرح الـ Common Table Expressions في الـ SQL

المقدمة

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

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

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

SELECT name, grade
FROM Students
WHERE level = (
    SELECT level
    FROM Students
    GROUP BY level
    HAVING AVG(grade) = (
        SELECT MAX(avg_grade)
        FROM (
            SELECT AVG(grade) AS avg_grade
            FROM Students
            GROUP BY level
        ) AS level_averages
    )
);

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

وبسبب وجود تداخل كبير في الـ Subquery مع بعضها البعض تشبه مشكلة الـ Callback Hell
بحيث أن كل Subquery تعتمد على نتيجة الـ Subquery التي بداخلها وهكذا
وهناك مشكلة أخرى وهي أنك قد تجد تكرار لنفس الـ Query في أماكن مختلفة من نفس الـ Query

كل هذه الأمور تجعل تعديل الـ Query وفهمها أمرًا صعبًا للغاية مع مرور الوقت

هنا يأتي دور الـ CTE أو الـ Common Table Expressions
والذي يقدم لنا طريقة أفضل لتنظيم الـ Query المعقدة التي تحتاج إلى أكثر من خطوة
وتجعل الـ Query أسهل في القراءة والفهم والتعديل

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

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

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

لدينا ثلاث جداول وهم الـ: 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             |
+----+-------------------------+---------------+----------------+

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

ما هو الـ CTE ؟

الـ CTE اختصار لـ Common Table Expression
وهو جدول مؤقت له اسم يمكنك تعريفه في بداية الـ Query باستخدام كلمة WITH
ثم تستخدم هذا الجدول المؤقت في الـ Query الرئيسية كأنه جدول مثل أي جدول بشكل اعتيادي

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

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

لنرى مثال بسيط جدًا لفهم الفكرة

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

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

لاحظ أننا استخدمنا Subquery في الـ WHERE لإيجاد أعلى درجة
الآن لنكتب نفس الـ Query باستخدام CTE:

WITH MaxGrade AS (
    SELECT MAX(grade) AS highest_grade
    FROM Students
)
SELECT name, grade
FROM Students, MaxGrade
WHERE grade = highest_grade;

لاحظ أننا عرفنا CTE عن طريق الـ WITH باسم MaxGrade في البداية وهو اسم للجدول المؤقت الذي يحتوي على نتيجة الـ Subquery هذا الـ CTE يمثل Query يحسب أعلى درجة من جدول الـ Students
الآن تخيل معي أن الـ MaxGrade هو جدول عادي يحتوى على عمود واحد highest_grade وقيمة واحدة وهي أعلى درجة

الآن في الـ Query الرئيسية استخدمنا هذا الجدول المؤقت MaxGrade كأنه جدول عادي في الـ FROM
ثم في الـ WHERE قارننا بين عمود grade في جدول الـ Students وعمود highest_grade في جدول الـ MaxGrade

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

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

قد تلاحظ أن المثال الخاص بالـ CTE أطول قليلاً من الـ Subquery
وأن الـ Subquery أسهل في هذا المثال البسيط

لكن الفائدة الحقيقية من الـ CTE تظهر عندما تكون هناك أكثر من Query متداخلة مع بعضها البعض ومكررة
وهذا ما سنراه في الأمثلة القادمة

وأيضًا ستلاحظ أننا لم نستخدم الجدول المؤقت MaxGrade مع الـ WHERE مباشرة كما فعلنا في الـ Subquery بهذا الشكل:

WITH MaxGrade AS (
    SELECT MAX(grade)
    FROM Students
)
SELECT name, grade
FROM Students
WHERE grade = MaxGrade;  -- خطأ

هكذا سنحصل على خطأ لأننا لم نستخدم الـ CTE بشكل صحيح
لأننا لا نستطيع استخدام الـ CTE مباشرة في الـ WHERE

بل نستخدمه كما استخدمناه سابقًا عن طريقه وضعه في الـ FROM لنستخدم العمود الناتج منه highest_grade في الـ WHERE
وهذا يعد من عيوب الـ CTE مقارنة بالـ Subquery التي يمكن استخدام الجدول الناتج منها مباشرة في أي مكان في الـ Query الرئيسية

وإن أردنا استخدام الـ CTE في الـ WHERE مباشرة دون الحاجة لوضعه في الـ FROM، فعلينا استخدامها في Subquery داخل الـ WHERE كما يلي:

WITH MaxGrade AS (
    SELECT MAX(grade) AS highest_grade
    FROM Students
)
SELECT name, grade
FROM Students
WHERE grade = (SELECT highest_grade FROM MaxGrade);

هنا استخدمنا الـ CTE لتعريف جدول مؤقت، ثم استخدمناه في Subquery داخل الـ WHERE لإحضار العمود الناتج وهو highest_grade
وطالما أن الجدول MaxGrade على صف واحدة فقط، فالـ Subquery سترجع قيمة واحدة فقط وهي أعلى درجة والتي يمكننا استخدامعمليات المقارنة مثل = و > و < معها هذا يوضح فرق أساسي بين الـ CTE والـ Subquery
يوضح أننا لا يمكننا استخدام الـ CTE في الـ WHERE أو الـ HAVING ولا نستطيع استخدام أوامر مثل IN أو EXISTS مع الـ CTE مباشرة كما نفعل مع الـ Subquery
هنا ندرك أن الـ CTE لم تأتي لتستبدل الـ Subquery بل لتكملها في الحالات التي تحتاج إلى تنظيم أفضل للـ Query المعقدة والمتداخلة والمكررة
لكن في معظم الحالات ستجد أن الـ CTE تتفوق على الـ Subquery في جعل الـ Query أسهل في القراءة والفهم
وهناك بضع الحالات نستخدم فيها الـ Subquery فقط بدلًا من الـ CTE كما سنرى لاحقًا

الفرق بين الـ CTE والـ Subquery

الآن دعنا نأخذ بعض الأمثلة ونقارن بين الـ Subquery وكتابتها بالـ CTE
المقارنات ستعطي فهمًا عمليًا لكيفية استخدام كل منهما

الدورات التي مدتها أكبر من المتوسط

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

باستخدام الـ Subquery:

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

باستخدام الـ CTE:

WITH AvgDuration AS (
    SELECT AVG(duration_hours) AS avg_hours
    FROM Courses
)
SELECT name, duration_hours
FROM Courses, AvgDuration
WHERE duration_hours > avg_hours;

هنا نفس الشيء لا تستطكن استخدام الـ CTE مباشرة في الـ WHERE
لكننا استخدمناها في الـ FROM لجلب العمود الناتج منها avg_hours ونستخدمه في الـ WHERE

برغم أن الـ Subquery هنا أبسط قليلاً من الـ CTE في الأمثلة الصغيرة والبسيطة
لكن كونك تعرف الـ CTE في البداية يجعل الـ Query أكثر وضوحًا عند القراءة
ستعرف فورًا من مجرد النظر إلى بداية الـ Query أننا عرفنا جدول يدعى AvgDuration يحسب متوسط المدة
وهذا يسهل عليك فهم ما يحدث في الـ Query الرئيسية

سيظهر تفوق الـ CTE على الـ Subquery بشكل أكبر عندما تكون هناك أكثر من خطوة أو تداخل في الـ Query

أعلى متوسط درجات بين المستويات

كنا نريد أعلى متوسط درجات بين كل المستويات التعليمية
فهنا كنا بحاجة لكتابة Query داخل ال FROM لحساب متوسط الدرجات لكل مستوى
ثم نستخدم هذا الناتج لإيجاد أعلى متوسط

باستخدام الـ 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;

باستخدام الـ CTE:

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

هنا يظهر تفوق الـ CTE بشكل واضح عن الـ Subquery
بحيث أننا عرفنا جدول يدعى AverageGradesByLevel في البداية يحسب متوسط الدرجات لكل مستوى
ثم في الـ Query الرئيسية تعاملنا مع هذا الجدول المؤقت بشكل مباشر في الـ FROM كأنه جدول عادي
وقلنا فقط نريد أعلى متوسط من هذا الجدول المؤقت باستدام دالة MAX

لاحظ كيف أن الـ Query الخاصة بالـ CTE أسهل في القراءة والفهم من الـ Query الخاصة بالـ Subquery
تخيل لو أزلنا الجزء الخاص بالـ CTE من الـ Query وقرأنا الـ Query الرئيسية فقط

SELECT MAX(average_grade) AS highest_average
FROM AverageGradesByLevel;

بمجرد النظر إلى هذه الـ Query وأنت لا ترى تعريف الـ CTE فأنت ستفهم بوضوح ما التي تفعله هذه الـ Query
وبمجرد ما تقرأ اسم الجدول AverageGradesByLevel ستعرف أن هذا الجدول يحتوي على متوسط الدرجات لكل مستوى
دون حتى أن تنظر إلى تعريف الـ CTE في الأعلى
هذا يجعل الـ Query أسهل في القراءة والفهم بشكل كبير

استخدام أكثر من CTE في نفس الـ Query

دعنا مثالًا أكثر تعقيدًا لنرى سهولة التفكير والتنظيم التي يقدمها الـ CTE
لنفترض أننا نريد معرفة أسماء الطلاب في المستوى الذي لديه أعلى متوسط درجات
هنا كما اعتدنا سنحتاج لتقسيم المشكلة إلى عدة خطوات:

1. حساب متوسط الدرجات لكل مستوى تعليمي
2. إيجاد المستوى الذي لديه أعلى متوسط
3. جلب أسماء الطلاب في هذا المستوى

كل خطوة يمكننا تنفيذها باستخدام CTE منفصلة
أولًا نحسب متوسط الدرجات لكل مستوى:

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

هكذا انشأنا أول CTE باسم AverageGradesByLevel ليحسب متوسط الدرجات لكل مستوى
تذكر أن الـ AverageGradesByLevel هو جدول مؤقت يحتوي على عمودين: level و average_grade
الآن نحتاج لإيجاد المستوى الذي لديه أعلى متوسط

WITH AverageGradesByLevel AS (
    SELECT level, AVG(grade) AS average_grade
    FROM Students
    GROUP BY level
),
HighestAverageLevel AS (
    SELECT level
    FROM AverageGradesByLevel
    WHERE average_grade = (SELECT MAX(average_grade) FROM AverageGradesByLevel)
)

هنا ركز معي لأن هناك العديم من النقاط المهمة في هذا الجزء
أولًا الجدول الأول AverageGradesByLevel ما زال كما هو لم يتغير
ثم لاحظ أننا عرفنا CTE ثاني باسم HighestAverageLevel في نفس الـ WITH
بحيث أننا يمكننا عن طريق الـ WITH تعريف أكثر من CTE
فقط نفصل بين كل CTE بفاصلة , ونكتبهم تحت نفس كلمة WITH في البداية

WITH table1 AS (...),
     table2 AS (...),
     table3 AS (...)

الآن نكون عرفنا CTE ثاني باسم HighestAverageLevel داخل نفس الـ WITH
يقوم فقط بإيجاد المستوى الذي لديه أعلى متوسط
وهو مجرد جدول مؤقت يحتوي على عمود واحد وهو level الذي لديه أعلى متوسط
لاحظ أننا استخدمنا الـ CTE الأول AverageGradesByLevel داخل الـ CTE الثاني HighestAverageLevel
في أمر الـ WHERE بحيث أننا نقوم بمقارنة average_grade مع أعلى متوسط من الجدول الأول AverageGradesByLevel باستخدام دالة الـ MAX داخل Subquery
وهذا من مميزات الـ CTE حيث يمكن لكل CTE استخدام الـ CTE التي قبلها
وركز في جملة التي قبلها لأن الـ CTE لا يمكنها استخدام الـ CTE التي تليها
بمعني لو عرفنا CTE باسم A ثم CTE باسم B ثم CTE باسم C
فإن A لا يمكنها استخدام B أو C، و B يمكنها استخدام A فقط، و C يمكنها استخدام A و B

على أي حال الآن نأتي للخطوة الأخيرة وهي جلب أسماء الطلاب في المستوى الذي لديه أعلى متوسط

WITH AverageGradesByLevel AS (
    SELECT level, AVG(grade) AS average_grade
    FROM Students
    GROUP BY level
),
HighestAverageLevel AS (
    SELECT level
    FROM AverageGradesByLevel
    WHERE average_grade = (SELECT MAX(average_grade) FROM AverageGradesByLevel)
)
SELECT s.name, s.grade, s.level
FROM Students AS s
INNER JOIN HighestAverageLevel AS h ON s.level = h.level;

أريدك الآن أن تركز في الـ Query الرئيسية ولا تنظر لتعريفات الـ CTE التي في الأعلى

SELECT s.name, s.grade, s.level
FROM Students AS s
INNER JOIN HighestAverageLevel AS h ON s.level = h.level;

بمجردالنظر وبدون أن تنظر لتعريفات الـ CTE التي في الأعلى
هل تستطيع أن تفهم ما الذي تفعله هذه الـ Query ؟
هنا ستلاحظ أننا نستخدم SELECT لإحضار الأعمدة name و grade و level من جدول الـ Students
ثم قمنا بعمل FROM على جدول الـ Students وأعطيناه الاسم المستعار s
ثم قمنا بعمل INNER JOIN مع جدول يدعى HighestAverageLevel وأعطيناه الاسم المستعار h
جدول الـ HighestAverageLevel من خلال اسمه فقط نستطيع أن نخمن بأنه يحتوي على المستوى الذي لديه أعلى متوسط للدرجات
ثم نقوم بعملية الربط بين الجدولين على أساس أن s.level = h.level
إذا فنحن سنحضر أسماء الطلاب الذين مستواهم يساوي المستوى الذي لديه أعلى متوسط درجات

+----------------+-------+----------+
| name           | grade | level    |
+----------------+-------+----------+
| Mohamed Adel   | 92.0  | Advanced |
| Kamal Mahmoud  | 90.0  | Advanced |
| Ayman Hassan   | 87.5  | Advanced |
| Ismail Khaled  | 95.0  | Advanced |
+----------------+-------+----------+

على أي حال أريدأن ابهرك قليلًا هنا
هل ترى الـ Query التي قمنا بها للتو مع الـ CTE ؟

WITH AverageGradesByLevel AS (
    SELECT level, AVG(grade) AS average_grade
    FROM Students
    GROUP BY level
),
HighestAverageLevel AS (
    SELECT level
    FROM AverageGradesByLevel
    WHERE average_grade = (SELECT MAX(average_grade) FROM AverageGradesByLevel)
)
SELECT s.name, s.grade, s.level
FROM Students AS s
INNER JOIN HighestAverageLevel AS h ON s.level = h.level;

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

WITH HighestAverageLevel AS (
    SELECT level
    FROM Students
    GROUP BY level
    ORDER BY AVG(grade) DESC
    LIMIT 1
)
SELECT s.name, s.grade, s.level
FROM Students AS s
INNER JOIN HighestAverageLevel AS h ON s.level = h.level;

لاحظ أننا الآن أصبح لدينا CTE واحد فقط وهو HighestAverageLevel
وهذا بسبب تعديلات في طريقة التفكير
هنا استخدمنا GROUP BY على جدول الـ Students لنقسم الطلاب حسب المستويات
ثم استخدمنا ORDER BY AVG(grade) DESC لترتيب المستويات حسب متوسط الدرجات من الأعلى إلى الأدنى
ثم استخدمنا LIMIT 1 لأخذ أول مستوى فقط من الترتيب وهو المستوى الذي لديه أعلى متوسط درجات

لا أخفي عنكم أنني أثناء كتابتي للمقالة كتبت الـ Query الأولى التي تحتوي على الـ AverageGradesByLevel و HighestAverageLevel
ولم أعرف أنني أستطيع تبسيطها إلى CTE واحد فقط إلا بعد الاستعانة بصديقي العزيز Claude الذي ساعدني في تحسين الـ Query
مهما بلغ مستوى خبرتك في الـ SQL ستجد دائمًا طرقًا جديدة لتحسين وتبسيط الـ Query الخاصة بك

إذا قمت بإعطاء Claude جميع الأمثلة التي كتبناها في هذه المقالة والمقالات السابقة
وطلبت منه أن يحسنها ويجعلها أكثر كفاءة وبساطة
ستجده يستطيع فعل ذلك بسهولة ويقدم لك Query أفضل بكثير من التي كتبتها في معظم الأحيان

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

أظن أننا فهمنا الآن الفرق بين الـ CTE والـ Subquery
الآن دعنا ننتقل للسؤال المهم التالي وهو هل نستخدم الـ CTE بدلاً من الـ Subquery في كل الحالات أم لا ؟

متى تستخدم الـ CTE ومتى تستخدم الـ Subquery ؟

كما قلنا بأن الـ CTE ليست بديلاً كاملاً للـ Subquery
برغم من أن الـ CTE تتفوق على الـ Subquery في معظم الحالات
بحيث يقدم وسيلة أفضل لتنظيم الـ Query المعقدة والمتداخلة والمكررة
لكن للأسف فهناك بعض الحدود التي لا يستطيع الـ CTE التعامل معها

لذا دعونا نسرد بعض الفروق الجوهرية بين الـ CTE والـ Subquery

الـ Query البسيطة والصغيرة

ولعل أول ملاحظة هى أن في الـ Query البسيطة والصغيرة التي لا تحتاج إلى أكثر من خطوة واحدة فهنا الـ Subquery تكون أسهل وأبسط من الـ CTE
أظننا لاحظها هذا في الأمثلة السابقة

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

-- CTE
WITH MaxGrade AS (
    SELECT MAX(grade) AS highest_grade
    FROM Students
)
SELECT name, grade
FROM Students, MaxGrade
WHERE grade = highest_grade;

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

تكرار نفس الـ Query

لعل أكثر سبب وأهم سبب يجعلنا نستخدم الـ CTE هو إعادة استخدام نفس النتيجة أكثر من مرة بدون الحاجة لتكرار نفس الـ Subquery في أكثر من مكان في نفس الـ Query

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

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

SELECT LEVEL, total_grade
FROM (
    SELECT LEVEL, SUM(grade) AS total_grade
    FROM Students
    GROUP BY level
) AS LevelTotalGrade
WHERE total_grade > (
    SELECT AVG(total_grade) AS average_total_grade
    FROM (
        SELECT LEVEL, SUM(grade) AS total_grade
        FROM Students
        GROUP BY level
    ) AS LevelTotalGrade
);

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

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

WITH LevelTotalGrade AS (
    SELECT LEVEL, SUM(grade) AS total_grade
    FROM Students
    GROUP BY level
)
SELECT LEVEL, total_grade
FROM LevelTotalGrade
WHERE total_grade > (
    SELECT AVG(total_grade) AS average_total_grade
    FROM LevelTotalGrade
);

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

على أي حال ناتج الـ Query سيكون كالتالي:

+--------------+-------------+
| LEVEL        | total_grade |
+--------------+-------------+
| Advanced     | 364.5       |
+--------------+-------------+

الـ CTE يستخدم مع الـ FROM

من حدود الـ CTE تستخدم بشكل مباشر مع الـ FROM فقط ولا يمكن استخدامها بشكل مباشر في الـ WHERE أو الـ HAVING أو مع أوامر مثل IN أو EXISTS كما نفعل مع الـ Subquery وإذا أردنا استخدام الـ CTE في هذه الأماكن فعلينا وضعها داخل Subquery أولًا كما رأينا سابقًا

بحيث أننا لو عرفنا CTE باسم MaxGrade كما في المثال التالي:

WITH MaxGrade AS (
    SELECT MAX(grade) AS highest_grade
    FROM Students
)

فلا يمكننا استخدامه مباشرة في الـ WHERE بهذا الشكل:

SELECT name, grade
FROM Students
WHERE grade = MaxGrade;  -- خطأ

بل يجب علينا في هذه الحالة استخدامه في Subquery داخل الـ WHERE كما يلي:

SELECT name, grade
FROM Students
WHERE grade = (SELECT highest_grade FROM MaxGrade); -- inside Subquery

أو يمكنك استخدامه في الـ FROM كما فعلنا سابقًا:

SELECT name, grade
FROM Students, MaxGrade
WHERE grade = highest_grade;

نفس الشيء ينطبق على الـ HAVING وأوامر مثل IN و EXISTS و ANY و ALL

أوامر الـ CTE المدعومة

في حالة أنك تستخدم الـ CTE في الـ MySql فهناك بعض القيود التي يجب أن تعرفها
هي أننا لا يمكننا استخدام أوامر مثل INSERT أو UPDATE أو DELETE داخل الـ CTE
بل يمكننا فقط استخدام الـ SELECT داخل الـ CTE
هذا ينطبق على الـ MySql فقط لأن بعض قواعد البيانات الأخرى مثل PostgreSQL و SQL Server تدعم استخدام أوامر أخرى داخل الـ CTE مثل INSERT و UPDATE و DELETE مع بعض الاختلاف في الصياغة

الـ Recursive CTE

من أقوى مميزات الـ CTE هي القدرة على كتابة Recursive CTE
وهو نوع خاص من الـ CTE يستدعي نفسه بشكل متكرر حتى يصل إلى نتيجة معينة
وهذا شيء لا يمكن فعله باستخدام الـ Subquery العادية

الـ Recursive CTE مفيد جدًا عندما تتعامل مع بيانات هرمية أو متسلسلة
أمر يشبه دوال الـ Recursion في لغات البرمجة
مثلًا إذا كان لدينا جدول يحتوي على بيانات الموظفين ومديريهم

+----+-----------------+------------+
| id | name            | manager_id |
+----+-----------------+------------+
| 1  | Ahmed Moustafa  | NULL       |
| 2  | Osama Ali       | 1          |
| 3  | Mohamed Adel    | 2          |
| 4  | Kamal Mahmoud   | 2          |
| 5  | Ayman Hassan    | 3          |
| 6  | Adam Ibrahim    | 4          |
| 7  | Ismail Khaled   | 5          |
+----+-----------------+------------+

ونريد إيجاد جميع الموظفين الذي يقعون تحت مدير معين في هذا الهيكل التنظيمي
بمعنى لو قلنا نريد جميع الموظفين الذين يعملون تحت المدير Osama Ali
فيجب هنا أن نجد أولًا الموظفين الذين يديرهم Osama Ali بشكل مباشر وهم Mohamed Adel و Kamal Mahmoud
ثم نبحث عن الموظفين الذين يديرهم Mohamed Adel وهو الموظف Ayman Hassan فقط
ونبحث عن الموظفين الذين يديرهم Kamal Mahmoud وهو الموظف Adam Ibrahim فقط
ثم نبحث عن الموظفين الذين يديرهم Ayman Hassan وهو الموظف Ismail Khaled فقط
وهكذا نستمر في البحث حتى نصل إلى نهاية الهيكل التنظيمي

لنجد في النهاية جميع الموظفين الذين يعملون تحت Osama Ali وهم Mohamed Adel و Kamal Mahmoud و Ayman Hassan و Adam Ibrahim و Ismail Khaled
يمكننا حل هذه المشكلة فقط باستخدام الـ Recursive CTE

لكن هذا الموضوع سنشرحه في مقالة منفصلة، لكي نتحدث عنه بالتفصيل

ملخص الفروقات بين الـ CTE والـ Subquery

بعد كل ما تعلمناه، دعنا نلخص الفروقات الجوهرية بين الـ CTE والـ Subquery في جدول واضح:

الخلاصة

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

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

أهم شيء هو أن تجعل الـ Query الخاصة بك واضحة وسهلة الفهم
لأنك أو أي شخص آخر سيحتاج لقراءة هذه الـ Query وفهمها لاحقًا

أتمنى أن تكون قد استفدت من هذه المقالة وفهمت الفرق بين الـ CTE والـ Subquery