SQL OUTER JOIN

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

Наиболее распространенным типом JOIN в реляционных моделях баз данных является SQL INNER JOIN. На практике пользователи используют INNER JOIN, например, если необходимо соединить две таблицы базы данных, используя одни и те же столбцы. Каждая запись одной таблицы объединяется с соответствующей записью другой таблицы. Записи данных, которым система управления базой данных (СУБД) не может найти соответствие в другой таблице, остаются скрытыми.

SQL OUTER JOIN, с другой стороны, выводит не только записи данных обеих таблиц, которые удовлетворяют условию отбора (например, равенство значений двух столбцов), но и все остальные кортежи одной или другой таблицы.

С учетом направления чтения в синтаксисе SQL существует левая и правая таблица. Соответствующие операции называются LEFT OUTER JOIN и RIGHT OUTER JOIN. Если в запросах к базе данных помимо записей данных, удовлетворяющих условию отбора, необходимо вывести все записи данных в левой и правой таблицах, то это FULL OUTER JOIN.

Принцип работы различных типов JOIN можно хорошо проиллюстрировать с помощью количественных диаграмм:

Совет

Ключевое слово OUTER является необязательным в синтаксисе SQL. Пользователи обычно используют сокращенное обозначение LEFT JOIN, RIGHT JOIN и FULL JOIN.

Мы проиллюстрируем функциональность OUTER JOIN на примере таблиц базы данных «сотрудники» и «автомобили».

Таблица: сотрудник

e_id фамилия имя идентификатор автомобиля
1 Шмидт Джек 3
2 Мюллер Блейн 1
3 МакКлейн Уокер 1
4 Кон Грег 2
5 Смит Маршалл NULL

Таблица employees содержит имена и фамилии сотрудников вымышленной компании и идентификатор закрепленного за компанией автомобиля (vehicle_id). Первичным ключом таблицы является последовательный идентификатор сотрудника (employee_id). Сотрудник с идентификатором 5 (Маршалл Смит) еще не получил служебный автомобиль. Поэтому ячейка в соответствующем столбце содержит нулевое значение.

Примечание

Нулевое значение NULL означает отсутствие значения. Оно не соответствует числовому значению 0.

Таблица: автомобиль

идентификатор автомобиля марка модель регистрация год Государственный осмотр
1 VW Caddy B KH 778 2016 12.18.2018
2 Opel Астра B PO 654 2010 08.12.2019
3 BMW X6 B MW 780 2017 09.01.2018
4 Porsche Boxster B AA 123 2018 12.23.2020

Таблица «автомобили» содержит информацию об автомобилях компании: марка, модель, регистрационный номер, год выпуска и дата следующего государственного осмотра. Каждому автомобилю компании присваивается последовательный идентификатор (vehicle_id), который выступает в качестве первичного ключа таблицы.

Обе таблицы связаны отношением внешнего ключа. Первичный ключ таблицы «автомобили» (vehicle_id) был интегрирован в таблицу «сотрудники» в качестве внешнего ключа. Это позволяет нам связать обе таблицы через общий столбец.

Примечание

Хотя действительные первичные ключи не должны содержать нулевых значений, нулевые значения во внешних ключах не нарушают целостность набора данных.

SQL ЛЕВОЕ ВНЕШНЕЕ ОБЪЕДИНЕНИЕ

При левостороннем внешнем соединении таблица, находящаяся слева от оператора соединения, является доминирующей таблицей. В реляционной алгебре LEFT OUTER JOIN обозначаются следующим оператором: ⟕.

Чтобы связать таблицы «сотрудник» и «автомобиль» в рамках LEFT OUTER JOIN, можно использовать следующую операцию:

сотрудник ⟕ vehicle_id=vehicle_idавтомобиль

Взаимодействие с СУБД происходит на языке баз данных SQL. Приведенной выше формуле соответствует следующий оператор SQL:

SELECT * FROM employee LEFT JOIN vehicle ON employee.vehicle_id = vehicle.vehicle_id;

Таблица «сотрудники» находится слева от оператора JOIN, таблица «автомобиль» — справа. В качестве условий отбора мы выбираем employee.vehicle_id=vehicle.vehicle_id. Набор результатов LEFT OUTER JOIN включает все записи данных из левой таблицы и те записи данных из правой таблицы, которые удовлетворяют условию JOIN. Это означает, что в набор результатов JOIN включаются только те записи данных из таблицы «vehicle», которые содержат значение в столбце vehicle_id, и для которых СУБД может найти соответствующее значение в таблице «employee».

Отсутствующие значения в таблице результатов выводятся как нулевые значения.

Примечание

Обратите внимание, что порядок таблиц в операторе SQL отличается от порядка таблиц в INNER JOIN. При LEFT JOIN все записи данных таблицы слева от оператора JOIN выводятся полностью, при RIGHT JOIN — все записи данных таблицы справа от оператора JOIN.

В результате выполнения LEFT OUTER JOIN мы получим следующую таблицу:

Таблица: LEFT OUTER JOIN через таблицы «employee» и «vehicle».

e_id фамилия имя сотрудник.vehicle_id vehicle.vehicle_id Марка Модель Регистрация Год Государственный осмотр
1 Шмидт Джек 3 3 BMW X6 B MW 780 2017 09.01.2018
2 Мюллер Блейн 1 1 VW Caddy B KH 778 2016 12.18.2018
3 МакКлейн Уокер 1 1 ФОЛЬКСВАГЕН Caddy B KH 778 2016 12.18.2018
4 Кон Грег 2 2 Opel Astra B PO 654 2010 08.12.2019
5 Смит Mashall NULL NULL NULL NULL NULL NULL NULL

Таблица результатов имеет две особенности:

Запись данных для vehicle_id 4 (Porsche Boxer) в таблице «vehicle» не появляется в результатах. Причина в том, что в таблице «Сотрудники» нет подходящего внешнего ключа для первичного ключа со значением 4. Условие отбора не выполняется. Запись данных поступает из правильной выходной таблицы и поэтому скрыта.

Внешний ключ vehicle_id в исходной таблице «employees» содержит нулевое значение для записи данных для сотрудника Маршалла Смита. Поэтому в таблице «vehicle» не может быть найден соответствующий первичный ключ. Условие отбора здесь также не выполняется. Однако, поскольку запись данных происходит из левой выходной таблицы, она все равно включается в таблицу результатов для LEFT JOIN. Недостающие значения в кортеже таблицы результатов устанавливаются в NULL.

SQL ПРАВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ

RIGHT OUTER JOIN следует тому же принципу, что и LEFT OUTER JOIN, но доминирующей таблицей здесь является не левая, а правая.

В набор результатов соединения RIGHT OUTER JOIN входят все кортежи таблицы, расположенной справа от оператора JOIN, и кортежи левой таблицы, удовлетворяющие условию JOIN. В качестве оператора используется следующий символ: ⟖.

Начнем снова с выходных таблиц «сотрудники» и «автомобили» и определим такое же условие отбора для ПРЯМОГО соединения, как и в примере с ЛЕВЫМ соединением.

Реляционная алгебра:

сотрудник ⟖ vehicle_id=vehicle_idавтомобиль

SQL-выражение:

SELECT * FROM employee RIGHT JOIN vehicle ON employee.vehicle_id = vehicle.vehicle_id; 

Таблица результатов для ПРЯМОГО соединения существенно отличается от ЛЕВОГО соединения.

Таблица: RIGHT OUTER JOIN через таблицы «employee» и «vehicle».

e_id фамилия имя сотрудник.vehicle_id vehicle.vehicle_id марка модель регистрация год Государственный осмотр
1 Шмидт Джек 3 3 BMW X6 B MW 780 2017 09.01.2018
2 Мюллер Блейн 1 1 VW Caddy B KH 778 2016 12.18.2018
3 МакКлейн Уокер 1 1 ФОЛЬКСВАГЕН Caddy B KH 778 2016 12.18.2018
4 Кон Грег 2 2 Opel Astra B PO 654 2010 08.12.2019
NULL NULL NULL 4 4 Porsche Boxster B AA 123 2018 12.23.2020

Запись данных для сотрудника Маршалла Смита не содержится в таблице результатов. Причина этого: vehicle_id набора данных соответствует значению NULL и поэтому не может быть присвоен набору данных в правой части таблицы.

В результате соединения RIGHT JOIN мы получаем все записи данных из таблицы «vehicle» — а также запись данных с vehicle_id 4, которой не был присвоен «кортеж» сотрудников.

SQL ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ

ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ — это комбинация ЛЕВОГО ВНЕШНЕГО СОЕДИНЕНИЯ и ПРЯМОГО ВНЕШНЕГО СОЕДИНЕНИЯ. Для этого процесса реляционная алгебра определяет следующий оператор: ⟗.

Мы также проиллюстрируем FULL JOIN в выходных таблицах «сотрудники» и «автомобили» и примем те же условия отбора, что и раньше.

Реляционная алгебра:

Сотрудник ⟗ vehicle_id=vehicle_idавтомобиль

SQL-выражение:

SELECT * FROM employee FULL JOIN vehicle ON employee.vehicle_id = vehicle.vehicle_id;

Результат соответствует следующей таблице:

Таблица: FULL OUTER JOIN для таблиц «employees» и «vehicles».

e_id фамилия имя сотрудник.vehicle_id автомобиль.vehicle_id марка модель Регистрация Год Государственный осмотр
1 Шмидт Джек 3 3 BMW X6 B MW 780 2017 09.01.2018
2 Мюллер Блейн 1 1 VW Caddy B KH 778 2016 12.18.2018
3 МакКлейн Уокер 1 1 ФОЛЬКСВАГЕН Caddy B KH 778 2016 12.18.2018
4 Кон Грег 2 2 Opel Astra B PO 654 2010 08.12.2019
5 Маршалл Смит NULL NULL NULL NULL NULL NULL NULL
NULL NULL NULL 4 4 Porsche Boxster B AA 123 2018 12.23.2020

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

Пропущенные значения также устанавливаются в NULL для ПОЛНОГО ДЖОИН.

Примечание

FULL OUTER JOIN имеют небольшое практическое значение и не поддерживаются ведущими системами управления базами данных, такими как MySQL и MariaDB.

ЕСТЕСТВЕННОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ

Как и INNER JOIN, OUTER JOIN также могут быть реализованы как NATURAL JOIN. Соответствующими операторами являются:

LEFT/RIGHT JOIN … USING

Or:

NATURAL LEFT/RIGHT JOIN

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

SELECT * FROM employee LEFT JOIN vehicle USING (vehicle_id);

В качестве альтернативы можно использовать краткую нотацию, при которой СУБД автоматически ищет столбцы с одинаковыми именами и соединяет через них перечисленные таблицы:

SELECT * FROM employee NATURAL LEFT JOIN sections;

Применительно к приведенным выше таблицам оба оператора SQL приводят к одному и тому же результату.

e_id псевдоним фамилия vehicle_id Марка Модель Регистрация Год Государственный осмотр
1 Шмидт Джек 3 BMW X6 B MW 780 2017 09.01.2018
2 Мюллер Блейн 1 VW Caddy B KH 778 2016 12.18.2018
3 МакКлейн Уокер 1 VW Кэдди B KH 778 2016 12.18.2018
4 Кон Грег 2 Opel Астра B PO 654 2010 08.12.2019
5 Смит Маршалл NULL NULL NULL NULL NULL NULL

В NATURAL LEFT JOIN столбцы employee.vehicle_id и vehicle.vehicle_id объединяются в общий столбец vehicle_id.

Внешние соединения на практике

OUTER JOINS обычно приводят к группам таблиц с нулевыми значениями. Это полезно, например, если вы хотите их выделить. В нашем примере Porsche Boxster еще не был закреплен за сотрудником. Это не отображается в таблице «транспортное средство». А таблица «сотрудники» просто показывает, что Маршалл Смит еще не пользовался служебным автомобилем. После полного соединения обеих таблиц легко увидеть, что Маршалл может водить Porsche в будущем.

Оцените статью
cdelat.ru
Добавить комментарий