Что такое self-join и когда он используется?

Self-Join (самосоединение таблицы)

Определение

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

Синтаксис

SELECT a.column1, b.column2
FROM table_name a
JOIN table_name b ON a.key = b.key AND a.id != b.id;

Ключевой момент: одна и та же таблица используется дважды с разными алиасами (a и b).

Типичные случаи использования

1. Иерархические данные

Наиболее частый случай — работа с иерархией, например, структурой компании:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT
);

SELECT 
    e.name AS employee_name,
    m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

2. Поиск дубликатов

SELECT 
    e1.employee_id,
    e1.name,
    e2.employee_id,
    e2.name
FROM employees e1
JOIN employees e2 
    ON e1.salary = e2.salary 
    AND e1.employee_id < e2.employee_id;

3. Поиск связанных объектов

SELECT 
    f1.user_id,
    f2.friend_id
FROM friendships f1
JOIN friendships f2 ON f1.friend_id = f2.user_id
WHERE f1.user_id = 123;

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

Для оптимизации self-join важно создавать индексы на колонках JOIN:

CREATE INDEX idx_employees_manager_id ON employees(manager_id);

Self-Join используется для работы с иерархиями, поиска дубликатов и сравнения записей в одной таблице. Это мощный инструмент в арсенале Data Engineer.