Self-JOIN: a table with itself

Sometimes the relationship you want to traverse is not between two different tables, but between rows of the same table. Typical example: a "category → subcategory" hierarchy, where both parent and child are rows of the same categories table, linked by a parent_id.

To read together one row (child) and "its" other row (parent) you use a self-join: the same table appears twice in the FROM, with two different aliases.

The syntax

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

The alias is mandatory: if we omit AS a / AS b, PostgreSQL has no way to know which of the two copies we mean when we write name or 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;

Read in plain English: "take the rows of categories named child, and for each one find the row of categories named parent whose id is the parent_id of the child".

The result excludes the root categories (those with parent_id = NULL) because an INNER JOIN skips rows with no match. If you want to include them, use LEFT JOIN.

Self-join with LEFT to "keep the roots too"

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

Now you get 9 rows (every category in the dataset), with parent = NULL for the 3 roots.

Aggregating with a self-join

It combines very well with GROUP BY to ask "how many children does each parent have":

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;

Note two important details:

1. WHERE p.parent_id IS NULL filters before the JOIN: we take only the roots as the "left-hand table".
2. COUNT(c.id) (not COUNT(*)) returns 0 for roots without children, whereas COUNT(*) would return 1 because of the LEFT JOIN row with NULL.

Try it yourself

Review exercise