Ловушки join и взрыв строк

Fan-out: два ребёнка, умноженные строки

Вспомни урок 1: join — это произведение, и число строк умножается. Fan-out — это то самое умножение, бьющее там, где ты не ждал. Возьми одного родителя (orders) с двумя детьми один-ко-многим: order_items (позиции) и payments (платежи частями). Соедини все три в одном запросе:

-- НЕВЕРНО: выручка удвоена (или хуже)
SELECT SUM(o.total) AS revenue,
       SUM(oi.quantity) AS units
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN payments    p  ON p.order_id  = o.id;

Если у заказа 3 позиции и 2 платежа, join производит 3 × 2 = 6 строк для этого единственного заказа — каждая позиция спарена с каждым платежом, потому что оба ребёнка джойнятся по order_id независимо. Теперь SUM(o.total) складывает total этого заказа 6 раз, а SUM(oi.quantity) считает каждую позицию 2 раза (по разу на платёж). Сумма заказа не изменилась; изменилось число строк, её несущих. Два ребёнка размножились друг против друга — декартово произведение внутри группы каждого заказа.

Фикс: предагрегируй каждого ребёнка перед join

Правило прямолинейно: никогда не соединяй двух независимых детей один-ко-многим с родителем и затем агрегируй. Схлопни каждого ребёнка до одной строки на родителя сначала — в подзапросе или CTE — затем соедини эти предагрегированные результаты, которые теперь один-к-одному с родителем и не могут размножиться:

-- ВЕРНО: каждый ребёнок агрегирован до одной строки на заказ, затем join
SELECT o.id,
       o.total,
       items.units,
       pays.paid
FROM orders o
LEFT JOIN (
  SELECT order_id, SUM(quantity) AS units
  FROM order_items GROUP BY order_id
) items ON items.order_id = o.id
LEFT JOIN (
  SELECT order_id, SUM(amount) AS paid
  FROM payments GROUP BY order_id
) pays ON pays.order_id = o.id;

Каждый подзапрос возвращает не более одной строки на order_id, поэтому ни один join не умножает. SUM(o.total) по этому теперь верен. Паттерн обобщается на любое число детей: агрегируй каждого в своём CTE/подзапросе, затем соедини сводки одна-строка-на-родителя. (LATERAL из прошлого урока — другой способ вычислить эти агрегаты на родителя инлайн.)

Для быстрого подсчёта иногда можно залатать fan-out через COUNT(DISTINCT oi.id) вместо COUNT(*) — distinct-подсчёт игнорирует дублированные строки. Но SUM(DISTINCT o.total) — ловушка: два разных заказа могут законно иметь одинаковый total, и DISTINCT их схлопнет. COUNT(DISTINCT) спасает подсчёты; только предагрегация надёжно спасает суммы.

Другие ловушки: пропущенные условия, неуникальные ключи, порядок join

Fan-out — тонкая, но три более грубые ловушки делят раздел. Когда ревьюишь запрос с несколькими join, спроси себя: ограничивает ли каждое условие join реальное отношение, уникален ли ключ хотя бы с одной стороны, и не присоединяются ли несколько независимых детей к одному родителю?

• Пропущенное условие join → случайный cross join из урока 1. Забытый ON или ON, который реально не связывает таблицы, даёт |A| × |B| строк — мгновенный взрыв, а не тихое удвоение.
• Неуникальные ключи join → даже «невинный» join дублирует строки, если ключ неуникален на стороне, которую ты считал уникальной. Соединение orders с users по email вместо id сделает fan-out, если два пользователя делят email. Всегда джойнь по ключу, уникальному хотя бы на одной стороне, или точно знай, почему он неуникален.
• Порядок join и планировщик → ты пишешь join слева направо, но планировщик свободно их переупорядочивает ради минимизации стоимости; результат идентичен независимо от написанного порядка (для inner join). Так что не подкручивай порядок join вручную ради корректности или скорости — чини предикаты и статистику и дай планировщику выбрать. (Outer join ограничивают переупорядочивание; глава execution-plans трека databases разбирает когда.)