See artikkel on praegu venekeelne — ingliskeelne tõlge on töös.
Self-join — это обычный 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
);
Зачем вообще соединять таблицу с собой
Ключевая идея: одна строка таблицы должна «увидеть» другую строку той же таблицы. Если бы менеджеры лежали в отдельной таблице managers, вы бы написали обычный JOIN. Но manager_id указывает на ту же employees, поэтому таблицу нужно подставить дважды.
Алиасы здесь не роскошь, а необходимость: без них СУБД не сможет различить «строку-сотрудника» и «строку-менеджера», и вы получите ошибку неоднозначности столбцов.
e — строка сотрудника;m — строка его менеджера;- условие соединения связывает
e.manager_id со m.id.
Иерархия сотрудник → менеджер
Классика жанра — вывести имя сотрудника рядом с именем его руководителя:
SELECT e.name AS employee,
m.name AS manager
FROM employees AS e
JOIN employees AS m ON m.id = e.manager_id;
Тут есть ловушка: у генерального директора manager_id IS NULL, и обычный (внутренний) JOIN его выкинет — NULL не равен ничему. Чтобы сохранить всех, включая «безначальников», берите LEFT JOIN:
SELECT e.name AS employee,
COALESCE(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;
COALESCE подставит заглушку вместо NULL, а NULLS FIRST поднимет CEO наверх. Один self-join даёт только один уровень иерархии. Для произвольной глубины (менеджер менеджера и так далее) нужен рекурсивный WITH RECURSIVE, но это уже отдельная тема.
Сравнение строк внутри одной таблицы
Self-join отлично подходит, когда нужно сравнить строки между собой. Например, найти сотрудников, которые получают больше, чем их собственный менеджер:
SELECT e.name AS employee,
e.salary AS emp_salary,
m.name AS manager,
m.salary AS mgr_salary
FROM employees AS e
JOIN employees AS m ON m.id = e.manager_id
WHERE e.salary > m.salary;
Или сравнить каждого с коллегой по отделу — кто пришёл раньше:
SELECT a.name AS earlier, b.name AS later, a.department
FROM employees AS a
JOIN employees AS b
ON a.department = b.department
AND a.hired_at < b.hired_at;
Обратите внимание: условие a.hired_at < b.hired_at одновременно решает две задачи — связывает только людей из одной пары по времени и не даёт строке соединиться самой с собой.
Пары без дублей и без само-соединения
Если вы хотите все пары сотрудников внутри отдела (например, для парного код-ревью), наивный JOIN по a.department = b.department выдаст мусор: каждый соединится сам с собой (Алиса–Алиса) и каждая пара появится дважды (Алиса–Боб и Боб–Алиса).
Лечится одним предикатом — сравнением по 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 исключило бы только само-соединение, но дубли остались бы;a.id < b.id отсекает и то и другое: каждая неупорядоченная пара остаётся ровно один раз.
Этот трюк со строгим неравенством — самый частый паттерн при генерации пар.
Подводный камень. Помните про производительность: self-join по неиндексированному manager_id или department на большой таблице может развернуться в дорогой Nested Loop. Создавайте индексы на колонках соединения (CREATE INDEX ON employees(manager_id)) и проверяйте план через EXPLAIN ANALYZE.
Различия между СУБД
Синтаксис self-join стандартный и работает одинаково в PostgreSQL, MySQL и ClickHouse, но есть нюансы:
- PostgreSQL / MySQL — всё как выше;
LEFT JOIN для сохранения строк с NULL-родителем обязателен.
- MySQL до 8.0 не имел
WITH RECURSIVE, так что глубокие иерархии строились только процедурно; в 8.0+ рекурсия доступна.
- ClickHouse —
JOIN есть, но он дороже и менее гибок; для иерархий чаще берут специальные функции массивов или денормализацию, а равнозначность NULL в JOIN ведёт себя иначе. На больших данных в ClickHouse self-join по неравенству (a.id < b.id) лучше избегать — он плохо распараллеливается.
Итого: self-join — это просто JOIN таблицы с собой через алиасы. Запомните три приёма: LEFT JOIN для иерархий с NULL, сравнение колонок для анализа строк и строгое неравенство id для пар без дублей.
Self-join — это обычный
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 );Зачем вообще соединять таблицу с собой
Ключевая идея: одна строка таблицы должна «увидеть» другую строку той же таблицы. Если бы менеджеры лежали в отдельной таблице
managers, вы бы написали обычныйJOIN. Ноmanager_idуказывает на ту жеemployees, поэтому таблицу нужно подставить дважды.Алиасы здесь не роскошь, а необходимость: без них СУБД не сможет различить «строку-сотрудника» и «строку-менеджера», и вы получите ошибку неоднозначности столбцов.
e— строка сотрудника;m— строка его менеджера;e.manager_idсоm.id.Иерархия сотрудник → менеджер
Классика жанра — вывести имя сотрудника рядом с именем его руководителя:
SELECT e.name AS employee, m.name AS manager FROM employees AS e JOIN employees AS m ON m.id = e.manager_id;Тут есть ловушка: у генерального директора
manager_id IS NULL, и обычный (внутренний)JOINего выкинет —NULLне равен ничему. Чтобы сохранить всех, включая «безначальников», беритеLEFT JOIN:SELECT e.name AS employee, COALESCE(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;COALESCEподставит заглушку вместоNULL, аNULLS FIRSTподнимет CEO наверх. Один self-join даёт только один уровень иерархии. Для произвольной глубины (менеджер менеджера и так далее) нужен рекурсивныйWITH RECURSIVE, но это уже отдельная тема.Сравнение строк внутри одной таблицы
Self-join отлично подходит, когда нужно сравнить строки между собой. Например, найти сотрудников, которые получают больше, чем их собственный менеджер:
SELECT e.name AS employee, e.salary AS emp_salary, m.name AS manager, m.salary AS mgr_salary FROM employees AS e JOIN employees AS m ON m.id = e.manager_id WHERE e.salary > m.salary;Или сравнить каждого с коллегой по отделу — кто пришёл раньше:
SELECT a.name AS earlier, b.name AS later, a.department FROM employees AS a JOIN employees AS b ON a.department = b.department AND a.hired_at < b.hired_at; -- a нанят строго раньше bОбратите внимание: условие
a.hired_at < b.hired_atодновременно решает две задачи — связывает только людей из одной пары по времени и не даёт строке соединиться самой с собой.Пары без дублей и без само-соединения
Если вы хотите все пары сотрудников внутри отдела (например, для парного код-ревью), наивный
JOINпоa.department = b.departmentвыдаст мусор: каждый соединится сам с собой (Алиса–Алиса) и каждая пара появится дважды (Алиса–Боб и Боб–Алиса).Лечится одним предикатом — сравнением по
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исключило бы только само-соединение, но дубли остались бы;a.id < b.idотсекает и то и другое: каждая неупорядоченная пара остаётся ровно один раз.Этот трюк со строгим неравенством — самый частый паттерн при генерации пар.
Подводный камень. Помните про производительность: self-join по неиндексированному
manager_idилиdepartmentна большой таблице может развернуться в дорогойNested Loop. Создавайте индексы на колонках соединения (CREATE INDEX ON employees(manager_id)) и проверяйте план черезEXPLAIN ANALYZE.Различия между СУБД
Синтаксис self-join стандартный и работает одинаково в PostgreSQL, MySQL и ClickHouse, но есть нюансы:
LEFT JOINдля сохранения строк сNULL-родителем обязателен.WITH RECURSIVE, так что глубокие иерархии строились только процедурно; в 8.0+ рекурсия доступна.JOINесть, но он дороже и менее гибок; для иерархий чаще берут специальные функции массивов или денормализацию, а равнозначностьNULLвJOINведёт себя иначе. На больших данных в ClickHouse self-join по неравенству (a.id < b.id) лучше избегать — он плохо распараллеливается.Итого: self-join — это просто
JOINтаблицы с собой через алиасы. Запомните три приёма:LEFT JOINдля иерархий сNULL, сравнение колонок для анализа строк и строгое неравенствоidдля пар без дублей.