Если говорить о понимании оператора JOIN в SQL, то в голову приходят круги Эйлера, которых на просторах интернета существует целая тьма. Легко можно нагуглить нечто вроде:
Предполагается, что глядя на все эти кружки, читатель легко сложит представление, о том как работают join-ы. Очень долгое время я сам этим страдал. Ведь интуитивно же понятно, что тот же INNER JOIN вернёт записи, что совпадают в обеих таблицах по некоему ключу.
Данное ошибочное понимание разрушается простейшим вопросом, например, какое минимальное количество записей вернёт LEFT JOIN?
В голове тут же всплывает следующая картинка:
Ну и ты, соответственно, начинаешь метаться между первой и второй, причём абсолютно не понимая в каких терминах мыслить. Очевидно, что справа результирующее множество (в терминологии кругов Эйлера) меньше. Но, что отображает рисунок справа? Это все записи левой таблицы исключая совпадения из правой таблицы. А как посчитать эти совпадения не абстрактно? И это в случае дополнительного фильтра WHERE. Слева рисунок вроде как говорит, что результатом будут все записи из левой таблицы (и тут непонятно зачем тогда нужен join если мы получаем просто обычный SELECT). Дальше, если мы смотрим на фильр “ON a.id = b.id”, то мы должны взять только пересечение, а это получается INNER JOIN. Бред! Если не знать, как работают join-ы, догадаться о деталях, глядя на круги Эйлера, НЕРЕАЛЬНО!
Возникает вопрос: почему?
Давайте начнём с кругов Эйлера. Проблематика диаграм этого типа заключается в том, что они прекрасно работают с множествами, которые содержат УНИКАЛЬНАЕ элементы. Если же множества содержат дубликаты (в математике, в большинстве случаев, это бесмысленно, но в прогрммировании такое сплошь и рядом) круги Эйлера НЕ РАБОТАЮТ! Доказать элементарно. Рассмотрим следующую картинку:
Есть множество А, которое содержит 2 красных шарика. И множество В, которое содержит 1 красный шарик. Вопрос если мы будем объединять эти два множества по красным шарикам, как мы это должны отобразить? Что получим – понятно: 3 красных шарика, а вот как отобразить кругами Эйлера? Семантически похожая операция INNER JOIN отображается в кругах Эйлера как пересечение. Но разве это то, что нам нужно?
С join-ами ещё сложнее. Ведь результирующий элемент join-а это совершенно новая сущность, которая предствляет из себя совокупность всех ячеек из всех столбцов ОБЕИХ таблиц! Это даже не дубликаты в множествах. Это вообще два разнородных множества, которые порождают новое разнородное множество элементов. С ума сойти! И это всё пытаются впихнуть в круги Эйлера! Зачем так поступать с замечательным математиком!?..
Итак, можем ли мы как-то отобразить схематически join-ы не используя круги Эйлера? У меня получилось нечто вроде такого:
На мой взгляд, на данной диаграмме отчетливо видно:
- Результатом join-a является объединение всех столбцов двух таблиц.
- В верхней части отображены данные из левой таблицы, что не удовлетворяют условию ON a.id = b.id. Отчетливо видно что дополненые ячейки из правой таблицы “забиты” нулями. Это отвечает на вопрос о том что LEFT JOIN это не SELECT.
- В средней части мы наблюдаем INNER JOIN. И как можно видеть, что INNER JOIN – это не просто пересечение из общих данных для обеих таблиц. Но это список новых кортежей состоящих из объединения столбцов двух таблиц в случае ON a.id = b.id.
- Ну и в нижней части можно заметить данные из правой таблицы, что не попали в результат выборки.
В данной диаграмме не отображены только кортежи, которые могут появиться в средней части в случае, когда один элемент из левой таблицы имеет множество совпадений из правой. Но это единственный минус. Все остальные ньюансы присутствуют.
И имея такую диаграму тут же понятно почему запрос SELECT * FROM tableA a LEFT JOIN tableB b ON a.id=b.id WHERE b.id IS NULL возвращает только часть таблицы A и почему b.id может быть NULL:
Без такого представления, а имея только круги Эйлера, мне было совершенно непонятно почему вообще что-то возвращается, когда b.id является NULL! Ведь если не знать как работает join и читать квери “в лоб, то получается полная ахинея. Типа сделай мне join и выбери из него только те кортежи, где b.id является NULL. Но в таком случае, если b.id является NULL, то и a.id является NULL по условию ON. А как такое может быть?! Из кругов Эйлера это НЕОЧЕВИДНО абсолютно!
INNER JOIN получился таким:
