Урок 5.7: SELF JOIN - Соединение таблицы с самой собой

SELF JOIN — не является командой SQL, это просто общепринятый термин для ситуации, когда таблица соединяется с самой собой. На практике такое соединение строится с помощью обычных видов JOIN — чаще всего INNER JOIN или LEFT JOIN, в зависимости от требуемой логики. Это полезно для запросов к иерархическим данным или для сравнения строк внутри одной и той же таблицы.

Что такое SELF JOIN?

Чтобы выполнить самосоединение, вы должны обращаться к одной таблице так, словно это две разные таблицы. Для этого обязательно нужно использовать псевдонимы (aliases), чтобы дать каждому экземпляру таблицы уникальное имя. Без псевдонимов база данных не поймет, к какому экземпляру относится тот или иной столбец.

Визуализация (иерархия сотрудников): Представьте таблицу employee, где у каждой строки есть поле manager_id, которое указывает на employee_id их начальника.

   Таблица A (Сотрудники)        Таблица B (Менеджеры)
   +----+-------+---------+     +----+-------+
   | id | имя   | mgr_id  |     | id | имя   |
   +----+-------+---------+     +----+-------+
   | 1  | Алиса | NULL    |     | 1  | Алиса |
   | 2  | Боб   | 1       | <-> | 1  | Алиса | (Начальник Боба — Алиса)
   | 3  | Кэрол | 1       | <-> | 1  | Алиса | (Начальник Кэрол — Алиса)
   +----+-------+---------+     +----+-------+

Синтаксис SELF JOIN

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

• employee AS e: Первый экземпляр (представляет сотрудников).
• employee AS m: Второй экземпляр (представляет менеджеров).
• ON e.manager_id = m.id: Условие, которое их связывает.

Практические примеры (база данных Sakila)

1. Поиск фильмов одинаковой продолжительности

Предположим, мы хотим найти пары фильмов, у которых абсолютно одинаковая длительность (length). Мы можем соединить таблицу film саму с собой.

SELECT
    f1.title AS film_1,
    f2.title AS film_2,
    f1.length
FROM
    film AS f1
INNER JOIN
    film AS f2 ON f1.length = f2.length
WHERE
    f1.film_id <> f2.film_id -- Убеждаемся, что мы не сравниваем фильм сам с собой
LIMIT 10;

Условие f1.film_id <> f2.film_id критически важно. Без него каждый фильм совпал бы сам с собой (так как его длительность равна самой себе).

2. Поиск клиентов из одного города

Если мы хотим увидеть, какие клиенты живут по одному адресу (на основе address_id в этом упрощенном примере):

SELECT
    c1.first_name AS cust_1_first,
    c1.last_name AS cust_1_last,
    c2.first_name AS cust_2_first,
    c2.last_name AS cust_2_last,
    c1.address_id
FROM
    customer AS c1
INNER JOIN
    customer AS c2 ON c1.address_id = c2.address_id
WHERE
    c1.customer_id < c2.customer_id; -- Используем '<' вместо '<>', чтобы избежать дубликатов пар (A-B и B-A)

Ключевые выводы урока

• SELF JOIN — это термин для соединения таблицы с самой собой, а не отдельное ключевое слово SQL.
• Такое соединение обычно реализуется через обычные виды JOIN, например INNER JOIN или LEFT JOIN.
• Псевдонимы таблиц обязательны для различения двух экземпляров таблицы.
• Используйте условия ON для определения связей между строками (например, иерархия или общие атрибуты).
• Используйте условия фильтрации, такие как id1 <> id2 или id1 < id2, чтобы избежать сопоставления строки самой с собой или повторения одних и тех же пар. В случае LEFT JOIN часть такой логики может размещаться не только в WHERE, но и в условии ON.