Self-JOIN: una tabella con se stessa

A volte la relazione che vuoi attraversare non è fra due tabelle diverse, ma fra righe della stessa tabella. Tipico esempio: una gerarchia "categoria → sotto-categoria", dove sia il padre sia il figlio sono righe della stessa tabella categories, legate da un parent_id.

Per leggere insieme una riga (figlio) e la "sua" altra riga (padre) si usa un self-JOIN: la stessa tabella appare due volte nella FROM, con due alias diversi.

La sintassi

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

L'alias è obbligatorio: se omettiamo AS a / AS b, PostgreSQL non sa quale delle due copie intendiamo quando scriviamo name o 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;

Letta in italiano: "prendi le righe di categories chiamate child, e per ognuna trova la riga di categories chiamata parent la cui id è il parent_id del child".

Il risultato esclude le categorie-radice (quelle con parent_id = NULL) perché un INNER JOIN salta le righe senza match. Se le vuoi includere, usa LEFT JOIN.

Self-JOIN con LEFT per "tenere anche le radici"

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

Ora ottieni 9 righe (tutte le categorie del dataset), con parent = NULL per le 3 radici.

Aggregare con self-JOIN

Si combina benissimo con GROUP BY per "quante figlie ha ogni padre":

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;

Nota due dettagli importanti:

1. WHERE p.parent_id IS NULL filtra prima del JOIN: prendiamo solo le radici come "tabella di sinistra".
2. COUNT(c.id) (non COUNT(*)) restituisce 0 per le radici senza figli, mentre COUNT(*) ne restituirebbe 1 grazie alla riga LEFT JOIN con NULL.

Prova tu

Esercizio di ripasso