Self-JOIN: o masă cu sine

Uneori, relația pe care vrei să o traversezi este nu între doi tabele diferite, dar între rânduri ale aceluiași tabel. Exemplu tipic: a ierarhia „categorie → subcategorie”, în care atât părintele, cât și copilul sunt rânduri ale același tabel categories, legat de un parent_id.

Pentru a citi împreună un rând (copil) și celălalt rând (părinte) folosiți a self-join: același tabel apare de două ori în FROM, cu două pseudonime diferite.

Sintaxa

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

Aliasul este obligatoriu: dacă omitem AS a / AS b, PostgreSQL nu are mod de a ști la care dintre cele două copii ne referim când scriem name sau 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;

Citiți în limba engleză simplă: "luați rândurile de categories numit copil și pentru fiecare, găsiți rândul de categories numit părinte al cărui id este parent_id a copilului".

Rezultatul exclude categoriile rădăcină (cele cu parent_id = NULL) deoarece un INNER JOIN omite rânduri fără potrivire. Dacă doriți să includeți ei, utilizați LEFT JOIN.

Alăturați-vă cu STÂNGA pentru a „păstra și rădăcinile”

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

Acum obțineți 9 rânduri (fiecare categorie din setul de date), cu parent = NULL pentru cele 3 rădăcini.

Agregarea cu o auto-unire

Se combină foarte bine cu GROUP BY pentru a întreba „câți copii face fiecare părintele are":

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;

Rețineți două detalii importante:

1. WHERE p.parent_id IS NULL filtrează înainte de JOIN: luăm doar rădăcini ca „masa din stânga”.
2. COUNT(c.id) (nu COUNT(*)) returnează 0 pentru rădăcini fără copii, întrucât COUNT(*) ar returna 1 din cauza rândului LEFT JOIN cu NULL.

Încercați-l singur

Exercițiu de revizuire