Что такое SELF JOIN в SQL?

SELF JOIN — это когда таблица объединяется сама с собой. Это вполне валидный и часто используемый способ сравнения строк одной таблицы между собой. При self join используются два разных alias'а для одной и той же таблицы.

Основная концепция

Самый простой способ понять SELF JOIN — это представить, что мы копируем таблицу, и потом объединяем исходную с копией по определенному условию.

SELECT a.id, a.name, b.id, b.name
FROM employees a
INNER JOIN employees b ON a.manager_id = b.id;

В этом примере a и b — это два разных alias'а одной таблицы employees.

Практические примеры

Пример 1: Иерархия сотрудников (сотрудник и его менеджер)

SELECT 
  a.employee_id,
  a.employee_name,
  b.employee_id as manager_id,
  b.employee_name as manager_name
FROM employees a
LEFT JOIN employees b ON a.manager_id = b.employee_id;

Пример 2: Поиск соответствий между пользователями (кто дружит с кем)

SELECT 
  a.user_id,
  a.username as user1,
  b.user_id as friend_id,
  b.username as user2
FROM users a
INNER JOIN users b ON a.city = b.city 
  AND a.user_id < b.user_id
WHERE a.user_id != b.user_id;

Пример 3: Сравнение зарплат сотрудников одного отдела

SELECT 
  a.employee_id,
  a.employee_name,
  a.salary,
  b.employee_id,
  b.employee_name,
  b.salary,
  (a.salary - b.salary) as salary_difference
FROM employees a
INNER JOIN employees b ON a.department_id = b.department_id
WHERE a.employee_id < b.employee_id
  AND a.salary > b.salary;

Виды SELF JOIN

INNER SELF JOIN — возвращает только совпадающие строки

SELECT a.id, b.id
FROM products a
INNER JOIN products b ON a.category = b.category
WHERE a.id < b.id;

LEFT SELF JOIN — может включать строки, у которых нет совпадений

SELECT a.id, a.name, b.id, b.parent_id
FROM categories a
LEFT JOIN categories b ON a.id = b.parent_id;

Практический пример: поиск постоянных клиентов

SELECT DISTINCT
  a.customer_id,
  a.customer_name,
  COUNT(b.order_id) as order_count
FROM orders a
INNER JOIN orders b ON a.customer_id = b.customer_id 
  AND MONTH(a.order_date) = MONTH(b.order_date)
WHERE a.customer_id = b.customer_id
GROUP BY a.customer_id, a.customer_name
HAVING COUNT(b.order_id) > 2;

Когда НЕ использовать SELF JOIN

• Если нужно сравнение внутри одной строки — используй вычисляемые столбцы
• Для большинства операций с иерархией лучше использовать Common Table Expressions (CTE) или Window Functions

Альтернатива с Window Function

SELECT 
  employee_id,
  employee_name,
  salary,
  LAG(salary) OVER (PARTITION BY department_id ORDER BY salary) as prev_salary,
  salary - LAG(salary) OVER (PARTITION BY department_id ORDER BY salary) as diff
FROM employees;

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

Важно: SELF JOIN может быть ресурсоемким на больших таблицах. Рекомендации:

• Убедитесь, что индексы созданы на столбцах join условия
• Используйте EXPLAIN ANALYZE для проверки плана выполнения
• Для больших наборов данных рассмотрите Window Functions или CTE

SELF JOIN полезен для иерархических структур, поиска дубликатов и сравнения записей внутри одной таблицы.