Самостоятельное присоединение к SQL Server

В этом руководстве вы узнаете, как использовать самосоединение SQL Server для присоединения таблицы к самой себе.

Синтаксис самостоятельного соединения SQL Server

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

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

Обратите внимание, что повторное обращение к одной и той же таблице в запросе без использования псевдонимов таблиц приведет к ошибке.

Ниже показан синтаксис присоединения таблицы T к самой себе:

SELECT
    select_list
FROM
    T t1
[INNER | LEFT]  JOIN T t2 ON
    join_predicate; 

Запрос дважды ссылается на таблицу T. Псевдонимы таблиц t1 и t2 используются для назначения таблице T разных имен в запросе.

Примеры самостоятельного соединения SQL Server

Давайте рассмотрим несколько примеров, чтобы понять, как работает самосоединение.

1) Использование самосоединения для запроса иерархических данных

Рассмотрим следующую таблицу персонала из образца базы данных :

Таблица staffs хранит информацию о сотрудниках, такую как идентификатор, имя, фамилия и адрес электронной почты. В ней также есть столбец manager_id, который указывает непосредственного менеджера. Например, Mireya подчиняется Fabiola, потому что значение manager_id Mireya — Fabiola.

У Фабиолы нет менеджера, поэтому столбец идентификатора менеджера имеет значение NULL.

Чтобы узнать, кто кому подчиняется, используйте самосоединение, как показано в следующем запросе:

SELECT
    e.first_name + ' ' + e.last_name employee,
    m.first_name + ' ' + m.last_name manager
FROM
    sales.staffs e
INNER JOIN sales.staffs m ON m.staff_id = e.manager_id
ORDER BY
    manager;

В этом примере мы ссылались на таблицу staffs дважды: один раз как e для сотрудников, а другие как m для менеджеров. Предикат соединения сопоставляет отношения между сотрудником и менеджером, используя значения в столбцах e.manager_id и m.staff_id.

В столбце employee нет Fabiola Jackson из-за эффекта INNER JOIN. Если заменить предложение INNER JOIN на предложение LEFT JOIN, как показано в следующем запросе, вы получите набор результатов, включающий Fabiola Jackson в столбце employee:

SELECT
    e.first_name + ' ' + e.last_name employee,
    m.first_name + ' ' + m.last_name manager
FROM
    sales.staffs e
LEFT JOIN sales.staffs m ON m.staff_id = e.manager_id
ORDER BY
    manager;

2) Использование самосоединения для сравнения строк в таблице

См. следующую таблицу клиентов:

В следующем операторе используется самосоединение для поиска клиентов, находящихся в одном городе.

SELECT
    c1.city,
    c1.first_name + ' ' + c1.last_name customer_1,
    c2.first_name + ' ' + c2.last_name customer_2
FROM
    sales.customers c1
INNER JOIN sales.customers c2 ON c1.customer_id > c2.customer_id
AND c1.city = c2.city
ORDER BY
    city,
    customer_1,
    customer_2;

Следующее условие гарантирует, что в операторе не сравнивается один и тот же клиент:

c1.customer_id > c2.customer_id

Следующее условие соответствует городу двух клиентов:

AND c1.city = c2.city

Обратите внимание, что если вы замените оператор «больше»(>) на оператор «не равно»(), вы получите больше строк:

SELECT
    c1.city,
    c1.first_name + ' ' + c1.last_name customer_1,
    c2.first_name + ' ' + c2.last_name customer_2
FROM
    sales.customers c1
INNER JOIN sales.customers c2 ON c1.customer_id  c2.customer_id
AND c1.city = c2.city
ORDER BY
    city,
    customer_1,
    customer_2;

Давайте рассмотрим разницу между > и в предложении ON, ограничившись одним городом, чтобы было легче сравнивать.

Следующий запрос возвращает клиентов, находящихся в Олбани:

SELECT 
   customer_id, first_name + ' ' + last_name c, 
   city
FROM 
   sales.customers
WHERE
   city = 'Albany'
ORDER BY 
   c;

В этом запросе используется оператор(>) в предложении ON:

SELECT
    c1.city,
    c1.first_name + ' ' + c1.last_name customer_1,
    c2.first_name + ' ' + c2.last_name customer_2
FROM
    sales.customers c1
INNER JOIN sales.customers c2 ON c1.customer_id > c2.customer_id
AND c1.city = c2.city
WHERE c1.city = 'Albany'
ORDER BY
    c1.city,
    customer_1,
    customer_2;

Вывод:

В этом запросе используется оператор() в предложении ON:

SELECT
    c1.city,
 c1.first_name + ' ' + c1.last_name customer_1,
    c2.first_name + ' ' + c2.last_name customer_2
FROM
    sales.customers c1
INNER JOIN sales.customers c2 ON c1.customer_id  c2.customer_id
AND c1.city = c2.city
WHERE c1.city = 'Albany'
ORDER BY
 c1.city,
    customer_1,
    customer_2;

Вот что получилось:

В этом руководстве вы узнали, как использовать самосоединение SQL Server для запроса иерархических данных и сравнения строк в одной таблице.