Self-JOIN: שולחן עם עצמו

לפעמים מערכת היחסים שאתה רוצה לעבור היא לא בין שניים טבלאות שונות, אבל בין שורות של אותה טבלה. דוגמה אופיינית: א היררכיית "קטגוריה → תת-קטגוריה", שבה גם ההורה וגם הילד הם שורות של אותה טבלה 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 בשם ילד, ו עבור כל אחד מהם מצא את השורה של categories בשם הורה שה-id שלו הוא parent_id של הילד".

התוצאה לא כוללת את קטגוריות השורש (אלה עם 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 עבור 3 השורשים.

מצטבר עם הצטרפות עצמית

זה משתלב היטב עם 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 עם NULL.

נסה זאת בעצמך

סקירת התרגיל