JOIN в SQL: виды, ON против WHERE и потерянные строки

Что делает джойн и в каком порядке идёт запрос

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

INNER оставляет только совпавшие пары. LEFT берёт всю левую таблицу, а справа подставляет совпавшее или NULL, если пары не нашлось. FULL делает то же с обеих сторон. CROSS соединяет каждую строку с каждой - обычно он получается не нарочно, из забытого условия соединения.

Запрос выполняется не в том порядке, в каком написан. Сначала FROM и JOIN, потом WHERE, потом GROUP BY, потом HAVING, и только затем SELECT, а в самом конце ORDER BY и LIMIT. Из этого следует бытовая странность, которая всех удивляет: алиас, придуманный в SELECT, не виден в WHERE - к моменту WHERE его ещё не существует.

// Держи этот порядок в голове как конвейер: каждая следующая стадия видит только то, что пережило предыдущую. Половина «необъяснимых» результатов объясняется именно им.

NULL

не ноль и не пустая строка, а «значение неизвестно». Любое сравнение с ним даёт не «ложь», а третий вариант - «неизвестно», и это источник половины сюрпризов в SQL

ON против WHERE: где живёт условие по правой таблице

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

Если условие o.status = 'paid' написать в ON, вернутся две строки: второй юзер на месте, у него просто NULL в статусе. Если то же условие написать в WHERE, вернётся одна строка. Проверено на живой базе, и это не тонкость реализации, а прямое следствие порядка выполнения.

Почему так. JOIN отрабатывает первым и честно оставляет второго юзера с NULL справа. Потом приходит WHERE и проверяет NULL = 'paid'. Результат сравнения - «неизвестно», а WHERE пропускает дальше только честное «истина». Юзер отваливается, и LEFT JOIN незаметно превратился в INNER.

// Правило: условие ОТБОРА пары живёт в ON, условие ФИЛЬТРАЦИИ результата - в WHERE. Одно исключение осознанное: анти-джойн «кто вообще без заказа» пишется как LEFT JOIN плюс WHERE right.id IS NULL, и вот тут отваливание строк - именно то, что нужно.

-- оба юзера на месте: у второго просто NULL
SELECT u.id, o.status FROM users u
LEFT JOIN orders o
  ON o.user_id = u.id AND o.status = 'paid';

-- WHERE o.status = 'paid' -- съел бы юзеров без оплат

Размножение строк: почему выросли суммы

Слева две строки: клиент 1 на 100 рублей и клиент 2 на 200. Сумма 300. Справа - таблица тегов, где у клиента 1 два тега, а у клиента 2 один. Джойним и получаем три строки, а SUM по сумме заказа выдаёт 400. Деньги выросли на сотню из ничего.

Никакой ошибки не произошло. Строка клиента 1 нашла справа две пары и превратилась в две строки, каждая со своими ста рублями. SUM послушно сложил сотню дважды. Это называется размножением строк, и в аналитическом SQL это ошибка номер один - потому что запрос отрабатывает без единого предупреждения.

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

// Экспресс-проверка до джойна, десять секунд: сравни COUNT(*) и COUNT(DISTINCT ключ) на правой таблице. Разошлись - ключ не уникален, размножение будет. А если нужно всего лишь «у кого есть хоть один», бери EXISTS: он проверяет наличие и строк не плодит, в отличие от JOIN с последующим DISTINCT.

зерно таблицы

что означает одна её строка. Пока не назвал зерно обеих таблиц словами, джойнить рано

Как отвечать: «После джойна суммы стали больше, чем были. Что случилось?»

Классическое размножение строк: ключ справа оказался неуникальным, каждая левая строка склеилась с несколькими правыми, и SUM посчитал одни и те же деньги по нескольку раз. Первым делом смотрю кардинальность правого ключа - COUNT(*) против COUNT(DISTINCT key), это занимает секунду. Дальше лечу по месту: агрегирую правую таблицу до зерна левой или джойню готовый предагрегат. Воткнуть DISTINCT поверх результата - это лечение симптома: суммы всё равно будут кривые, а кривой джойн останется в запросе и выстрелит в следующий раз.

Назван механизм, дана экспресс-диагностика и честное лечение вместо заплатки. По этому вопросу интервьюер отличает практика мгновенно, потому что теоретики предлагают DISTINCT.

На чём валят

• −NOT IN с NULL внутри подзапроса возвращает пусто - всегда, при любых данных. Сравнение с NULL даёт «неизвестно», а WHERE пропускает только «истина». Пиши NOT EXISTS.
• −Суммы после джойна многие-ко-многим задваиваются: агрегируй правую таблицу до джойна, а не после.
• −WHERE по правой таблице после LEFT JOIN молча выкидывает строки без пары - фильтр по правой живёт в ON.
• −Джойнить по «уникальному» ключу, уникальность которого никто не проверял.
• −Забыть условие соединения и получить декартово произведение: миллион на миллион складывается быстро.