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

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

Ключевой момент: При выполнении SELF JOIN необходимо использовать псевдонимы таблиц (алиасы), чтобы можно было ссылаться на разные "экземпляры" одной таблицы в одном запросе.

Классический пример: Иерархия сотрудников Предположим, есть таблица employees:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT NULL, -- Ссылается на id другого сотрудника в этой же таблице
    FOREIGN KEY (manager_id) REFERENCES employees(id)
);

Задача: Получить список всех сотрудников вместе с именами их менеджеров.

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 в Node.js приложениях:

1. Поиск пар или дубликатов: Найти пользователей, живущих в одном городе.

   SELECT a.name, b.name, a.city
   FROM users a
   INNER JOIN users b ON a.city = b.city AND a.id < b.id;

2. Анализ последовательностей событий: В таблице логов найти пары последовательных действий одного пользователя.

   SELECT prev.action AS previous_action, curr.action AS current_action
   FROM user_logs curr
   JOIN user_logs prev ON curr.user_id = prev.user_id
       AND curr.timestamp > prev.timestamp
       AND NOT EXISTS (
           SELECT 1 FROM user_logs mid
           WHERE mid.user_id = curr.user_id
           AND mid.timestamp > prev.timestamp
           AND mid.timestamp < curr.timestamp
       );

3. Организация древовидных структур (adjacency list): Как в примере с сотрудниками. Для работы с глубокими иерархиями в Node.js часто используют рекурсивные запросы (WITH RECURSIVE в PostgreSQL) или специализированные паттерны (Nested Sets, Materialized Path).