Self Joins: A Table Meets Itself

When the foreign key points home

This shape is everywhere once you look: a comment stores the id of its parent comment, a category stores its parent category, a user stores who referred them. Whenever a row relates to another row of the same kind, the foreign key points at its own table. The classic example is an employees table that stores who manages whom with a manager_id column pointing at another row in the same table:

Ana is the boss (no manager). To print each worker next to their manager's name, you join the table to itself. That requires giving the table two temporary names, called aliases, with AS:

SELECT worker.name, boss.name
FROM employees AS worker
JOIN employees AS boss ON boss.id = worker.manager_id
ORDER BY worker.id;

Mentally there are now two copies of employees: one playing the worker role, one playing the boss role. The ON rule connects a worker's manager_id to a boss's id.