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 з назвою child і для кожного знайдіть рядок 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 фільтрує до JOIN: ми беремо лише коріння як "лівостороння таблиця".
2. COUNT(c.id) (а не COUNT(*)) повертає 0 для коренів без дітей, тоді як COUNT(*) поверне 1 через рядок LEFT JOIN з NULL.

Спробуйте самі

Повторна вправа