Self join в SQL: самосоединение таблицы, примеры

Коротко: self join — это обычное соединение таблицы с самой собой, где одну и ту же таблицу подключают дважды под разными алиасами (например e и m) и связывают её строки между собой. Так решают задачи, где данные ссылаются друг на друга внутри одной таблицы: иерархия сотрудник — менеджер, сравнение соседних строк, поиск пар и дубликатов. Никакого особого синтаксиса нет — только два псевдонима и условие соединения.

Разберём self join от простого к сложному: как записать его без путаницы, как развернуть цепочку подчинения, как сравнить строку с предыдущей и как за один запрос выловить дубликаты. По дороге — типичные ловушки, из-за которых запрос молча возвращает мусор.

Что такое self join и зачем соединять таблицу с собой?

Self join (самосоединение) — это соединение таблицы с её же копией. Физически в базе одна таблица, но в запросе мы обращаемся к ней как к двум разным наборам строк. Это работает, потому что SQL оперирует не «файлами», а логическими наборами: одну таблицу можно подставить в FROM сколько угодно раз, лишь бы каждое вхождение получило собственное имя.

Зачем это нужно? Есть класс задач, где связанные сущности лежат в одной таблице, а не в двух разных:

• Иерархии. В таблице employees есть колонка manager_id, которая ссылается на id в той же таблице. Чтобы вывести «сотрудник и имя его руководителя», нужно соединить таблицу саму с собой.
• Сравнение соседних строк. Найти дни, когда выручка выросла относительно предыдущего дня.
• Поиск пар. Сотрудники из одного отдела, товары одной категории, пользователи одного города — все комбинации внутри группы.
• Дубликаты. Записи с одинаковым email, но разными id.

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

Как правильно записать self join через алиасы?

Ключевой инструмент — алиасы (псевдонимы). Без них база не поймёт, к какому из двух вхождений таблицы относится колонка name, и выдаст ошибку ambiguous column.

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

CREATE TABLE employees (
    id         INT PRIMARY KEY,
    name       TEXT,
    manager_id INT,      -- ссылка на employees.id
    salary     INT
);

INSERT INTO employees VALUES
(1, 'Ирина',   NULL, 400000),  -- директор, без руководителя
(2, 'Павел',   1,    250000),
(3, 'Марина',  1,    230000),
(4, 'Денис',   2,    180000),
(5, 'Олег',    2,    170000),
(6, 'Света',   3,    175000);

Соединяем таблицу саму с собой: e — сотрудник, m — его менеджер.

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

Результат:

Три правила, чтобы self join не превратился в кашу:

• Всегда давай осмысленные алиасы. Не a/b, а e/m (employee/manager) или cur/prev. Через месяц запрос читаешь ты сам.
• Каждую колонку квалифицируй через алиас — e.name, m.salary. Даже если сейчас имя уникально, после добавления колонок появится неоднозначность.
• Условие соединения — в ON, а не только в WHERE. Так намерение видно сразу, и легче переключиться на LEFT JOIN.

Обратите внимание: Ирина (директор) в результат не попала, потому что INNER JOIN отбрасывает строки без пары, а у неё manager_id = NULL. Это первая ловушка, к которой вернёмся ниже.

Как построить иерархию сотрудник — менеджер?

Иерархия — самый классический пример self join. Чтобы в отчёт попал и директор (у которого нет руководителя), меняем INNER JOIN на LEFT JOIN:

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
ORDER BY e.id;

Теперь Ирина остаётся в выборке со значением NULL в поле менеджера, которое мы аккуратно подменяем через COALESCE. Подробнее про обработку пустых значений — в разборе NULL, типы данных, COALESCE и CAST.

Часто спрашивают: «а как посчитать сотрудников у каждого менеджера?». Тоже self join плюс группировка:

SELECT
    m.name AS manager,
    COUNT(e.id) AS reports
FROM employees AS m
LEFT JOIN employees AS e
    ON e.manager_id = m.id
GROUP BY m.id, m.name
ORDER BY reports DESC;

Здесь ведущей таблицей стал менеджер m, а подчинённые e подтягиваются к нему. LEFT JOIN гарантирует, что менеджер с нулём подчинённых тоже попадёт в отчёт со счётчиком 0 (COUNT по e.id не считает NULL-строки).

Важное ограничение: одиночный self join разворачивает ровно один уровень иерархии — сотрудник и его прямой руководитель. Чтобы подняться на два уровня («руководитель руководителя»), придётся присоединить таблицу третий раз:

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

А вот для иерархии произвольной глубины (вся цепочка до самого верха) self join уже не годится — там нужен рекурсивный CTE. Его механику разбираем отдельно в Рекурсивные CTE в SQL.

Как сравнить соседние строки через самосоединение?

Задача «сравнить каждую строку с предыдущей» — это self join по ключу со сдвигом. Классика: найти дни, когда выручка выросла относительно вчерашней.

CREATE TABLE daily_revenue (
    day     DATE,
    revenue INT
);

INSERT INTO daily_revenue VALUES
('2026-01-01', 120000),
('2026-01-02', 135000),
('2026-01-03', 110000),
('2026-01-04', 160000);

Соединяем таблицу саму с собой так, чтобы к каждому дню cur подтянулся предыдущий день prev:

SELECT
    cur.day,
    prev.revenue AS yesterday,
    cur.revenue  AS today,
    cur.revenue - prev.revenue AS delta
FROM daily_revenue AS cur
JOIN daily_revenue AS prev
    ON prev.day = cur.day - INTERVAL '1 day'
WHERE cur.revenue > prev.revenue;

Результат — только «растущие» дни:

Ключевой момент — условие соединения prev.day = cur.day - INTERVAL '1 day'. Оно надёжно работает, только если даты идут без пропусков. Если в данных есть дыры (выходные без продаж), жёсткая привязка «минус один день» промахнётся. В таких случаях соединяют по «ближайшей предыдущей дате» через подзапрос или, что честнее, переходят на оконную функцию LAG (об этом в последнем разделе).

Тот же приём годится для аналитики поведения: сравнить два соседних события пользователя, посчитать время между покупками, найти разрыв в сессиях. Когда данных много и это разовый расчёт — удобно вынести в Python и pandas, где сдвиг строки делается методом shift().

Как найти пары и дубликаты одним запросом?

Self join отлично ищет пары строк внутри одной таблицы. Пример: найти всех сотрудников из одного отдела (все возможные пары коллег).

SELECT
    a.name AS person_1,
    b.name AS person_2,
    a.dept
FROM staff AS a
JOIN staff AS b
    ON a.dept = b.dept
    AND a.id < b.id;   -- убираем зеркальные дубли и пары «сам с собой»

Условие a.id < b.id — обязательная деталь. Без него запрос вернёт:

• пару (Денис, Денис) — строка соединится сама с собой;
• обе версии (Денис, Олег) и (Олег, Денис) — зеркальные дубли.

Строгое неравенство a.id < b.id оставляет ровно одну версию каждой пары и выкидывает самосоединение строки. Если нужно включить пары «сам с собой», используют <=.

Теперь дубликаты. Допустим, в таблице пользователей один email завёлся под несколькими id:

SELECT
    u1.id   AS id_1,
    u2.id   AS id_2,
    u1.email
FROM users AS u1
JOIN users AS u2
    ON u1.email = u2.email
    AND u1.id < u2.id;

Запрос вернёт пары id с совпадающим email — ровно то, что нужно для чистки данных. Правда, для простого «сколько дублей всего» чаще берут GROUP BY email HAVING COUNT(*) > 1 — это дешевле. Про разные способы дедупликации есть отдельный разбор: Дедупликация данных: ROW_NUMBER и DISTINCT ON. Self join выигрывает, когда нужно именно показать конкретные пары конфликтующих записей, а не просто их посчитать.

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

Какие ловушки подстерегают в self join?

Self join выглядит безобидно, но именно на нём чаще всего теряют строки или получают взрывной рост результата. Собрал главные грабли:

• INNER вместо LEFT теряет «корневые» строки. Директор без manager_id, первый день без «вчера», товар без родительской категории — все они выпадают при INNER JOIN. Если корневые записи важны, берите LEFT JOIN и обрабатывайте NULL.
• Забытое a.id < b.id в поиске пар. Без него — зеркальные дубли и самосоединения строк. Результат раздувается примерно вдвое плюс диагональ.
• Декартово произведение по неполному ON. Если условие соединения слишком слабое (например, соединяем по dept, а отделов мало), таблица на N строк может дать до N×N строк. На больших данных это кладёт запрос.
• Сравнение с NULL через =. Условие prev.day = cur.day - INTERVAL '1 day' никогда не сматчит NULL-дату, а WHERE x = NULL всегда ложно. Помните: NULL не равен ничему, даже самому себе.
• Неквалифицированные колонки. WHERE salary > 200000 при двух алиасах — ошибка неоднозначности или, хуже, тихо неверный фильтр. Всегда пишите e.salary.
• Дубли из-за связи «один ко многим». Если справа на один ключ приходится несколько строк, левые строки размножатся. Проверяйте кардинальность до агрегации.

Практический совет: перед тем как навешивать WHERE и агрегаты, запустите «голый» self join и посчитайте строки. Если число строк неожиданно большое — условие соединения дырявое. Разбор частых промахов собран в 10 антипаттернов SQL.

Чем self join отличается от оконных функций?

Половину задач «сравни строку с соседней» сегодня решают не self join, а оконными функциями LAG / LEAD. Сравним лоб в лоб на той же выручке:

-- через оконную функцию: чище и без ловушки с пропусками ключа
SELECT
    day,
    revenue,
    LAG(revenue) OVER (ORDER BY day)               AS yesterday,
    revenue - LAG(revenue) OVER (ORDER BY day)     AS delta
FROM daily_revenue
ORDER BY day;

Когда что выбирать:

Грубое правило: если задача про связь строк по значению (одинаковый отдел, ссылка на id, совпадающий email) — это self join. Если про позицию строки в упорядоченном ряду (предыдущая, следующая, ранг, накопление) — это оконная функция. Границы пересекаются, и опытный аналитик держит оба инструмента. Полное погружение в окна — в Оконных функциях SQL: полный гайд, а системно всё соединяется в SQL с нуля: учебник для аналитика и на курсе по SQL.

Понимание, где self join, а где окно — частый вопрос на интервью в продуктовых командах, где считают retention и отток когортами: и то, и другое строится на сравнении строк одного пользователя во времени.

Что запомнить про self join и где потренироваться?

Соберём в одну картину:

• Self join — обычный JOIN, где таблица подключена дважды под разными алиасами.
• Алиасы обязательны, каждую колонку квалифицируй.
• Иерархия на один уровень — self join; на любую глубину — рекурсивный CTE.
• Поиск пар и дубликатов — соединение с условием a.id < b.id.
• Сравнение соседних строк по позиции — почти всегда чище через LAG/LEAD.
• Главные ловушки: INNER теряет корни, слабый ON даёт декартов взрыв, NULL не равен ничему.

Теория укладывается в голове только через руки. В SQL-тренажёре первые 5 задач открыты бесплатно — можно прямо сейчас написать self join на иерархии и на поиске дубликатов, проверить на настоящем PostgreSQL и разобрать решение. Дальше, если зайдёт, полный доступ ко всем 425 задачам открывается по подписке Pro — без спешки, в своём темпе. А перед собеседованием прогоните формулировки в банке вопросов и на мок-собесе с AI.