Self-JOIN и иерархические данные

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

Основы self-join

При self-join одна и та же таблица используется дважды в запросе, но с разными алиасами. Это позволяет SQL-движку обрабатывать её как две независимые таблицы. Использование алиасов при self-join обязательно — без них SQL не сможет различить, к какой "копии" таблицы относится столбец.

SELECT 
    e1.employee_name AS employee,
    e2.employee_name AS manager
FROM employees e1
INNER JOIN employees e2 ON e1.manager_id = e2.employee_id;

Здесь таблица employees содержит столбец manager_id, который ссылается на employee_id другой записи в той же таблице. Запрос показывает, кто чей руководитель.

Анализ иерархических структур

Self-join идеально подходит для работы с организационными иерархиями, категориями с подкатегориями, географическими структурами (город → регион → страна).

Пример: поиск всех подчиненных конкретного руководителя

SELECT 
    m.employee_name AS manager,
    e.employee_name AS subordinate,
    e.position,
    e.salary
FROM employees e
INNER JOIN employees m ON e.manager_id = m.employee_id
WHERE m.employee_name = 'Иванов Иван'
ORDER BY e.salary DESC;

Этот запрос находит всех сотрудников, чей непосредственный руководитель — Иванов Иван, и сортирует их по зарплате.

LEFT JOIN в self-join: Используйте LEFT JOIN вместо INNER JOIN, когда нужно включить записи без пары. Например, для вывода всех сотрудников, включая тех, у кого нет руководителя:

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

Сравнение записей внутри таблицы

Self-join используется для поиска пар записей, удовлетворяющих определенному условию.

Задача: найти пользователей, зарегистрировавшихся в один день

SELECT 
    u1.user_id AS user1_id,
    u1.user_name AS user1_name,
    u2.user_id AS user2_id,
    u2.user_name AS user2_name,
    u1.registration_date
FROM users u1
INNER JOIN users u2 ON u1.registration_date = u2.registration_date
    AND u1.user_id < u2.user_id  -- Избегаем дублирования пар
WHERE u1.registration_date >= '2024-01-01';

Условие u1.user_id < u2.user_id критически важно — оно гарантирует, что каждая пара пользователей появится только один раз (без него получим и (A,B), и (B,A)).

Реферальные программы и связи

Self-join незаменим при анализе реферальных программ, где пользователи приглашают других пользователей.

SELECT 
    referrer.user_name AS referrer_name,
    referrer.registration_date AS referrer_reg_date,
    referred.user_name AS referred_name,
    referred.registration_date AS referred_reg_date,
    COUNT(*) OVER (PARTITION BY referrer.user_id) AS total_referrals
FROM users referred
INNER JOIN users referrer ON referred.referrer_id = referrer.user_id
WHERE referrer.registration_date >= '2024-01-01';

Этот запрос показывает, кто кого пригласил, и с помощью оконной функции добавляет общее количество рефералов для каждого реферера.

Поиск последовательностей событий

Задача: найти пользователей, совершивших два заказа подряд с интервалом менее 7 дней

SELECT DISTINCT
    o1.user_id,
    o1.order_id AS first_order,
    o1.order_date AS first_date,
    o2.order_id AS second_order,
    o2.order_date AS second_date,
    o2.order_date - o1.order_date AS days_between
FROM orders o1
INNER JOIN orders o2 ON o1.user_id = o2.user_id
    AND o2.order_date > o1.order_date
    AND o2.order_date <= o1.order_date + INTERVAL '7 days'
WHERE o1.order_date >= '2024-01-01';

Важные моменты

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