Полное внешнее соединение SQL Server

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

Введение в полное внешнее соединение SQL Server

FULL OUTER JOIN — это предложение оператора SELECT. Предложение FULL OUTER JOIN возвращает результирующий набор, включающий строки из левой и правой таблиц.

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

Ниже показан синтаксис предложения FULL OUTER JOIN при объединении двух таблиц T1 и T2:

SELECT 
    select_list
FROM 
    T1
FULL OUTER JOIN T2 ON join_predicate;

Ключевое слово OUTER является необязательным, поэтому его можно пропустить, как показано в следующем запросе:

SELECT 
    select_list
FROM 
    T1
FULL JOIN T2 ON join_predicate;

В этом синтаксисе:

Сначала укажите левую таблицу T1 в предложении FROM.
Во-вторых, укажите правильную таблицу T2 и предикат соединения.

Следующая диаграмма Венна иллюстрирует ПОЛНОЕ ВНЕШНЕЕ ОБЪЕДИНЕНИЕ двух наборов результатов:

Пример полного внешнего соединения SQL Server

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

Сначала создайте новую схему с именем pm, что означает управление проектами:

CREATE SCHEMA pm;
GO

Далее создайте новые таблицы с именами projects и members в схеме pm:

CREATE TABLE pm.projects(
    id INT PRIMARY KEY IDENTITY,
    title VARCHAR(255) NOT NULL
);
CREATE TABLE pm.members(
    id INT PRIMARY KEY IDENTITY,
    name VARCHAR(120) NOT NULL,
    project_id INT,
    FOREIGN KEY(project_id) 
        REFERENCES pm.projects(id)
);

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

Затем вставьте несколько строк в таблицы проектов и участников:

INSERT INTO 
    pm.projects(title)
VALUES
   ('New CRM for Project Sales'),
   ('ERP Implementation'),
   ('Develop Mobile Sales Platform');
INSERT INTO
    pm.members(name, project_id)
VALUES
   ('John Doe', 1),
   ('Lily Bush', 1),
   ('Jane Doe', 2),
   ('Jack Daniel', null);

После этого запросите данные из таблиц проектов и участников:

SELECT * FROM pm.projects;

SELECT * FROM pm.members;

Наконец, используйте FULL OUTER JOIN для запроса данных из таблиц проектов и участников:

SELECT 
    m.name member, 
    p.title project
FROM 
    pm.members m
    FULL OUTER JOIN pm.projects p 
        ON p.id = m.project_id;

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

В этом примере запрос вернул участников, которые участвуют в проектах, участников, которые не участвуют ни в одном проекте, и проекты, в которых нет ни одного участника.

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

SELECT 
    m.name member, 
    p.title project
FROM 
    pm.members m
    FULL OUTER JOIN pm.projects p 
        ON p.id = m.project_id
WHERE
    m.id IS NULL OR
    P.id IS NULL;

На следующем рисунке показан результат:

Как ясно видно из выходных данных, Jack Daniel не участвует ни в одном проекте, а Develop Mobile Sales Platform не имеет участников.

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