Self-JOIN: een tafel met zichzelf

Soms is de relatie die je wilt doorkruisen niet tussen twee verschillende tabellen, maar tussen rijen van dezelfde tabel. Typisch voorbeeld: "categorie → subcategorie"-hiërarchie, waarbij zowel ouder als kind rijen zijn dezelfde categories tabel, gekoppeld door een parent_id.

Om de ene rij (kind) en "zijn" andere rij (ouder) samen te lezen, gebruik je a self-join: dezelfde tabel verschijnt twee keer in de FROM, met twee verschillende aliassen.

De syntaxis

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

De alias is verplicht: als we AS a / AS b weglaten, heeft PostgreSQL geen manier om te weten welke van de twee kopieën we bedoelen als we name of id schrijven.

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

Lees in gewoon Engels: "neem de rijen van categories met de naam kind, en zoek voor elke rij de rij categories met de naam ouder waarvan id de parent_id van het kind".

Het resultaat sluit de hoofdcategorieën uit (die met parent_id = NULL) omdat een INNER JOIN rijen overslaat zonder overeenkomst. Als je wilt opnemen gebruik LEFT JOIN.

Sluit u aan bij LEFT om "ook de wortels te behouden"

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

Nu krijg je 9 rijen (elke categorie in de dataset), met parent = NULL voor de 3 wortels.

Samenvoegen met een self-join

Het combineert heel goed met GROUP BY om te vragen "hoeveel kinderen elk doen ouder heeft":

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;

Let op twee belangrijke details:

1. WHERE p.parent_id IS NULL filters vóór de JOIN: we nemen alleen de wortels als de "linkertafel".
2. COUNT(c.id) (niet COUNT(*)) retourneert 0 voor wortels zonder kinderen, terwijl COUNT(*) 1 zou retourneren vanwege de rij LEFT JOIN met NUL.

Probeer het zelf

Oefening beoordelen