Само собой разумеется, что соединения — одна из наиболее узнаваемых особенностей реляционных баз данных. Объединения позволяют нам объединять данные из одной или нескольких таблиц на основе связанных условий для создания согласованной операции с данными.
В SQL существуют различные типы объединений, каждый из которых имеет уникальный способ обработки данных из участвующих таблиц или результирующего набора. Одним из наиболее распространенных типов соединений в SQL является ВНЕШНЕЕ СОЕДИНЕНИЕ.
ВНЕШНЕЕ СОЕДИНЕНИЕ в SQL извлекает все совпадающие строки из задействованных таблиц, а также несовпадающие строки из одной или обеих таблиц. Это удобно, когда вы имеете дело с таблицами, содержащими значения NULL или отсутствующие множества.
Давайте подробнее рассмотрим, что делают эти объединения, как они работают и как мы можем использовать их в базе данных SQL.
Требования:
В этом уроке мы будем работать с MySQL 8.0 и использовать образец базы данных Sakila. Однако вы можете свободно использовать любой другой набор данных, который вы сочтете применимым.
Типы внешних соединений
В SQL существует три основных типа ВНЕШНИХ СОЕДИНЕНИЙ. Эти типы ВНЕШНИХ СОЕДИНЕНИЙ включают в себя:
-
Левые ВНЕШНИЕ СОЕДИНЕНИЯ
-
Правые ВНЕШНИЕ СОЕДИНЕНИЯ
-
ПОЛНЫЕ ВНЕШНИЕ СОЕДИНЕНИЯ
В случае LEFT OUTER JOINS соединение извлекает все строки из левой таблицы и только соответствующие строки из правой таблицы. Если в правой таблице нет соответствующих строк, соединение возвращает значения NULL для столбцов в правой таблице.
Это похоже на RIGHT OUTER JOIN. Однако он извлекает все строки из правой таблицы и только соответствующие строки из левой таблицы. Если в левой таблице нет соответствующих строк, соединение включает значения NULL для столбцов левой таблицы.
Наконец, у нас есть ПОЛНЫЕ ВНЕШНИЕ СОЕДИНЕНИЯ. Этот тип соединения сочетает в себе как ПРАВОЕ, так и ЛЕВОЕ внешние соединения. В результате соединение извлекает все строки, если есть совпадения либо в левой, либо в правой таблице. Если совпадений нет, соединение возвращает значения NULL для столбцов таблицы, не имеющих совпадений.
Синтаксис SQL OUTER JOIN
Следующее описывает синтаксис SQL OUTER JOIN. Однако следует иметь в виду, что синтаксис может незначительно отличаться в зависимости от ядра целевой базы данных.
Ниже приведена общая структура:
ВЫБРАТЬ столбцыИЗ таблицы1
[СЛЕВА | ПРАВО | FULL] ВНЕШНЕЕ СОЕДИНЕНИЕ таблица2
ВКЛ таблица1.имя_столбца = таблица2.имя_столбца;
Синтаксис OUTER JOIN в SQL довольно понятен.
Примеры:
Давайте рассмотрим некоторые примеры использования различных типов OUTER JOINS в SQL.
Как мы уже упоминали, для демонстрации мы будем использовать образец базы данных Sakila. В данном случае мы используем таблицы «клиент» и «платеж».
Пример 1: ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ
Начнем с ВНЕШНЕГО СОЕДИНЕНИЯ. Предположим, мы хотим получить всю информацию о клиентах вместе с их платежной информацией, если она доступна.
Это делает применимым LEFT OUTER JOIN, поскольку нам нужна вся информация о клиенте (слева) и информация о платежах, если она доступна (справа).
Если клиент не произвел никакого платежа, в объединении будут показаны значения NULL для столбцов, связанных с платежами.
Пример следующий:
ВЫБИРАТЬc.customer_id,
c.first_name,
c.фамилия,
сумма,
p.pay_date
ОТ
клиент с
ЛЕВЫЙ ВНЕШНИЙ JOIN платеж p
НА
c.customer_id = p.customer_id;
В данный запрос мы включаем столбцы «customer_id», «first_name» и «last_name» из таблицы «customer». Мы также включаем сумму и «дата_платежа» из таблицы «платеж».
Затем мы выполняем ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ между таблицами «клиент» и «платеж» на основе «customer_id».
Это все клиенты (независимо от того, была ли произведена оплата или нет) вместе с их платежными реквизитами (если таковые имеются).
Пример вывода выглядит следующим образом:
Пример 2: ПРАВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ
Теперь перейдем к ПРАВОМУ ВНЕШНЕМУ СОЕДИНЕНИЮ. Предположим, в этом случае мы хотим включить всю платежную информацию и связанного с ней клиента, если таковой имеется.
В этом случае, если платеж осуществлен клиентом, в объединении будут отображены сведения об этом клиенте. Если есть платеж, не связанный с клиентом, в столбцах, связанных с клиентом, будут показаны значения NULL.
ВЫБИРАТЬc.customer_id,
c.first_name,
c.фамилия,
сумма,
p.pay_date
ОТ
клиент с
ПРАВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ платежа p
НА
c.customer_id = p.customer_id;
В результате набор выглядит следующим образом:
Пример 3: ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ
С другой стороны, FULL OUTER JOIN извлекает всю информацию о клиенте и платежах. Сюда входят все клиенты и все платежи, а в тех случаях, когда между таблицами нет совпадений, отображаются значения NULL.
ВЫБИРАТЬc.customer_id,
c.first_name,
c.фамилия,
сумма,
p.pay_date
ОТ
клиент с
ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ платежа p
НА
c.customer_id = p.customer_id;
Следует иметь в виду, что MySQL изначально не поддерживает FULL OUTER JOIN. Вам придется творить магию джиу-джитсу с помощью ЛЕВОГО СОЕДИНЕНИЯ, СОЕДИНЕНИЯ и ПРАВОГО СОЕДИНЕНИЯ. Мы могли бы добавить, что это довольно раздражает.
Заключение
В этом уроке мы узнали все о OUTER JOINS. Мы узнали, что такое ВНЕШНЕЕ СОЕДИНЕНИЕ в SQL, типы ВНЕШНИХ СОЕДИНЕНИЙ и примеры использования этих типов ВНЕШНИХ СОЕДИНЕНИЙ.