Что такое SELF JOIN в SQL и для чего он используется?

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

Типичный пример: Таблица employees с ссылкой на менеджера.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT REFERENCES employees(id)
);
-- SELF JOIN для получения сотрудников и их менеджеров
SELECT 
    emp.name AS employee_name,
    mgr.name AS manager_name
FROM employees emp
LEFT JOIN employees mgr ON emp.manager_id = mgr.id;

Ключевые аспекты SELF JOIN:

1. Обязательное использование алиасов: Разные алиасы (emp, mgr) необходимы, чтобы различать два "экземпляра" одной таблицы.
2. Типы JOIN: Можно использовать INNER JOIN, LEFT JOIN, RIGHT JOIN или FULL JOIN в зависимости от задачи.
3. Основные сценарии использования:
   • Иерархические данные (сотрудники-менеджеры, категории товаров).
   • Поиск пар строк, удовлетворяющих условию (например, найди сотрудников с одинаковой зарплатой: SELECT a.name, b.name FROM employees a JOIN employees b ON a.salary = b.salary AND a.id < b.id).
   • Вычисление на основе сравнения строк (например, найти сотрудников, которые зарабатывают больше своего менеджера).