Self-JOIN: tabela z samą sobą

Czasami związek, który chcesz przejść, nie jest pomiędzy dwojgiem ludzi różnych tabel, ale pomiędzy wierszami tej samej tabeli. Typowy przykład: Hierarchia „kategoria → podkategoria”, w której zarówno rodzic, jak i dziecko są wierszami ta sama tabela categories, połączona przez parent_id.

Aby przeczytać razem jeden wiersz (dziecko) i „jego” drugi wiersz (rodzic), użyj a samołączenie: ta sama tabela pojawia się dwukrotnie w FROM, z dwoma różne pseudonimy.

Składnia

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

Alias jest obowiązkowy: jeśli pominiemy AS a / AS b, PostgreSQL nie będzie miał sposób wiedzieć, którą z dwóch kopii mamy na myśli, pisząc name lub 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;

Przeczytaj prostym angielskim: ”weź wiersze categories o nazwie dziecko i dla każdego znajdź wiersz categories o nazwie parent, którego id to KODPH3 dziecka”.

Wynik wyklucza kategorie główne (te z parent_id = NULL) ponieważ INNER JOIN pomija wiersze bez dopasowania. Jeśli chcesz uwzględnić nich, użyj LEFT JOIN.

Samodołączenie za pomocą LEWEGO, aby „zachować korzenie”

-- 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;

Teraz otrzymujesz 9 wierszy (każda kategoria w zestawie danych) z parent = NULL dla 3 korzeni.

Agregowanie przy użyciu samodzielnego łączenia

Bardzo dobrze łączy się z GROUP BY, aby zapytać „ile dzieci robi każde z nich rodzic ma":

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;

Zwróć uwagę na dwa ważne szczegóły:

1. Filtry WHERE p.parent_id IS NULL przed DOŁĄCZENIEM: bierzemy tylko korzenie jako „stół po lewej stronie”.
2. COUNT(c.id) (nie COUNT(*)) zwraca 0 dla pierwiastków bez dzieci, podczas gdy COUNT(*) zwróci 1 z powodu wiersza LEFT JOIN NULL.

Spróbuj sam

Przejrzyj ćwiczenie