Урок 5.7 · Время чтения: ~5 мин

SELF JOIN в SQL соединяет таблицу с самой собой: одна и та же таблица дважды появляется в запросе под двумя разными псевдонимами. В этом уроке вы узнаете, почему псевдонимы обязательны, как найти пары строк с общим значением без повторов и как self join читает иерархию «сотрудник — руководитель».

SQL SELF JOIN: соединение таблицы с самой собой

Иногда строки, которые нужно связать, находятся в одной таблице: сотрудник и его руководитель, два актёра с одинаковой фамилией, рейс и стыковочный рейс. Специального ключевого слова SELF JOIN нет. Это обычный INNER JOIN или LEFT JOIN, в котором обе стороны — одна и та же таблица.

Псевдонимы обязательны

База данных должна понимать, из какой копии таблицы взят каждый столбец, поэтому каждой копии нужен свой псевдоним:

SELECT a1.column, a2.column
FROM some_table AS a1
INNER JOIN some_table AS a2 ON a1.some_column = a2.some_column;

Представляйте a1 и a2 как две отдельные таблицы с одинаковым содержимым.

Поиск пар строк

У каких актёров одинаковая фамилия?

SELECT a1.first_name AS first_actor,
       a2.first_name AS second_actor,
       a1.last_name
FROM actor AS a1
INNER JOIN actor AS a2 ON a2.last_name = a1.last_name
                      AND a2.actor_id > a1.actor_id
ORDER BY a1.last_name, first_actor, second_actor;

Результат: 108 пар актёров с одинаковой фамилией, каждая пара по одному разу.

Условие a2.actor_id > a1.actor_id выполняет две задачи:

• исключает совпадение актёра с самим собой, ведь у каждого актёра фамилия совпадает с его собственной;
• оставляет только одну из двух зеркальных пар: с <> вы получили бы и «CHRISTIAN–DEBBIE AKROYD», и «DEBBIE–CHRISTIAN AKROYD».

Сравнение строк

Self join может сравнивать строку с другими строками той же таблицы. Фильмы того же рейтинга, что и «ACADEMY DINOSAUR», но длиннее его:

SELECT f2.title, f2.rating, f2.length
FROM film AS f1
INNER JOIN film AS f2 ON f2.rating = f1.rating
                     AND f2.length > f1.length
WHERE f1.title = 'ACADEMY DINOSAUR'
ORDER BY f2.length DESC
LIMIT 10;

Результат: 10 самых длинных фильмов с рейтингом PG, которые длиннее «ACADEMY DINOSAUR» (86 минут).

Иерархии: сотрудники и руководители

Классический self join — иерархия в одной таблице: в строке каждого сотрудника есть manager_id, указывающий на другую строку той же таблицы. В Sakila такой таблицы нет, поэтому покажем шаблон:

SELECT e.name AS employee,
       m.name AS manager
FROM employee AS e
LEFT JOIN employee AS m ON m.employee_id = e.manager_id;

• e — копия в роли сотрудников, m — в роли руководителей.
• LEFT JOIN сохраняет главного руководителя, у которого manager_id равен NULL; с INNER JOIN эта строка пропала бы.

Один self join поднимается на один уровень вверх. Чтобы пройти всю иерархию на любую глубину, вы будете использовать рекурсивные CTE в уроке 6.5.

Ключевые выводы:

• Self join — обычное соединение, в котором обе стороны — одна и та же таблица.
• Каждой копии таблицы нужен свой псевдоним.
• Используйте a1.id < a2.id, чтобы исключить совпадение строки с собой и зеркальные повторные пары.
• Для иерархий соединяйте manager_id с ключом той же таблицы; LEFT JOIN сохраняет верхний уровень.

Часто задаваемые вопросы

Что такое self join в SQL?

Соединение таблицы с самой собой. Таблица указывается дважды с разными псевдонимами, а условие соединения связывает строки одной копии со строками другой, например сотрудника с его руководителем.

Как избежать повторяющихся пар в self join?

Сравнивайте ключи через <, а не <>: a1.id < a2.id. Это исключает совпадение строки с собой и оставляет только одну из двух зеркальных пар.

SELF JOIN — это отдельное ключевое слово SQL?

Нет. Self join записывается через INNER JOIN или LEFT JOIN; разница лишь в том, что обе таблицы в соединении — одна и та же.