الانضمام الذاتي: جدول مع نفسه

في بعض الأحيان، تكون العلاقة التي تريد اجتيازها ليست بين اثنين جداول مختلفة، ولكن بين صفوف نفس الجدول. مثال نموذجي: أ التسلسل الهرمي "الفئة → الفئة الفرعية"، حيث يكون كل من الأصل والطفل في صفوف نفس الجدول categories، المرتبط بواسطة parent_id.

لقراءة صف واحد (فرعي) والصف الآخر (الأصل) معًا، يمكنك استخدام a الانضمام الذاتي: يظهر نفس الجدول مرتين في FROM، مع وجود اثنين أسماء مستعارة مختلفة.

بناء الجملة

SELECT colonne
FROM   tabella AS alias_a
JOIN   tabella AS alias_b ON alias_a.foreign_key = alias_b.id;

الاسم المستعار إلزامي: إذا حذفنا AS a / AS b، فإن PostgreSQL لا يحتوي على طريقة معرفة أي النسختين نقصد عندما نكتب name أو id.

-- Ogni sotto-categoria con il nome della categoria padre:
SELECT child.name  AS subcategory,
       parent.name AS category
FROM   categories AS child
JOIN   categories AS parent ON child.parent_id = parent.id;

اقرأ باللغة الإنجليزية البسيطة: "خذ صفوف categories المسماة child، و لكل واحد، ابحث عن الصف categories المسمى الأصل والذي id هو محمي_INLINE_10 للطفل".

تستبعد النتيجة الفئات الجذرية (تلك التي تحتوي على parent_id = NULL) لأن INNER JOIN يتخطى الصفوف التي لا يوجد بها تطابق. إذا كنت تريد أن تشمل لهم، استخدم LEFT JOIN.

الانضمام الذاتي مع LEFT "للحفاظ على الجذور أيضًا"

-- Tutte le categorie, con nome del padre o NULL se sono radice:
SELECT child.name AS category,
       parent.name AS parent
FROM   categories AS child
LEFT JOIN categories AS parent ON child.parent_id = parent.id;

الآن تحصل على 9 صفوف (كل فئة في مجموعة البيانات)، مع parent = NULL للجذور الثلاثة .

التجميع مع الانضمام الذاتي

إنه يندمج جيدًا مع GROUP BY للسؤال "كم عدد الأطفال الذين يقومون بكل منهما الوالدين لديهم":

SELECT p.name AS parent_category,
       COUNT(c.id) AS children
FROM   categories AS p
LEFT JOIN categories AS c ON c.parent_id = p.id
WHERE  p.parent_id IS NULL          -- solo le radici
GROUP BY p.name
ORDER BY p.name;

لاحظ تفصيلين مهمين:

1. مرشحات WHERE p.parent_id IS NULL قبل الانضمام: نأخذ فقط الجذور باعتبارها "الجدول الأيسر".
2. COUNT(c.id) (ليس COUNT(*)) يُرجع 0 للجذور بدون أطفال، بينما COUNT(*) سيُرجع 1 بسبب الصف LEFT JOIN الذي يحتوي على فارغة.

جربه بنفسك

تمرين المراجعة