Артём Санников

Что такое SELF JOIN?

SELF JOIN — это запрос, который предназначен для самообъединения таблицы, то есть таблица соединяется сама с собой.

Синтаксис запроса SELF JOIN

Для формирования запроса SELF JOIN нужно указать одну и ту же таблицу дважды, но присвоить им разные псевдонимы (алиасы).

Запрос SELF JOIN основан на работе оператора INNER JOIN, также можно использовать LEFT JOIN или RIGHT JOIN. В блоке ON указываются условия для объединения таблиц.

SELECT список_столбцов
FROM таблица_1 AS псевдоним_1
[INNER JOIN|LEFT JOIN|RIGHT JOIN] таблица_1 AS псевдоним_2
ON условие_для_объединения;

Синтетические данные для работы

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

Основываясь на представленной выше структуре организации создаём таблицу list_users и заполняем её данными.

CREATE TABLE list_users (
	id integer,
	last_name varchar(30),
	first_name varchar(30),
	boss_id integer
);

INSERT INTO list_users(id, last_name, first_name, boss_id)
VALUES (1, 'Алешина', 'Варвара', null),
	   (2, 'Селиванова', 'Милана', 1),
	   (3, 'Александрова', 'Анна', 1),
	   (4, 'Нестерова', 'Анна', 2),
	   (5, 'Воронин', 'Артём', 2),
	   (6, 'Гусев', 'Всеволод', 3),
	   (7, 'Потапова', 'Елизавета', 3),
	   (8, 'Лукин', 'Артемий', 4);
COMMIT;

Практический пример

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

SELECT t1.id,
	   t1.last_name,
	   t1.first_name,
	   t2.last_name||' '||t2.first_name AS name_boss
FROM list_users AS t1
INNER JOIN list_users AS t2
ON t2.id = t1.boss_id
ORDER BY t1.id ASC;

Этот запрос сформирован при помощи оператора INNER JOIN и дважды ссылается на таблицу list_users, сначала для поиска сотрудника, а затем для поиска его руководителя. Псевдоним t1 используется для сотрудника, а t2 для руководителя.

В блоке ON указывается условие для самообъединения таблицы list_users и означает, что будут выведены только те записи, которые соответствуют условию id_руководителя = id_руководителя_сотрудника.

Обратите внимание, что руководитель компании с id = 1 отсутствует в списке, так как для него не нашлось пары.

Чтобы вывести в результате запроса руководителя с id = 1, необходимо заменить в запросе INNER JOIN на LEFT JOIN.

SELECT t1.id,
	   t1.last_name,
	   t1.first_name,
	   t2.last_name||' '||t2.first_name AS name_boss
FROM list_users AS t1
LEFT JOIN list_users AS t2
ON t2.id = t1.boss_id
ORDER BY t1.id ASC;

Теперь в результатах запроса присутствует руководитель компании.

Метки: INNER JOIN, PostgreSQL, SELF JOIN.