Соединение наборов данных (JOIN)

Соединения объединяют данные из двух источников (которые обычно называют “левый” и “правый”) в один набор данных. Соединение данных осуществляется для каждой строки и обычно включает в себя проверку условия соединения (join condition) для того, чтобы определить, какие строки должны быть объединены и оказаться в результирующем наборе данных.

Результат соединения также может быть соединён с другим набором данных с помощью следующего соединения.

Существует несколько типов (INNER, OUTER) и классов (именованные, натуральные, и др.) соединений, каждый из которых имеет свой синтаксис и правила.

Для примеров мы будем использовать две таблицы:

Таблица PROGRAMMER

Таблица LANG

Общий синтаксис использования операторов соединения нескольких источников можно представить в следующем виде:

SELECT ... FROM <source1> <join1> <source2> <join2> <source3> ...

где <source>:

• таблица
• представление
• селективная хранимая процедура, с возможными аргументами
• производный запрос
• общее табличное выражение

а <join>:

• [INNER] JOIN {ON condition | USING (column-list)}
• {LEFT | RIGHT | FULL} [OUTER] JOIN {ON condition | USING (column-list)}
• NATURAL [INNER] JOIN
• NATURAL {LEFT | RIGHT | FULL} [OUTER] JOIN
• CROSS JOIN

Рассмотрим виды соединений более подробно и приведем примеры.

Внутреннее соединение (INNER JOIN)

Данный тип соединяет только строки, которые удовлетворяет условию соединения. Порядок соединения таблиц неважен.

Самый очевидный, но наиболее понятный алгоритм работы такого соединения заключается в том, что каждая строка левого источника сопоставляется с каждой строкой правого источника и происходит проверка условия. Если условие выполняется, то строки соединяются и включаются в результирующий набор. Такой алгоритм универсален, но не оптимален и существует множество других реализаций такого типа соединения. Оптимизатор СУБД выбирает наиболее подходящий в зависимости от множества факторов. Какую бы реализацию не выбрал оптимизатор, результат всегда должен быть одинаков.

Например, запрос

SELECT * FROM PROGRAMMER P INNER JOIN LANG L ON P.LANG_ID = L.LANG_ID

Выдаст результат

Ключевое слово INNER является необязательным.

Обратите внимание, что для каждой записи возможны несколько подходящих строк из правого источника. Также обратите внимание, что поле LANG_ID фактические присутствует дважды: из левого источника и из правого. Если условие будет не простым равенством, значения в этих полях не обязательно будут совпадать.

Левое внешнее соединение (LEFT OUTER JOIN)

Оператор левого внешнего соединения LEFT OUTER JOIN соединяет две таблицы. Порядок таблиц для оператора важен.

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

Например, запрос

SELECT * FROM PROGRAMMER P LEFT OUTER JOIN LANG L ON P.LANG_ID = L.LANG_ID

Выдаст результат

Ключевое слово OUTER является необязательным.

Правое внешнее соединение (RIGHT OUTER JOIN)

Оператор правого внешнего соединения RIGHT OUTER JOIN соединяет две таблицы. Порядок таблиц для оператора важен.

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

Например, запрос

SELECT * FROM PROGRAMMER P RIGHT OUTER JOIN LANG L ON P.LANG_ID = L.LANG_ID

Выдаст результат

Ключевое слово OUTER является необязательным.

Полное внешнее соединение (FULL OUTER JOIN)

Оператор полного внешнего соединения FULL OUTER JOIN соединяет две таблицы. Порядок таблиц для оператора неважен.

В результат включается внутреннее соединение (INNER JOIN) левой и правой таблиц. Затем добавляются те строки левой таблицы, которые не вошли во внутреннее соединение. Для таких строк столбцы, соответствующие правой таблице, заполняются значениями NULL. Затем добавляются те строки правой таблицы, которые не вошли во внутреннее соединение. Для таких строк столбцы, соответствующие левой таблице, заполняются значениями NULL.

Например, запрос

SELECT * FROM PROGRAMMER P FULL OUTER JOIN LANG L ON P.LANG_ID = L.LANG_ID

Выдаст результат

Ключевое слово OUTER является необязательным.

Явные условия соединения

В синтаксисе явного соединения есть предложение ON, с условием соединения, в котором может быть указано любое логическое выражение, но, как правило, оно содержит условие сравнения между двумя участвующими источниками. Довольно часто, это условие — проверка на равенство (или ряд проверок на равенство объединённых оператором AND) использующая оператор “=”.

Такие соединения называются эквисоединениями. Рассмотренные выше соединения являлись таковыми.

Еще примеры соединений с явными условиями:

Выборка всех заказчиков из города Муром, которые сделали покупку.

SELECT * FROM customers c
  JOIN sales s ON s.cust_id = c.id
  WHERE c.city = 'Муром'

Тоже самое, но включает в выборку заказчиков, которые не совершали покупки.

SELECT * FROM customers c
  LEFT JOIN sales s ON s.cust_id = c.id
  WHERE c.city = 'Муром'

Для каждого мужчины выбрать женщин, которые выше него. Мужчины, для которых такой женщины не существуют, не будут выключены в выборку.

SELECT m.fullname AS man, f.fullname AS woman
  FROM males m
  JOIN females f ON f.height > m.height

Соединение именованными столбцами

Эквисоединения часто сравнивают столбцы, которые имеют одно и то же имя в обеих таблицах. Для таких соединений мы можем использовать второй тип явных соединений, называемый соединением именованными столбцами (Named Columns Joins). Соединение именованными столбцами осуществляются с помощью предложения USING, в котором перечисляются только имена столбцов.

Таким образом, один из запросов выше

SELECT * FROM PROGRAMMER P INNER JOIN LANG L ON P.LANG_ID = L.LANG_ID

можно переписать следующим образом

SELECT * FROM PROGRAMMER P INNER JOIN LANG L USING (LANG_ID)

что значительно короче.

Результирующий набор несколько отличается, по крайней мере, при использовании SELECT *.

• Результат соединения с явным условием соединения в предложении ON будет содержать каждый из столбцов дважды: один раз для левой таблицы и один раз для правой. Очевидно, что они будут иметь они и те же значения.
• Результат соединения именованными столбцами, с помощью предложения USING, будет содержать эти столбцы один раз.

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

SELECT P.*, L.* FROM PROGRAMMER P 
  INNER JOIN LANG L USING (LANG_ID)

Естественное соединение (NATURAL JOIN)

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

Для нашего примера запрос,

SELECT * FROM PROGRAMMER P NATURAL JOIN LANG L

эквивалентен запросу

SELECT * FROM PROGRAMMER P  INNER JOIN LANG L USING (LANG_ID)

Легко заменить, что если бы поля PNAME и LNAME назывались просто NAME, то результат был бы совершенно другим и не вернул бы ни одной записи.

Как и все соединения, естественные соединения являются внутренними соединениями по умолчанию, но вы можете превратить их во внешние соединения, указав LEFT, RIGHT или FULL перед ключевым словом JOIN.

Если в двух исходных таблицах не будут найдены одноименные столбцы, то будет выполнен CROSS JOIN.

Неявное соединение

В стандарте SQL-89 таблицы, участвующие в соединении, задаются списком с разделяющими запятыми в предложении FROM. Условия соединения задаются в предложении WHERE среди других условий поиска. Такие соединения называются неявными.

Синтаксис неявного соединения может осуществлять только внутренние соединения.

Пример неявного соединения:

SELECT * FROM PROGRAMMER P, LANG L WHERE P.LANG_ID = LANG.LANG_ID

Фактически данный запрос внутри СУБД будет преобразован ко внутреннему соединению, по условию после WHERE.

SELECT * FROM PROGRAMMER P INNER JOIN LANG L ON P.LANG_ID = L.LANG_ID

В настоящее время неявные соединения не рекомендованы для использования.

Перекрестные соединения (CROSS JOIN)

Такое соединение еще называют декартовым произведением двух отношений. Каждая строка левой таблицы соединяется с каждой строкой правой таблицы. Условий при этом никаких нет. Можно добавить условия в части WHERE по аналогии с неявным соединением.

Порядок таблиц для оператора неважен.

Перекрёстное соединение двух наборов эквивалентно их соединению по условию тавтологии (условие, которое всегда верно).

Следующие два запроса дадут один и тот же результат:

SELECT * FROM PROGRAMMER CROSS JOIN LANG;

SELECT * FROM PROGRAMMER JOIN LANG ON 1 = 1;

Перекрёстные соединения являются внутренними соединениями, потому что они отбирают строки, для которых есть соответствие, а соответствие есть для каждой строки. Внешнее перекрёстное соединение, если бы оно существовало, ничего не добавило бы к результату.

Для нашего примера запрос

SELECT * FROM PROGRAMMER CROSS JOIN LANG

вернет результат

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

SELECT
    m.name,
    s.size,
    c.name
FROM materials m
    CROSS JOIN sizes s
    CROSS JOIN colors c

Контрольные вопросы

1. Какие типы соединений вы знаете?
2. Какие классы соединений вы знаете?
3. Можно ли левое соединение заменить правым и что для этого нужно сделать?
4. Опишите простейший алгоритм работы внутреннего соединения?
5. Что такое эквисоединения?
6. Чем соединение именованными столбцами отличается от соответствующего эквисоединения?
7. В чем опасность применения естественных соединений?
8. Что такое неявные соединения?
9. Что такое перекрестные соединения?