Как соединить таблицу саму с собой в SQL (self join)?

Когда строка ссылается на другую строку той же таблицы, делают self join — соединение таблицы с самой собой. Секрет в том, что таблицу нужно подключить дважды под разными алиасами, как будто это две разные таблицы:

SELECT
  e.name  AS employee,
  m.name  AS manager
FROM employees AS e
LEFT JOIN employees AS m
  ON e.manager_id = m.id;

Здесь e — это «сотрудник», а m — «его руководитель», хотя физически это одна таблица employees. Условие e.manager_id = m.id связывает сотрудника с тем, кто им руководит.

Почему LEFT JOIN, а не INNER: у самого верхнего начальника manager_id обычно NULL (у него нет руководителя). INNER JOIN выкинул бы его из результата, а LEFT JOIN оставит, подставив в колонку manager значение NULL. Если хотите, можно показать это явно:

SELECT e.name AS employee,
       COALESCE(m.name, 'нет руководителя') AS manager
FROM employees AS e
LEFT JOIN employees AS m ON e.manager_id = m.id;

Главное правило self join — обязательные разные алиасы. Без них (FROM employees JOIN employees ON ...) СУБД не поймёт, к какому из двух экземпляров относится столбец id или name, и выдаст ошибку неоднозначности.

Такой приём используют не только для иерархий «сотрудник-начальник», но и чтобы, например, найти пары строк с одинаковым значением какого-то поля.