Теория: SELF JOIN (соединение таблицы с собой)

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

В этом уроке мы научимся решать такие задачи с помощью соединений с условием: INNER JOIN, LEFT JOIN, RIGHT JOIN или FULL JOIN. Такой прием называется SELF JOIN, но это именно название приема, а не инструкция языка SQL.

Применяем SELF JOIN при работе с графами

Допустим, в базе данных хранится информация о сотрудниках. Почти у каждого сотрудника есть его непосредственный руководитель. Получается иерархическая структура:

Эта структура — дерево, частный случай графа. Узлами являются сотрудники, а ребра указывают на то, кто кому подчиняется.

Информация о сотрудниках хранится в таблице employees:

employees

View on DB Fiddle

У каждого сотрудника в поле manager_id хранится ссылка на employee_id его непосредственного начальника. Такой способ используется для хранения иерархии между сотрудниками.

Наша задача — построить отчет, в котором для каждого сотрудника будет указан его непосредственный начальник.

Предположим, что у нас есть полная копия таблицы employees — таблица employeesCopy. У них одинаковые структура и данные. Тогда мы можем решить эту задачу с помощью соединения таблиц:

SELECT
    empl.employee_name,
    mngr.employee_name AS manager_name
FROM employees AS empl
LEFT JOIN employeescopy AS mngr ON
    empl.manager_id = mngr.employee_id

Разберем этот запрос:

• Из таблицы employees мы загружаем сотрудников, поэтому дали ей псевдоним empl.
• Из таблицы employeesCopy мы загружаем менеджеров, поэтому дали ей псевдоним mngr.
• Поскольку нам нужны все сотрудники, мы используем LEFT JOIN.
• Условие соединения гарантирует нам, что менеджер из mngr является непосредственным начальником сотрудника из empl: empl.manager_id = mngr.employee_id.

Результат выполнения запроса:

Мы решили задачу, предположив, что у нас есть полная копия таблицы employees. Но в этом нет необходимости. Вместо таблицы-копии мы можем использовать саму таблицу employees:

SELECT
    empl.employee_name,
    mngr.employee_name AS manager_name
FROM employees AS empl
LEFT JOIN employees AS mngr ON
    empl.manager_id = mngr.employee_id

View on DB Fiddle

В этом запросе таблица employees соединяется сама с собой. Этот прием называется SELF JOIN. Логически этот запрос работает так же, как если бы мы соединяли таблицу со своей полной копией.

В SELF JOIN запросах одна и та же таблица выполняет разные роли. Поэтому необходимо использовать псевдонимы для таблиц, чтобы явно разграничить эти роли.

В нашем запросе используется LEFT JOIN, но это не принципиально. Другие виды соединений также поддерживают соединение таблицы с самой собой.

Применяем SELF JOIN для поиска «дубликатов»

Допустим, в базе данных хранится информация об аккаунтах пользователей: имя аккаунта и адрес электронной почты. Мы хотим определить все случаи, когда на один и тот же адрес электронной почты зарегистрировано больше одного аккаунта.

Информация хранится в таблице users:

users

Переформулируем задачу в терминах, близких к базе данных: в таблице users необходимо найти разные строки с одинаковыми значениями поля email. Решим эту задачу с помощью INNER JOIN:

SELECT
    u1.nickname AS first_nickname,
    u2.nickname AS second_nickname,
    u1.email
FROM users AS u1
INNER JOIN users AS u2 ON
    u1.email = u2.email
    AND u1.user_id != u2.user_id

В этом запросе таблица users соединяется сама с собой. Условием соединения является выражение: u1.email = u2.email AND u1.user_id != u2.user_id. Благодаря ему в результат попадут только такие пары строк, у которых одинаковое значение email, но разные user_id.

Результат выполнения запроса:

View on DB Fiddle

Логически можно представить, что СУБД для каждой строки попыталась найти подходящую по условию соединения пару. Поэтому в результате две строки.

Выводы

SELF JOIN — это прием, когда выполняется соединение таблицы с самой собой. СУБД обрабатывает такие запросы так, будто мы соединяем две разные таблицы с одинаковой структурой и данными.

У SELF JOIN есть две популярные области применения:

• Когда строки внутри одной таблицы ссылаются друг на друга, то есть образуют граф
• Когда нужно выполнить поиск разных строк, у которых совпадает значение одного из полей