Self-join в SQL: как соединить таблицу саму с собой

Self-join — это обычный JOIN, только таблица соединяется сама с собой.

Звучит немного странно: зачем одной и той же таблице встречаться с самой собой? Но на практике это очень полезный приём. Он нужен, когда одна строка таблицы должна найти другую строку из этой же таблицы.

Например:

• сотрудник должен найти своего менеджера;
• задача должна найти родительскую задачу;
• товар должен сравнить себя с похожим товаром;
• пользователь должен найти другого пользователя из того же города;
• сотрудники одного отдела должны быть собраны в пары.

У self-join нет отдельного специального синтаксиса. Мы просто указываем одну и ту же таблицу два раза и даём ей разные алиасы.

Будем разбирать на таблице сотрудников:

CREATE TABLE employees (
  id          bigint PRIMARY KEY,
  name        text NOT NULL,
  manager_id  bigint REFERENCES employees(id),
  department  text,
  salary      numeric(10, 2),
  hired_at    date
);

Обратите внимание на колонку manager_id. Она ссылается на id в этой же таблице employees.

То есть менеджер — это тоже сотрудник. Просто для одного сотрудника он руководитель, а для другого — обычная строка в той же таблице.

Зачем соединять таблицу саму с собой

Представьте таблицу employees.

В ней лежат и обычные сотрудники, и менеджеры, и директор. Отдельной таблицы managers нет. Все люди находятся в одном месте.

Например:

id	name	manager_id	department
1	Anna	NULL	Management
2	Boris	1	Sales
3	Maria	1	Sales
4	Ivan	2	Sales

Как прочитать такую таблицу?

• Anna — директор, у неё manager_id IS NULL;
• Boris подчиняется Anna, потому что у него manager_id = 1;
• Maria тоже подчиняется Anna;
• Ivan подчиняется Boris, потому что у него manager_id = 2.

Если бы менеджеры лежали в отдельной таблице, мы сделали бы обычный JOIN:

SELECT ...
FROM employees e
JOIN managers m ON m.id = e.manager_id;

Но менеджеры лежат в той же таблице employees. Поэтому мы пишем эту таблицу дважды:

SELECT ...
FROM employees e
JOIN employees m ON m.id = e.manager_id;

Здесь одна и та же таблица играет две роли:

• e — сотрудник;
• m — менеджер.

Алиасы в self-join не украшение, а необходимость. Без них база не поймёт, из какой именно «версии» таблицы вы хотите взять name, id или salary.

Простой пример: сотрудник и его менеджер

Классический пример self-join — вывести сотрудника рядом с его руководителем.

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

Что происходит в запросе:

1. Берём строку сотрудника из employees AS e.
2. Смотрим его manager_id.
3. Ищем в этой же таблице строку менеджера employees AS m.
4. Соединяем строки по условию m.id = e.manager_id.

Получится примерно так:

employee	manager
Boris	Anna
Maria	Anna
Ivan	Boris

Запрос читается почти как обычная фраза:

Покажи сотрудника e и его менеджера m, где id менеджера равен manager_id сотрудника.

Почему директор пропал из результата

В предыдущем запросе есть важная ловушка.

Мы использовали обычный JOIN, то есть INNER JOIN. Он возвращает только те строки, у которых нашлась пара.

А у директора менеджера нет:

manager_id IS NULL

NULL не равен никакому id, поэтому директор не попадает в результат.

Чтобы сохранить всех сотрудников, даже тех, у кого нет менеджера, нужен LEFT JOIN.

SELECT
  e.name AS employee,
  COALESCE(m.name, 'no manager') AS manager
FROM employees AS e
LEFT JOIN employees AS m ON m.id = e.manager_id
ORDER BY m.name NULLS FIRST, e.name;

Теперь в результате будет и директор:

employee	manager
Anna	no manager
Boris	Anna
Maria	Anna
Ivan	Boris

Здесь LEFT JOIN говорит:

Возьми всех сотрудников из левой таблицы e. Если менеджер нашёлся — покажи его. Если не нашёлся — оставь сотрудника всё равно.

А COALESCE заменяет NULL на понятный текст.

Важно: строка 'no manager' в SQL-коде написана латиницей. В реальном русскоязычном интерфейсе вы можете заменить это значение уже на уровне приложения.

INNER JOIN или LEFT JOIN

Для иерархий почти всегда нужно внимательно выбирать тип соединения.

Если вы хотите показать только сотрудников, у которых есть менеджер, подойдёт JOIN:

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

Если вы хотите показать всех, включая директора или сотрудников без руководителя, нужен LEFT JOIN:

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

Разница простая:

• JOIN оставит только строки с найденной парой;
• LEFT JOIN сохранит все строки слева, даже если пары справа нет.

Для новичка это один из самых важных моментов в self-join.

Один self-join показывает только один уровень

Запрос:

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

показывает только ближайшего менеджера.

Например:

employee	manager
Ivan	Boris

Но он не покажет всю цепочку:

Ivan -> Boris -> Anna

Один self-join — это один шаг вверх по иерархии.

Если нужно пройти на любую глубину: сотрудник, его менеджер, менеджер менеджера, директор и так далее — обычно используют рекурсивный запрос через WITH RECURSIVE.

Но для базового уровня «сотрудник → менеджер» обычного self-join вполне достаточно.

Сравнение сотрудника с его менеджером

Self-join нужен не только для красивого вывода иерархии. Он отлично подходит для сравнения строк внутри одной таблицы.

Например, найдём сотрудников, которые получают больше своего менеджера.

SELECT
  e.name   AS employee,
  e.salary AS employee_salary,
  m.name   AS manager,
  m.salary AS manager_salary
FROM employees AS e
JOIN employees AS m ON m.id = e.manager_id
WHERE e.salary > m.salary;

Здесь таблица снова играет две роли:

• e — сотрудник;
• m — его менеджер.

А в WHERE мы сравниваем их зарплаты:

e.salary > m.salary

Такой запрос может быть полезен для проверки данных, HR-аналитики или поиска необычных ситуаций в зарплатной сетке.

Сравнение коллег внутри одного отдела

Другой сценарий: сравнить сотрудников из одного отдела между собой.

Например, найти пары «кто пришёл раньше — кто пришёл позже».

SELECT
  a.name AS earlier_employee,
  b.name AS later_employee,
  a.department
FROM employees AS a
JOIN employees AS b
  ON a.department = b.department
 AND a.hired_at < b.hired_at;

Здесь мы используем уже не алиасы e и m, а a и b, потому что речь не про сотрудника и менеджера, а про двух сотрудников из одной таблицы.

Условие соединения состоит из двух частей:

a.department = b.department

Эта часть говорит: сотрудники должны быть из одного отдела.

a.hired_at < b.hired_at

Эта часть говорит: сотрудник a должен быть нанят раньше сотрудника b.

В результате мы получаем пары коллег, где один пришёл раньше другого.

Почему важно не соединить строку саму с собой

Когда таблица соединяется сама с собой, легко случайно получить бессмысленные пары.

Например:

SELECT
  a.name AS employee_a,
  b.name AS employee_b,
  a.department
FROM employees AS a
JOIN employees AS b ON a.department = b.department;

Такой запрос соединит всех сотрудников одного отдела со всеми сотрудниками этого же отдела.

Если в отделе есть Anna, Boris и Maria, результат будет содержать:

employee_a	employee_b
Anna	Anna
Anna	Boris
Anna	Maria
Boris	Anna
Boris	Boris
Boris	Maria
Maria	Anna
Maria	Boris
Maria	Maria

Проблемы две:

1. Появились пары сотрудника с самим собой: Anna — Anna.
2. Одна и та же пара появилась дважды: Anna — Boris и Boris — Anna.

Для реальных задач это почти всегда мусор.

Как получить пары без дублей

Допустим, нам нужно составить пары сотрудников внутри отдела: для ревью, наставничества, созвона или совместной работы.

Правильный приём — использовать строгое сравнение по уникальному id.

SELECT
  a.name AS person_a,
  b.name AS person_b,
  a.department
FROM employees AS a
JOIN employees AS b
  ON a.department = b.department
 AND a.id < b.id
ORDER BY a.department, person_a;

Условие:

a.id < b.id

делает сразу две вещи:

• убирает соединение строки самой с собой;
• убирает дубли пар.

Почему это работает?

Пусть есть сотрудники:

id	name
1	Anna
2	Boris
3	Maria

Сравнение a.id < b.id оставит только такие пары:

person_a	person_b
Anna	Boris
Anna	Maria
Boris	Maria

А такие пары не пройдут:

person_a	person_b	why
Anna	Anna	same id
Boris	Anna	reverse duplicate
Maria	Anna	reverse duplicate
Maria	Boris	reverse duplicate

Это один из самых полезных паттернов self-join.

Запомните:

a.id < b.id

означает «каждая пара только один раз».

Почему a.id <> b.id не всегда подходит

Иногда новички пишут так:

SELECT
  a.name AS person_a,
  b.name AS person_b
FROM employees AS a
JOIN employees AS b
  ON a.department = b.department
 AND a.id <> b.id;

Это действительно убирает пары вида Anna — Anna.

Но дубли остаются:

person_a	person_b
Anna	Boris
Boris	Anna

Для некоторых задач порядок важен. Например, если вы строите маршрут «от кого к кому». Тогда Anna — Boris и Boris — Anna могут считаться разными парами.

Но если вам нужны обычные пары людей без повторов, лучше использовать:

a.id < b.id

Ещё пример: сотрудники с одинаковой зарплатой

Self-join можно использовать, чтобы найти сотрудников с одинаковой зарплатой внутри отдела.

SELECT
  a.name AS employee_a,
  b.name AS employee_b,
  a.department,
  a.salary
FROM employees AS a
JOIN employees AS b
  ON a.department = b.department
 AND a.salary = b.salary
 AND a.id < b.id
ORDER BY a.department, a.salary;

Здесь мы ищем пары, где:

• отдел одинаковый;
• зарплата одинаковая;
• пара не повторяется дважды.

Такой запрос может пригодиться для анализа зарплат, поиска совпадений или проверки правил внутри компании.

Ещё пример: кто пришёл в отдел раньше всех

Для задачи «найти первого сотрудника в отделе» обычно проще использовать оконные функции или подзапросы. Но через self-join тоже можно показать полезную идею.

Найдём сотрудников, для которых нет коллеги из того же отдела с более ранней датой найма.

SELECT
  e.name,
  e.department,
  e.hired_at
FROM employees AS e
LEFT JOIN employees AS older
  ON older.department = e.department
 AND older.hired_at < e.hired_at
WHERE older.id IS NULL;

Как это читается:

1. Берём сотрудника e.
2. Пытаемся найти сотрудника older из того же отдела, который был нанят раньше.
3. Если такой не найден, значит e — один из самых ранних сотрудников отдела.

У этого запроса есть нюанс: если несколько человек пришли в один день и раньше них никого нет, он вернёт всех таких сотрудников. Иногда это именно то, что нужно.

Производительность self-join

Self-join может быть дорогим, особенно на больших таблицах.

Почему? Потому что база фактически работает с одной таблицей как с двумя источниками данных. Если условий мало или нет подходящих индексов, количество пар может резко вырасти.

Например, если в отделе 1000 сотрудников и вы соединяете каждого с каждым, потенциально получается около миллиона комбинаций.

Особенно опасны запросы такого вида:

SELECT
  a.name,
  b.name
FROM employees AS a
JOIN employees AS b ON a.department = b.department;

Если отделы большие, результат может стать огромным.

Какие индексы помогают

Для иерархии «сотрудник → менеджер» полезен индекс по manager_id.

CREATE INDEX idx_employees_manager_id
  ON employees (manager_id);

Для поиска коллег по отделу может помочь индекс по department.

CREATE INDEX idx_employees_department
  ON employees (department);

Для запроса, где внутри отдела используется дата найма, может быть полезен составной индекс:

CREATE INDEX idx_employees_department_hired_at
  ON employees (department, hired_at);

Для пар внутри отдела по id:

CREATE INDEX idx_employees_department_id
  ON employees (department, id);

Но индексы не нужно создавать наугад. Сначала смотрите на реальные запросы, потом проверяйте план выполнения.

В PostgreSQL для этого используют:

EXPLAIN ANALYZE
SELECT
  a.name AS person_a,
  b.name AS person_b,
  a.department
FROM employees AS a
JOIN employees AS b
  ON a.department = b.department
 AND a.id < b.id;

Если таблица маленькая, база может честно решить, что проще прочитать её целиком. Это нормально. Индексы особенно важны на больших таблицах и частых запросах.

Частая ошибка: забыли алиасы

Так писать нельзя:

SELECT
  name,
  name
FROM employees
JOIN employees ON id = manager_id;

База не поймёт, какой именно name вы имеете в виду. Ведь таблица участвует в запросе два раза.

Нужно дать каждой роли своё имя:

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

Хорошие алиасы делают запрос понятным.

Для иерархий удобно использовать:

• e — employee;
• m — manager.

Для сравнения двух строк:

• a и b;
• first и second;
• child и parent;
• current_row и previous_row.

Главное — чтобы читатель запроса понимал роли.

Частая ошибка: условие соединения написали в WHERE вместо ON

Иногда запросы с JOIN пишут так:

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

Для INNER JOIN это часто даст тот же результат, но для LEFT JOIN может сломать смысл.

Если условие по правой таблице перенести в WHERE, строки без менеджера могут исчезнуть. То есть LEFT JOIN неожиданно начнёт вести себя как обычный JOIN.

Правильнее держать условие связи в ON:

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

Для новичка хорошее правило такое:

То, как таблицы связаны между собой, пишем в ON. То, какие строки хотим оставить после соединения, пишем в WHERE.

Self-join в PostgreSQL, MySQL и ClickHouse

Сам базовый синтаксис self-join работает почти одинаково в PostgreSQL, MySQL и ClickHouse.

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

Но есть нюансы.

PostgreSQL

В PostgreSQL все примеры выше работают привычно.

Для сортировки строк с NULL можно использовать NULLS FIRST или NULLS LAST:

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

Для глубоких иерархий, где нужно подняться от сотрудника до самого верхнего руководителя, используют WITH RECURSIVE.

MySQL

В MySQL обычный self-join тоже работает.

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

В старых версиях MySQL до 8.0 не было рекурсивных CTE, поэтому произвольные иерархии строить было сложнее. В MySQL 8.0 и новее WITH RECURSIVE доступен.

Также помните, что синтаксис сортировки NULLS FIRST в MySQL отличается от PostgreSQL. Для простого self-join это не мешает, но при переносе запросов сортировку стоит проверять отдельно.

ClickHouse

В ClickHouse JOIN тоже есть, но это аналитическая колоночная СУБД, и соединения там часто дороже, чем в классических OLTP-базах вроде PostgreSQL или MySQL.

Простой self-join возможен:

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

Но на больших данных стоит быть осторожнее, особенно с условиями неравенства:

a.id < b.id

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

Идея простая: в PostgreSQL self-join — обычный рабочий инструмент. В ClickHouse его тоже можно использовать, но на больших объёмах лучше сначала подумать о структуре данных.

Когда использовать self-join

Self-join хорошо подходит, если одна строка должна найти другую строку в той же таблице.

Самые частые ситуации:

1. Иерархия:

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

2. Сравнение с менеджером:

SELECT
  e.name AS employee,
  e.salary AS employee_salary,
  m.name AS manager,
  m.salary AS manager_salary
FROM employees AS e
JOIN employees AS m ON m.id = e.manager_id
WHERE e.salary > m.salary;

3. Пары сотрудников без дублей:

SELECT
  a.name AS person_a,
  b.name AS person_b,
  a.department
FROM employees AS a
JOIN employees AS b
  ON a.department = b.department
 AND a.id < b.id;

4. Поиск похожих строк:

SELECT
  a.name AS employee_a,
  b.name AS employee_b,
  a.department,
  a.salary
FROM employees AS a
JOIN employees AS b
  ON a.department = b.department
 AND a.salary = b.salary
 AND a.id < b.id;

Главное из статьи

Self-join — это не отдельная команда SQL, а обычный JOIN, где одна и та же таблица участвует в запросе два раза.

Чтобы база различала роли таблицы, обязательно используйте алиасы:

FROM employees AS e
JOIN employees AS m ON m.id = e.manager_id

Для иерархий вроде «сотрудник → менеджер» часто нужен LEFT JOIN, иначе строки без родителя пропадут:

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

Для сравнения строк внутри одной таблицы используйте разные алиасы и сравнивайте нужные колонки:

WHERE e.salary > m.salary

Для пар без дублей используйте строгое сравнение по уникальному ключу:

a.id < b.id

Оно убирает и пары строки с самой собой, и зеркальные повторы.

На больших таблицах self-join может быть дорогим, поэтому проверяйте план через EXPLAIN ANALYZE и добавляйте индексы на колонки соединения: manager_id, department, hired_at, id.

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