SQL JOIN — запросы по нескольким таблицам данных

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

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

Мы познакомим вас с математическим принципом работы SQL JOIN, сравним различные типы JOIN и на практических примерах покажем, как реализовать JOIN в контексте запросов к базе данных через SQL.

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

• декартово произведение
• Выборка

Декартово произведение (также называемое перекрестным произведением) — это операция теории множеств, с помощью которой два или более множеств соединяются вместе. В реляционной модели баз данных декартово произведение используется для соединения кортежных множеств в виде таблиц. Результатом этой операции является набор упорядоченных кортежей, где каждый кортеж состоит из одного элемента каждого исходного набора.

В качестве оператора декартова произведения в реляционной алгебре используется знак умножения (x).

Приведем пример:

Декартово произведение A × B обоих множеств A = {x, y, z} и B = {1, 2, 3} имеет вид:

A × B = {(x,1), (x,2), (x,3), (y,1), (y,2), (y,3), (z,1), (z,2), (z,3)}.

Расчет можно проиллюстрировать с помощью следующего графика:

Обратите внимание на последовательность формирования пар. Например, декартово произведение A × B не соответствует тому же количеству, что и декартово произведение B × A.

A × B = {(x,1), (x,2), (x,3), (y,1), (y,2), (y,3), (z,1), (z,2), (z,3)}.

B × A = {(1,x), (1,y), (1,z), (2,x), (2,y), (2,z), (3,x), (3,y), (3,z)}.

В терминологии SQL операция, в которой декартово произведение формируется из двух таблиц базы данных, называется «перекрестным JOIN». На практике перекрестные JOIN редко используются из-за нефильтрованного набора результатов.

Выборка — это операция реляционной алгебры, которая позволяет выбрать определенные кортежи в исходном наборе и вывести их в качестве результирующего набора. Какие кортежи входят в результирующий набор, можно определить с помощью выражения сравнения. Результатом выбора является набор кортежей, удовлетворяющих условию выбора, заданному в выражении сравнения. В качестве оператора используется греческая буква сигма (σ). Операция обозначается следующим образом:

σ_F (R)

Местоимение «F» соответствует реляционному выражению — формуле логических предикатов, определяющих условия отбора. R означает набор данных, подлежащий отбору. В качестве альтернативы можно использовать линейную нотацию R[F].

Для формулировки условий отбора доступны обычные операторы сравнения: например, равно (=), больше (>) или меньше (<).

Мы объясним выборку на примере, который мы уже приводили в основном тексте о реляционной модели базы данных. В следующей таблице представлены фиктивные данные о персонале, которые компания могла бы ввести для своих сотрудников. Для каждого сотрудника указывается номер персонала (e_id), имя (фамилия, имя), номер социального страхования (ssn), адрес (улица, номер, почтовый индекс) и закрепленный за компанией автомобиль (ID автомобиля компании).

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

Если мы хотим выбрать таблицу сотрудников так, чтобы отображались только те сотрудники, которые управляют автомобилем с идентификатором vehicle_id 1, мы можем поступить следующим образом.

σ_{vehicle_id=1}(сотрудник)

Мы получим только те кортежи, в которых значение в столбце vehicle_id равно 1.

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

Таблица: сотрудник (выбран)

В языке баз данных SQL условия отбора определяются с помощью команды «WHERE».

Если кортеж удовлетворяет условию vehicle_id=1, то для этого кортежа должны быть показаны значения всех столбцов.

Все распространенные типы JOIN объединяют декартово произведение с условием отбора. Чтобы объяснить этот вид работы с базой данных, мы для наглядности сократим таблицу «сотрудники» до четырех столбцов. Кроме того, мы введем таблицу «автомобили», где хранится подробная информация об автопарке компании.

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

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

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

Если вы хотите объединить две таблицы и одновременно выбрать соответствующие кортежи, вы комбинируете ранее введенные операции базы данных:

σ_{vehicle_id=vehicle_id}(сотрудник × автомобиль)

Сначала формируется декартово произведение сотрудник × автомобиль. Результатом (промежуточным) является перекрестный JOIN — таблица результатов, в которой каждый кортеж из таблицы «сотрудники» объединяется с каждым кортежем из таблицы «транспортное средство».

Таблица: Декартово произведение «сотрудник» × «автомобиль»

Затем выбираются только те кортежи, которые показывают, что vehicle_id таблицы «vehicle» совпадает с vehicle_id таблицы «employees». Условием отбора является то, что внешний ключ таблицы «сотрудники» соответствует первичному ключу таблицы «транспортное средство».

(Конечным) результатом является таблица, которая объединяет обе выходные таблицы без излишеств.

Таблица: JOIN между «сотрудник» и «автомобиль»

Являясь комбинацией декартова произведения и последующего выбора, JOIN объединяет обе операции в одну. В качестве оператора используется символ бабочки (⋈).

Как показано здесь:

σ_{vehicle_id=vehicle_id}(сотрудник × автомобиль) := сотрудник⋈_{vehicle_id=vehicle_id}автомобиль

Операция σ_{vehicle_id=vehicle_id}(employee × vehicle)соответствует JOIN через таблицы «employee» и «vehicle» с условием vehicle_id=vehicle_id.

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

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

Различные типы SQL JOIN используются в реляционной модели базы данных, которая позволяет выполнять запросы, используя группу таблиц базы данных. Необходимым условием для этого является то, что выбранные таблицы связаны друг с другом с помощью отношений внешних ключей.

К наиболее важным типам JOIN относятся следующие:

• INNER JOINs: Внутренний JOIN — это фильтрованная форма перекрестного JOIN, в которой в результатах объединяются только те кортежи из обеих выходных таблиц, которые удовлетворяют условию отбора, заданному пользователем.
• внешние соединения (OUTER JOINs): Внешнее JOIN является расширением результата внутреннего JOIN и содержит кортежи обеих выходных таблиц, которые удовлетворяют условию отбора, определенному пользователем, а также все оставшиеся кортежи из первой таблицы, второй таблицы или обеих таблиц. Внешние JOIN реализуются как левый внешний JOIN, правый внешний JOIN или полный внешний JOIN.

Различия между внутренними JOIN и различными вариантами внешнего JOIN можно проиллюстрировать с помощью количественных диаграмм. Следующий график является наглядным представлением представленных типов JOIN:

Независимо от различия между внутренним JOIN и внешним JOIN, SQL JOIN также можно классифицировать по следующим типам JOIN:

• EQUI JOIN
• НЕЭКВИВАЛЕНТНОЕ СОЕДИНЕНИЕ
• САМОСТОЯТЕЛЬНОЕ СОЕДИНЕНИЕ

Внутренние JOIN и внешние JOIN могут быть реализованы как equi JOIN и non equi JOIN. Все примеры JOIN, представленные до сих пор, представляют собой equi JOIN. Экви JOIN характеризуются тем, что они допускают только знак равенства в качестве реляционного оператора (=).

Условием выбора равнозначного JOIN всегда является то, что значения столбцов должны быть равны.

Однако, в принципе, JOIN (как и выбор в реляционной алгебре) не ограничивается равенством столбцов. Возможными операторами сравнения являются:

Начиная с SQL-92, язык баз данных с ключевым словом USING предоставляет краткую форму для EQUI JOINS. Однако это предполагает, что соответствующие столбцы имеют одинаковые имена, что не обязательно должно быть так.

В следующем примере показаны два разных оператора SQL, которые приводят к одному и тому же результату. В первом операторе операция JOIN явно определена с помощью ключевого слова ON. Для второго оператора мы используем краткую нотацию с ключом USING.

С другой стороны, NON EQUI JOIN исключают операции, основанные на равенстве столбцов. Разрешены все операции сравнения, кроме знака равенства (=).

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

Если две таблицы связаны столбцами с одинаковым именем, то это называется NATURAL JOIN. NATURAL JOIN по умолчанию реализуется как INNER JOIN с использованием одноименного ключевого слова. Для NATURAL JOIN не устанавливается этот тип JOIN. Также возможны NATURAL LEFT OUTER JOIN или NATURAL RIGHT OUTER JOIN.

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