INNER JOIN: определение и применение

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

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

Здесь мы иллюстрируем INNER JOIN на примере двух таблиц. Таблица «employees» содержит сотрудников компании, включая идентификатор сотрудника (e_id) и соответствующий отдел (d_id).

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

В таблице показаны две особенности: сотрудники Мюллер и МакКлейн работают в одном отделе. Сотрудник Коннолли еще не был приписан к отделу.

В таблице «отделы» перечислены все отделы компании, включая идентификатор и местоположение.

Таблица: отделы

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

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

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

Подобный INNER JOIN обозначается в реляционной алгебре следующим образом:

employee⋈_{d_id=d_id}отделы

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

Команда SELECT предписывает СУБД запросить данные из базы данных. В качестве альтернативы SQL предлагает возможность ввода (INSERT INTO), изменения (UPDATE) или удаления (DELETE FROM) данных. За командой SELECT следует указание того, какие данные необходимо получить. Поскольку мы хотим получить полный набор данных, мы выбираем подходящий символ: звездочку (*).

Команда SELECT всегда требует ключевого слова FROM и указания, из какой таблицы или группы таблиц (JOIN) необходимо получить данные. В нашем случае источником данных является INNER JOIN через таблицы «отделы» и «сотрудники». Мы также указываем условие для связи с помощью ключевого слова ON. Мы просто хотим связать записи данных и вывести их в виде таблицы результатов, для которой «d_id» таблицы «сотрудники» соответствует «d_id» таблицы «отделы».

INNER JOIN через две выходные таблицы с условием «employee.d_id=departments.d_id» возвращает следующую таблицу результатов:

Таблица: SQL INNER JOIN между «employee» и «departments»

Если сравнить таблицу результатов с двумя выходными таблицами, то можно заметить, что в каждой таблице не хватает по одной записи данных: записи данных, значение которой в столбце «d_id» не имеет соответствия в другой таблице.

и

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

Если мы хотим обнаружить эти нарушения и сделать их видимыми в запросе, нам следует выбрать OUTER JOIN вместо INNER JOIN.

Внутренние соединения можно разделить на THETA JOINs, EQUI JOINs, NON EQUI JOINs и NATURAL JOINs.

INNER JOIN в терминологии SQL соответствует THETA JOIN в реляционной алгебре. THETA JOIN отличается от EQUI JOIN и NON EQUI JOIN тем, что предоставляет пользователям неограниченный набор операторов сравнения. EQUI JOIN, с другой стороны, ограничивают условие выбора для запросов равенством значений столбцов. Для NON EQUI JOIN разрешены все реляционные операторы, кроме знака равенства.

Если две таблицы (как в предыдущих примерах) соединены с помощью столбцов с одинаковыми именами, то INNER JOIN обычно преобразуются в NATURAL JOIN.

NATURAL JOIN являются подвидами EQUI JOIN. Как и EQUI JOIN, NATURAL JOIN также требует равенства значений двух столбцов в качестве условия отбора.

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

SQL-оператор дает указание СУБД связать перечисленные таблицы. Условие отбора создается с помощью ключевого слова USING, которое указывает, какие столбцы должны быть проверены на равенство. Необходимым условием является наличие столбца «d_id» в обеих таблицах. Записи данных из обеих таблиц включаются в результирующий набор только в том случае, если СУБД находит одинаковые значения в столбцах с пометкой «d_id».

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

Таблица: NATURAL JOIN между «employee» и «departments»

Вместо «идентификаторов отделов» обеих таблиц как «employees.d_id» и «departments.d_id», воспроизводится только один столбец «d_id».

NATURAL JOIN могут быть сокращены без клаузулы USING. Вместо этого используется оператор NATURAL JOIN. Краткая нотация приведенной выше операции соответствует следующему оператору SQL.

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