В модуле 2 мы уже познакомились с θ-соединением: JOIN ... ON p — это σ_p(R1 × R2). Эта операция симметрична: если у клиента нет заказов, клиент исчезнет из результата; если у заказа нет клиента (что в нормальной схеме невозможно, но допустим) — заказ тоже выпадет.

В этом уроке мы углубим инвентарь: добавим четыре варианта, которые отличаются ровно одним — что делать с «непарными» строками. Это самая частая тема собеседований по SQL и самая частая причина «у меня в отчёте пропали клиенты, у которых нет заказов».

INNER JOIN: повтор и точное определение

INNER JOIN (или просто JOIN) возвращает только те пары, для которых предикат истинен. Если у строки слева нет ни одной пары справа — она пропадает. Аналогично справа.

Формально:

$R_1 \bowtie_p R_2 = \{ (t_1, t_2) \mid t_1 \in R_1, t_2 \in R_2, p(t_1, t_2) = \text{TRUE} \}$

Обрати внимание на = TRUE в конце. Если предикат вернул NULL (например, из-за NULL в одной из колонок) — это не TRUE, и строка не попадёт в результат. В трёхзначной логике SQL INNER JOIN так же безжалостен к NULL, как WHERE.

В результате нет клиентов 8 (Гиорги) и 10 (Ирина) — у них нет заказов в нашей вселенной. Это и есть «безжалостность» INNER JOIN.

LEFT OUTER JOIN: «сохрани левых»

Теперь мы хотим всех клиентов, даже тех, у кого нет заказов. Для этого существует LEFT OUTER JOIN (слово OUTER можно опустить, обычно пишут LEFT JOIN).

Правило: возьми все строки левой таблицы. Для каждой найди пары справа по предикату. Если пар нет — оставь левую строку и подставь NULL во все правые колонки.

Гиорги (id=8) и Ирина (id=10) теперь есть в выводе — но order_id и status у них NULL. Это очень частая идиома: «найди тех, у кого нет ни одной пары» — LEFT JOIN ... WHERE right.id IS NULL. Мы вернёмся к ней в уроке про anti-join.

RIGHT OUTER JOIN: «сохрани правых»

RIGHT JOIN — зеркальная операция. Сохраняет все строки правой таблицы, добавляет NULL-ы слева для тех, у кого нет пары.

В реальном коде RIGHT JOIN встречается редко — почти всегда легче поменять таблицы местами и написать LEFT JOIN. Это даёт более привычное чтение слева направо: «бери левую таблицу, добивай данными справа».

Результат идентичен предыдущему LEFT JOIN. Договорённость в команде обычно такая: пиши LEFT JOIN, чтобы все читали запрос одинаково.

FULL OUTER JOIN: сохрани всех

FULL OUTER JOIN — это объединение LEFT и RIGHT. Сохраняет «сирот» с обеих сторон. Если строка слева не нашла пары — её правая часть NULL. Если справа не нашла — левая NULL. Если обе нашли друг друга — обычная пара.

FULL OUTER JOIN обычно нужен в задачах сверки: «какие записи есть в источнике A, но нет в источнике B, и наоборот».

Строки с payment_id IS NULL — заказы, за которые ещё не заплатили (например, status = pending или cancelled). Строк с order_id IS NULL в этой вселенной нет (платежи всегда привязаны к существующему заказу), но в реальной системе именно так ловят «осиротевшие» платежи.

Где появляется NULL — и где это опасно

При OUTER JOIN все «непарные» столбцы заполняются настоящим SQL NULL. Это значит, что любой WHERE-предикат на правой таблице после LEFT JOIN может неожиданно отфильтровать как раз тех «сирот», ради которых ты писал OUTER.

WHERE o.status = 'delivered' отсеивает строки, где o.status — NULL (у Гиорги и Ирины нет пары), потому что NULL = 'delivered' это NULL, не TRUE. LEFT JOIN молча схлопывается до INNER.

Правильный способ — перенести фильтр в ON:

Теперь Гиорги и Ирина в результате есть (с NULL в status), а среди заказов остаются только delivered. Разница между ON и WHERE для OUTER JOIN — отдельный урок 4.6, там разберём подробнее.

NATURAL JOIN: почему нельзя

NATURAL JOIN берёт предикат равенства автоматически, по всем атрибутам с одинаковыми именами в обеих таблицах. Звучит удобно, выглядит лаконично:

SELECT * FROM orders NATURAL JOIN customers;

Проблема: что произойдёт, если кто-то добавит в customers колонку id без переименования? Или поле created_at появится и там и там? Поведение запроса молча изменится — предикат расширится на новые колонки, и результат потеряет строки.

Конкретно в нашей вселенной NATURAL JOIN customers JOIN orders соединит по id (а не по customer_id), потому что id есть в обеих таблицах. Это бессмысленно — мы хотели соединить через FK, а не сравнить customers.id = orders.id.

Правило простое: никогда не пиши NATURAL JOIN в проде. Всегда явно указывай ON ... или, если очень хочется компактности, USING (...) — про это в уроке 4.6.

Чек-лист

• INNER JOIN — только пары, для которых предикат = TRUE. NULL в предикате выбрасывает строку.
• LEFT JOIN — все строки слева, плюс пары справа; «сироты» слева получают NULL в правых колонках.
• RIGHT JOIN — зеркальный LEFT; в коде пиши LEFT, поменяв таблицы местами.
• FULL OUTER JOIN — сироты с обеих сторон; полезен для сверок.
• WHERE-фильтр на правой таблице после LEFT JOIN молча схлопывает его до INNER. Перенеси фильтр в ON, если хочешь сохранить сирот.
• NATURAL JOIN опасен — соединяется по всем одноимённым атрибутам, поведение меняется при изменении схемы. Не используй.