Self-JOIN : une table avec elle-même

Parfois, la relation que vous souhaitez traverser n'est pas entre deux tables différentes, mais entre lignes d'une même table. Exemple typique : un hiérarchie "catégorie → sous-catégorie", où le parent et l'enfant sont des rangées de la même table categories, liée par un parent_id.

Pour lire ensemble une ligne (enfant) et "son" autre ligne (parent), vous utilisez un self-join : la même table apparaît deux fois dans le FROM, avec deux différents pseudonymes.

La syntaxe

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

L'alias est obligatoire : si on omet AS a / AS b, PostgreSQL n'a pas moyen de savoir de laquelle des deux copies nous parlons lorsque nous écrivons name ou 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;

Lire en anglais simple : "prenez les lignes de categories nommées enfant, et pour chacun trouvez la ligne de categories nommée parent dont id est le parent_id de l'enfant".

Le résultat exclut les catégories racines (celles avec parent_id = NULL) car un INNER JOIN saute les lignes sans correspondance. Si vous souhaitez inclure eux, utilisez LEFT JOIN.

Auto-adhésion avec LEFT pour "garder aussi les racines"

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

Vous obtenez maintenant 9 lignes (chaque catégorie de l'ensemble de données), avec parent = NULL pour les 3 racines.

Agrégation avec une auto-jointure

Il se combine très bien avec GROUP BY pour demander « combien d'enfants chacun a-t-il ? le parent a :

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;

Notez deux détails importants :

1. WHERE p.parent_id IS NULL filtre avant le JOIN : on prend uniquement le racines comme « table de gauche ».
2. COUNT(c.id) (pas COUNT(*)) renvoie 0 pour les racines sans enfants, alors que COUNT(*) renverrait 1 à cause de la ligne LEFT JOIN avec NULL.

Essayez-le vous-même

Exercice de révision