Self-JOIN: una tabla con ella misma

A veces la relación que deseas atravesar no es entre dos tablas diferentes, pero entre filas de la misma tabla. Ejemplo típico: un Jerarquía de "categoría → subcategoría", donde tanto el padre como el hijo son filas de la misma tabla categories, unida por un parent_id.

Para leer juntos una fila (secundaria) y "su" otra fila (principal), utilice un autounión: la misma tabla aparece dos veces en FROM, con dos diferentes alias.

La sintaxis

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

El alias es obligatorio: si omitimos AS a / AS b, PostgreSQL no tiene manera de saber a cuál de las dos copias nos referimos cuando escribimos 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;

Leer en inglés sencillo: "tome las filas de categories denominadas niño y para cada uno encuentre la fila de categories llamada padre cuyo id es el CÓDIGOPH3 del niño".

El resultado excluye las categorías raíz (aquellas con parent_id = NULL) porque un INNER JOIN omite filas que no coinciden. Si quieres incluir ellos, utilice LEFT JOIN.

Autounirse con IZQUIERDA para "conservar las raíces también"

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

Ahora obtienes 9 filas (cada categoría en el conjunto de datos), con parent = NULL para las 3 raíces.

Agregando con una autounión

Combina muy bien con GROUP BY para preguntar "¿cuántos niños tiene cada uno?" padre tiene":

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;

Tenga en cuenta dos detalles importantes:

1. WHERE p.parent_id IS NULL filtra antes de JOIN: tomamos solo el raíces como la "tabla de la izquierda".
2. COUNT(c.id) (no COUNT(*)) devuelve 0 para raíces sin hijos, mientras que COUNT(*) devolvería 1 debido a la fila LEFT JOIN con NULO.

Pruébalo tú mismo

Ejercicio de revisión