Self-JOIN: Eine Tabelle mit sich selbst

Manchmal besteht die Beziehung, die Sie durchqueren möchten, nicht zwischen zwei verschiedene Tabellen, aber zwischen Zeilen derselben Tabelle. Typisches Beispiel: a „Kategorie → Unterkategorie“-Hierarchie, in der sowohl das übergeordnete als auch das untergeordnete Element Zeilen sind dieselbe categories-Tabelle, verknüpft durch einen parent_id.

Um eine Zeile (untergeordnetes Element) und „ihre“ andere Zeile (übergeordnetes Element) zusammen zu lesen, verwenden Sie a Selbstverknüpfung: Dieselbe Tabelle erscheint zweimal im FROM, mit zwei verschiedene Aliase.

Die Syntax

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

Der Alias ist obligatorisch: Wenn wir AS a / AS b weglassen, hat PostgreSQL keinen So können Sie herausfinden, welche der beiden Kopien wir meinen, wenn wir name oder id schreiben.

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

Lesen Sie im Klartext: "Nehmen Sie die Zeilen von categories mit dem Namen child, und Suchen Sie für jeden die Zeile von categories mit dem Namen parent, deren id der ist parent_id des Kindes".

Das Ergebnis schließt die Stammkategorien aus (solche mit parent_id = NULL) weil ein INNER JOIN Zeilen ohne Übereinstimmung überspringt. Wenn Sie einschließen möchten verwenden Sie LEFT JOIN.

Selbstverknüpfung mit LINKS, um „auch die Wurzeln zu behalten“

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

Jetzt erhalten Sie 9 Zeilen (jede Kategorie im Datensatz) mit parent = NULL für die 3 Wurzeln.

Aggregation mit einem Self-Join

Es lässt sich sehr gut mit GROUP BY kombinieren, um zu fragen, „wie viele Kinder das macht“. Eltern haben":

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;

Beachten Sie zwei wichtige Details:

1. WHERE p.parent_id IS NULL-Filter vor dem JOIN: Wir nehmen nur die Wurzeln als „linke Tabelle“.
2. COUNT(c.id) (nicht COUNT(*)) gibt 0 für Wurzeln ohne Kinder zurück, wohingegen COUNT(*) aufgrund der LEFT JOIN-Zeile mit 1 zurückgeben würde NULL.

Probieren Sie es selbst aus

Wiederholungsübung