Self-JOIN: таблица сама с собой

Иногда отношения, которые вы хотите установить, не между двумя разные таблицы, но между строками одной таблицы. Типичный пример: А. Иерархия «категория → подкатегория», где родительский и дочерний элементы являются строками та же таблица categories, связанная с помощью parent_id.

Чтобы прочитать вместе одну строку (дочернюю) и «ее» другую строку (родительскую), вы используете самосоединение: одна и та же таблица появляется дважды в 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 с именем parent, 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 с НУЛЬ.

Попробуйте сами

Обзор упражнения