Как на самом деле работают операции JOIN в SQL?

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

Гугл подтверждает это:

Все используют Диаграммы Венна для объяснения джойнов. Но это…

ПРОСТО НЕПРАВИЛЬНО!

Диаграммы Венна превосходно подходят для иллюстрации операций над множествами! В SQL известно три из них:

• UNION
• INTERSECT
• EXCEPT

И они могут быть объяснены так:

 

(эти слайды взяты из презентации тренинга JOOQ )

Большинство из Вас редко используют операцию UNION, INTERSECT и EXCEPT еще более экзотичны.

Суть: эти операции оперируют наборами элементов (кортежами), которые имеют одинаковый тип. Как видно на примерах сверху, все элементы это записи о людях с именем и фамилией.Так же INTERSECT и EXCEPT редко используются, потому что обычно они бесполезны. JOIN намного полезней. Например, Вы хотите соединить набор актеров со связанным с ним набором фильмов.

JOIN на самом деле декартово произведение с фильтрами. Приведем хорошую иллюстрацию декартова произведения:

Так какой же лучший способ иллюстрировать операции JOIN?

Диаграммы JOINов! Давайте сперва глянем на CROSS JOIN. потому что дргуие типы JOIN могут быть выведены из CROSS JOIN:

Запомните, перекрестное соединение (cross join) совершается следующим образом: берется каждый элемент из левой стороны, и соединяется с каждым элементом с правой стороны. Когда вы совершаете CROSS JOIN таблицы с 3 записями с таблицей, укоторой  4 записи, то получите 3×4=12 результирующих записей.

INNER JOIN

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

INNER JOIN это CROSS JOIN, в котором содержатся только те комбинации которые сохранились в соответствии с заданным предикатом. Например:

-- "Classic" ANSI JOIN syntax
SELECT *
FROM author a
JOIN book b ON a.author_id = b.author_id
 
-- "Nice" ANSI JOIN syntax
SELECT *
FROM author a
JOIN book b USING (author_id)
 
-- "Old" syntax using a "CROSS JOIN"
SELECT *
FROM author a, book b
WHERE a.author_id = b.author_id

OUTER JOIN

Типы OUTER JOIN помогают нам, там где мы хотим оставить только те записи, либо с ЛЕВОЙ стороны, либо с ПРАВОЙ или с обоих (FULL) сторон, которые соответствуют предикату.

LEFT OUTER JOIN в реляционной алгебре определяются как:

В SQL:

SELECT *
FROM author a
LEFT JOIN book b USING (author_id)

В результате мы получим всех авторов и их книги, но если автор не имеет ни одной книги, мы получим автора с NULL в качестве значения его книги. Это то же самое, что написать так:

SELECT *
FROM author a
JOIN book b USING (author_id)

UNION

SELECT a.*, NULL, NULL, NULL, ..., NULL
FROM (
SELECT a.*
FROM author a

EXCEPT

SELECT a.*
FROM author a
JOIN book b USING (author_id)
) a

Но никто не хочет писать столько SQL, поэтому OUTER JOIN были введены.

Примеры

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

SELECT A.Colour, B.Colour FROM A CROSS JOIN B

Cross join (или декартово произведение) создает результат с каждой комбинацией записей с двух таблиц. Каждая таблица имеет по 4 записи и результат будет содержать 16 записей.

SELECT A.Colour, B.Colour FROM A INNER JOIN B ON A.Colour = B.Colour

Inner join вернет все записи из перекрестного соединения, которые соответствуют условию join. В этом случае имеем 5 записей:

SELECT A.Colour, B.Colour FROM A INNER JOIN B ON A.Colour NOT IN (‘Green’,’Blue’)

Условие join не обязательно должно быть условием равенства и не всегда может иметь ссылки на столбцы из обоих (или даже одной) из таблиц. Вычисление условия A.Colour NOT IN ('Green','Blue') на каждой строке cross join возвращает:

SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour

Outer join логически вычисляются таким же способом, как и inner джойны, кроме того что если запись из левой таблицы (или правой) не соединяется с любой из строк из правой таблицы она будет внесена в результат со значением NULL для правой колонки.

SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour WHERE B.Colour IS NULL

В этом примере просто ограничивается результат предыдущего примера, теперь результат возвращает записи, в которых B.Colour IS NULL. В этом конкретном случае, это будут строки, которые не имеют совпадений  с правой стороны  и запрос вернет единственную красную строку, которая не имеет совпадений с таблицей B. Этот трюк также известен как anti semi join.

SELECT A.Colour, B.Colour FROM A RIGHT OUTER JOIN B ON A.Colour = B.Colour

RIGHT OUTER JOIN действует так же, как и LEFT OUTER JOIN, кроме того, что он хранит не сопадающие записи с правой таблицы и NULL в качестве значения записи из левой таблицы.

SELECT A.Colour, B.Colour FROM A FULL OUTER JOIN B ON A.Colour = B.Colour

FULL OUTER JOIN комбинирует поведение левого и правого джойна и хранит не совпадающие записи из левой и правой таблицы.

Заключение

Скажи НЕТ диаграмме Венна!

Джойны относительно легко понять интуитивно. И их сравнительно легко объяснить с помощью диаграмм Венна. Но всякий раз, когда Вы делаете это, запомните, что Вы делаете неправильную аналогию. JOIN не является строго заданной операцией над множествами, которая может быть описана диаграммами Венна. JOIN всегда декартово произведение с предикатом, и возможно UNION для добавления записей к результату OUTER JOIN.

Так что, если сомневаетесь, то применяйте диаграммы JOINов, лучше чем Диаграммы Венна. Они более точны и визуально более полезны.