INNER против OUTER join

INNER оставляет совпадения; OUTER оставляет и несовпавшие

Из прошлого урока: JOIN — это отфильтрованное произведение, и вопрос в том, что делать со строками, не нашедшими партнёра. Этот выбор и есть различие inner/outer.

• INNER JOIN — оставить лишь выжившие пары. Пользователь без заказов просто исчезает из результата.
• LEFT OUTER JOIN — оставить каждую левую строку при любом раскладе. Если левая строка нашла совпадение — она спаривается нормально; если нет — она всё равно выдаётся один раз, со всеми колонками правой стороны в NULL. Это NULL-расширение.
• RIGHT OUTER JOIN — зеркало: оставить каждую правую строку, NULL-расширить несовпавшую левую. (A RIGHT JOIN B ≡ B LEFT JOIN A; большинство команд нормализуют к LEFT ради читаемости.)
• FULL OUTER JOIN — оставить всё: совпавшие пары, несовпавшие левые строки (правая в NULL) и несовпавшие правые строки (левая в NULL).

-- Каждый пользователь плюс его заказы при наличии; у пользователей без заказов колонки заказа NULL
SELECT u.id, u.name, o.id AS order_id, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

Пользователь, который никогда не заказывал, появляется здесь один раз с order_id и total в NULL. Эта строка — вся причина использовать outer join: она несёт информацию «у этой левой строки не было партнёра».

Ловушка: WHERE на внешней таблице низводит join

Вот баг из хука, в дистилляте. Расположение предиката решает, фильтрует ли он произведение (в ON) или результат (в WHERE) — а для outer join это не одно и то же.

-- НЕВЕРНО: это тайно INNER JOIN
SELECT u.id, u.name, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.status = 'shipped';   -- выполняется ПОСЛЕ NULL-расширения

Для пользователя без заказов o.status — это NULL. Сравнение NULL = 'shipped' даёт UNKNOWN, что WHERE трактует как ложь, поэтому NULL-расширенная строка отбрасывается. Каждая несовпавшая левая строка выбрасывается — LEFT JOIN теперь ведёт себя ровно как INNER JOIN. Фикс — перенести условие в ON, чтобы оно фильтровало, какие заказы считаются совпадением, до NULL-расширения:

-- ВЕРНО: фильтр совпадения в ON, сохраняем семантику outer
SELECT u.id, u.name, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'shipped';

Теперь пользователи без отгруженного заказа всё равно выдаются, NULL-расширенными. Правило: предикат на внешней (правой) таблице принадлежит ON, а не WHERE. WHERE на NULLable колонке внешней таблицы — почти всегда низведённый outer join. Единственное законное исключение — WHERE o.id IS NULL, которое намеренно оставляет только несовпавшие строки — это паттерн anti-join из следующего урока.

Чтение и подсчёт результатов outer

Поскольку несовпавшие строки несут NULL, для отображения или агрегации внешние колонки обычно оборачивают в COALESCE:

SELECT u.id,
       COUNT(o.id)                  AS order_count,   -- COUNT игнорирует NULL → 0 для незаказывавших
       COALESCE(SUM(o.total), 0)    AS lifetime_value -- SUM по нулю строк это NULL, а не 0
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id;

Здесь прячутся две сеньорские детали. COUNT(o.id) считает не-NULL значения, поэтому пользователь без заказов корректно получает 0 — а вот COUNT(*) посчитал бы NULL-расширенную строку как 1, классическая ошибка на единицу. И SUM по нулю совпавших строк возвращает NULL, а не 0, поэтому COALESCE(SUM(...), 0) обязателен ради вменяемого итога. Чтобы разделить совпавшие и несовпавшие, считай o.id IS NULL: это твоё число «пользователей, которые никогда не заказывали».