Соединение источников (JOIN)

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

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

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

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

Таблица PROGRAMMER

LANG_ID	NAME
1	Андрей
2	Леонид
1	Сергей
4	Григорий

Таблица LANG

LANG_ID	NAME
1	C/C++
2	Java
3	Python

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

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

где :

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

а :

[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)

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

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

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

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

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

LANG_ID	NAME	LANG_ID1	NAME1
1	Андрей	1	C/C++
2	Леонид	2	Java
1	Сергей	1	C/C++

Обратите внимание, что возможны несколько совпадений.

Левое внешнее соединение (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

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

LANG_ID	NAME	LANG_ID1	NAME1
1	Андрей	1	C/C++
2	Леонид	2	Java
1	Сергей	1	C/C++
4	Григорий	NULL	NULL

Правое внешнее соединение (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

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

LANG_ID	NAME	LANG_ID1	NAME1
1	Андрей	1	C/C++
2	Леонид	2	Java
1	Сергей	1	C/C++
NULL	NULL	3	Python

Полное внешнее соединение (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

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

LANG_ID	NAME	LANG_ID1	NAME1
1	Андрей	1	C/C++
2	Леонид	2	Java
1	Сергей	1	C/C++
4	Григорий	NULL	NULL
NULL	NULL	3	Python

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

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

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

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

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

SELECT *
FROM customers c
JOIN sales s ON s.cust_id = c.id
WHERE c.city = 'Detroit'

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

SELECT *
FROM customers c
LEFT JOIN sales s ON s.cust_id = c.id
WHERE c.city = 'Detroit'

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

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)

Еще пример с соединением по именованным столбцам:

SELECT *
FROM flotsam f
JOIN jetsam j
ON f.sea = j.sea AND f.ship = j.ship

можно переписать так:

SELECT *
FROM flotsam
JOIN jetsam USING (sea, ship)

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

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

Например, даны две таблицы:

CREATE TABLE TA (
    a BIGINT,
    s VARCHAR(12),
    ins_date DATE
);

CREATE TABLE TB (
    a BIGINT,
    descr VARCHAR(12),
    x FLOAT,
    ins_date DATE
);

Естественное соединение таблиц TA и TB будет происходить по столбцам a и ins_date , а два следующих оператора дадут один и тот же результат:

SELECT * FROM TA NATURAL JOIN TB;

SELECT * FROM TA JOIN TB USING (a, ins_date);

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

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

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

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

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

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

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

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

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

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

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

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

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

SELECT * FROM TA CROSS JOIN TB;

SELECT * FROM TA JOIN TB ON 1 = 1;

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

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

SELECT * FROM PROGRAMMER CROSS JOIN LANG

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

LANG_ID	NAME	LANG_ID1	NAME1
1	Андрей	1	C/C++
1	Андрей	2	Java
1	Андрей	3	Python
2	Леонид	1	C/C++
2	Леонид	2	Java
2	Леонид	3	Python
1	Сергей	1	C/C++
1	Сергей	2	Java
1	Сергей	3	Python
4	Григорий	1	C/C++
4	Григорий	2	Java
4	Григорий	3	Python

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

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