Урок 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.

Продолжить практику

Войти

Регистрация