sql-tutorial

Самосоединение (Self Join) в SQL

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

Самосоединение работает как обычный INNER JOIN, LEFT JOIN, RIGHT JOIN или даже FULL JOIN, но вместо двух разных таблиц вы используете одну таблицу дважды (или больше раз), присваивая ей разные псевдонимы (aliases).

Почему нужны псевдонимы?

Без псевдонимов SQL не поймёт, к какой «копии» таблицы относится каждая колонка. Псевдонимы позволяют трактовать одну физическую таблицу как две логические.

Пример 1: Сотрудники и их менеджеры (иерархия)

См onecompiler.com

Создадим таблицу Employees:

CREATE TABLE Employees (
    employee_id   INT PRIMARY KEY,
    employee_name VARCHAR(100),
    manager_id    INT NULL,          -- NULL у генерального директора
    salary        DECIMAL(10,2)      -- зарплата
);

INSERT INTO Employees VALUES
(1, 'Иван Иванов',    NULL, 150000),
(2, 'Мария Петрова',  1,    120000),
(3, 'Сергей Сидоров', 1,     90000),
(4, 'Анна Кузнецова', 2,    110000),
(5, 'Пётр Смирнов',   2,     95000),
(6, 'Елена Васильева',3,     80000);

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

SELECT 
    e.employee_name          AS Сотрудник,
    m.employee_name          AS Руководитель,
    e.salary                 AS Зарплата_сотрудника,
    COALESCE(m.salary, 0)    AS Зарплата_руководителя
FROM Employees AS e
LEFT JOIN Employees AS m     -- LEFT, чтобы включить директора без руководителя
    ON e.manager_id = m.employee_id
ORDER BY e.employee_id;

e — псевдоним для «сотрудника». m — псевдоним для «менеджера» (той же таблицы).

Если использовать INNER JOIN, директор исчезнет из результата.

Пример 2. Поиск пар объектов с одинаковыми атрибутами (но не самих себя)

Например, клиенты, живущие в одном городе: см onecompiler.com

SELECT 
    A.CustomerName AS Клиент1,
    B.CustomerName AS Клиент2,
    A.City
FROM Customers A
JOIN Customers B 
    ON A.City = B.City 
   AND A.CustomerID <> B.CustomerID   -- исключаем пару самого с собой
ORDER BY A.City, A.CustomerName;

Важный момент: условие A.CustomerID <> B.CustomerID исключает пару самого с собой. Но нужно также убрать симметричные дубли пар, такие как

Иванов │ Кузнецова │ Львов │

│ Кузнецова │ Иванов │ Львов │

Для этого пишем условие A.CustomerID < B.CustomerID

Альтернативное решение задачи поиска дубликатов по полю - это группировка: GROUP BY ... HAVING COUNT(*) > 1

Пример 3. Сравнение строк внутри одной таблицы

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

SELECT 
    e.employee_name AS Сотрудник,
    e.salary        AS Зарплата_сотрудника,
    m.employee_name AS Руководитель,
    m.salary        AS Зарплата_руководителя
FROM Employees e
JOIN Employees m ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;

Пример 4. Несколько самосоединений

Можно делать несколько самосоединений подряд: менеджер менеджера

SELECT 
    emp.employee_name          AS Сотрудник,
    mgr.employee_name          AS Непосредственный_руководитель,
    grandmgr.employee_name     AS Руководитель_руководителя
FROM Employees emp
LEFT JOIN Employees mgr      ON emp.manager_id = mgr.employee_id
LEFT JOIN Employees grandmgr ON mgr.manager_id = grandmgr.employee_id;

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

Self Join может быть дорогим, особенно на больших таблицах, потому что создается декартово произведение до применения условия ON.

Решение: создавайте индексы на столбцы из ON

Рекомендации

• Всегда используйте явные псевдонимы (AS можно опускать)
• В SELECT явно указывайте, из какой «копии» берёте колонку
• Добавляйте комментарии к сложным self join
• Тестируйте на небольших данных, проверяя случаи с NULL и без совпадений:
  • INNER выдаст только пары, где есть совпадение
  • LEFT — все записи из первой «копии» + совпадения из второй
  • FULL - все строки из обоих экземпляров, даже без совпадений

Домашнее задание

Дана таблица Студенты:

CREATE TABLE Students (
    student_id   INT PRIMARY KEY,
    student_name VARCHAR(50),
    city         VARCHAR(30),
    age          INT,
    course       INT
);

INSERT INTO Students VALUES
(1, 'Иван',     'Москва', 20, 2),
(2, 'Мария',    'Киев',   21, 3),
(3, 'Алексей',  'Москва', 19, 1),
(4, 'Ольга',    'Киев',   22, 4),
(5, 'Дмитрий',  'Москва', 20, 2),
(6, 'Анна',     'Львов', 21, 3),
(7, 'Сергей',   'Москва', 20, 2);

Найдите все возможные пары студентов, которые:

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

В результате должны быть колонки:

• Студент_1
• Студент_2
• Город
• Курс_1
• Курс_2

Выведите каждую пару только один раз