В этом руководстве вы узнаете о различных соединениях SQL Server, которые позволяют объединять данные из двух таблиц.
В реляционной базе данных данные распределены по нескольким логическим таблицам. Чтобы получить полный осмысленный набор данных, вам необходимо запросить данные из этих таблиц с помощью соединений. SQL Server поддерживает множество видов соединений, включая внутреннее соединение, левое соединение, правое соединение, полное внешнее соединение и перекрестное соединение. Каждый тип соединения определяет, как SQL Server использует данные из одной таблицы для выбора строк в другой таблице.
Давайте создадим образцы таблиц для демонстрации.
Настройка таблиц образцов
Сначала создайте новую схему с именем hr:
CREATE SCHEMA hr; GO
Во-вторых, создайте две новые таблицы с именами candidates и employees в схеме hr:
CREATE TABLE hr.candidates(
id INT PRIMARY KEY IDENTITY,
fullname VARCHAR(100) NOT NULL
);
CREATE TABLE hr.employees(
id INT PRIMARY KEY IDENTITY,
fullname VARCHAR(100) NOT NULL
);
В-третьих, вставьте несколько строк в таблицы кандидатов и сотрудников:
INSERT INTO
hr.candidates(fullname)
VALUES
('John Doe'),
('Lily Bush'),
('Peter Drucker'),
('Jane Doe');
INSERT INTO
hr.employees(fullname)
VALUES
('John Doe'),
('Jane Doe'),
('Michael Scott'),
('Jack Sparrow');
Назовем таблицу кандидатов левой таблицей, а таблицу сотрудников — правой таблицей.
Внутреннее соединение SQL Server
Внутреннее соединение создает набор данных, включающий строки из левой таблицы и соответствующие строки из правой таблицы.
В следующем примере используется предложение внутреннего соединения для получения строк из таблицы candidates, которые содержат соответствующие строки с такими же значениями в столбце fullname таблицы employees:
SELECT
c.id candidate_id,
c.fullname candidate_name,
e.id employee_id,
e.fullname employee_name
FROM
hr.candidates c
INNER JOIN hr.employees e
ON e.fullname = c.fullname;
Вот что получилось:
Следующая диаграмма Венна иллюстрирует результат внутреннего объединения двух результирующих наборов:
Левое соединение SQL Server
Left join выбирает данные, начиная с левой таблицы и соответствующие строки в правой таблице. Left join возвращает все строки из левой таблицы и соответствующие строки из правой таблицы. Если строка в левой таблице не имеет соответствующей строки в правой таблице, столбцы правой таблицы будут содержать пустые значения.
Левое соединение также известно как левое внешнее соединение. Ключевое слово outer необязательно.
Следующий оператор объединяет таблицу кандидатов с таблицей сотрудников с помощью левого соединения:
SELECT c.id candidate_id, c.fullname candidate_name, e.id employee_id, e.fullname employee_name FROM hr.candidates c LEFT JOIN hr.employees e ON e.fullname = c.fullname;
Вот что получилось:
Следующая диаграмма Венна иллюстрирует результат левого соединения двух результирующих наборов:
Чтобы получить строки, которые доступны только в левой таблице, но отсутствуют в правой, добавьте предложение WHERE к приведенному выше запросу:
SELECT
c.id candidate_id,
c.fullname candidate_name,
e.id employee_id,
e.fullname employee_name
FROM
hr.candidates c
LEFT JOIN hr.employees e
ON e.fullname = c.fullname
WHERE
e.id IS NULL;
На следующем рисунке показан результат:
Следующая диаграмма Венна иллюстрирует результат левого соединения, которое выбирает строки, доступные только в левой таблице:
Правое соединение SQL Server
Правое соединение или правое внешнее соединение выбирает данные, начиная с правой таблицы. Это обратная версия левого соединения.
Правое соединение возвращает результирующий набор, содержащий все строки из правой таблицы и соответствующие строки в левой таблице. Если строка в правой таблице не имеет соответствующей строки в левой таблице, все столбцы в левой таблице будут содержать пустые значения.
В следующем примере используется правильное соединение для запроса строк из таблиц кандидатов и сотрудников:
SELECT
c.id candidate_id,
c.fullname candidate_name,
e.id employee_id,
e.fullname employee_name
FROM
hr.candidates c
RIGHT JOIN hr.employees e
ON e.fullname = c.fullname;
Вот что получилось:
Обратите внимание, что все строки из правой таблицы(сотрудники) включены в результирующий набор.
Диаграмма Венна иллюстрирует правильное соединение двух наборов результатов:
Аналогично вы можете получить строки, доступные только в правой таблице, добавив предложение WHERE к приведенному выше запросу следующим образом:
SELECT
c.id candidate_id,
c.fullname candidate_name,
e.id employee_id,
e.fullname employee_name
FROM
hr.candidates c
RIGHT JOIN hr.employees e
ON e.fullname = c.fullname
WHERE
c.id IS NULL;
Вот что получилось:
И диаграмма Венна, иллюстрирующая эту операцию:
Полное соединение SQL Server
Полное внешнее соединение или полное соединение возвращает результирующий набор, содержащий все строки из левой и правой таблиц, с соответствующими строками с обеих сторон, где это возможно. В случае отсутствия соответствия отсутствующая сторона будет иметь значения NULL.
В следующем примере показано, как выполнить полное соединение таблиц кандидатов и сотрудников:
SELECT
c.id candidate_id,
c.fullname candidate_name,
e.id employee_id,
e.fullname employee_name
FROM
hr.candidates c
FULL JOIN hr.employees e
ON e.fullname = c.fullname;
Вот что получилось:
Диаграмма Венна, иллюстрирующая полное внешнее соединение:
Чтобы выбрать строки, которые существуют либо в левой, либо в правой таблице, исключите строки, общие для обеих таблиц, добавив предложение WHERE, как показано в следующем запросе:
SELECT
c.id candidate_id,
c.fullname candidate_name,
e.id employee_id,
e.fullname employee_name
FROM
hr.candidates c
FULL JOIN hr.employees e
ON e.fullname = c.fullname
WHERE
c.id IS NULL OR
e.id IS NULL;
Вот что получилось:
Диаграмма Венна иллюстрирует вышеописанную операцию:
В этом руководстве вы изучили различные соединения SQL Server, объединяющие данные из двух таблиц.
