JOIN без потерь и дублей: как соединение меняет зерно отчёта и выручку

Коротко

Соединение само по себе ничего не портит — портит метрика, посчитанная поверх изменившегося зерна. Поэтому весь разговор про JOIN сводится к одному вопросу: что такое одна строка результата после того, как ты присоединил вторую таблицу.

• Связь один-ко-многим умножает не только строки, но и все суммы из левой таблицы.
• Коэффициент роста суммы равен не среднему числу совпадений, а средневзвешенному по самой сумме.
• Если справа нужен один показатель, сверни правую таблицу в CTE до ключа, а потом соединяй.
• Когда нужен факт наличия связи, а не её поля, это EXISTS: он не размножает строки.
• «Кого нет» выражается через NOT EXISTS; LEFT JOIN ... IS NULL требует трёх дополнительных условий.
• NOT IN с единственным NULL в подзапросе возвращает ноль строк и выглядит как «таких нет».

Зерно результата меняется раньше, чем ты это заметишь в числах

Зерно (grain) — это ответ на вопрос «что описывает одна строка». У таблицы orders зерно очевидное: один заказ. У order_items — одна позиция в заказе. После orders join order_items зерно становится «позиция заказа», и orders в этом наборе больше нет: вместо одной строки на заказ лежит от одной до десяти, в каждой повторён один и тот же amount.

Ошибка редко выглядит как ошибка. Запрос выполняется, колонки на месте, топ категорий правдоподобен, тренд по дням сохраняет форму. Расходится только абсолютный уровень, а его проверяют реже всего: если в дашборде выручка июля ₽8,24 млн, а в бухгалтерии ₽3,18 млн, поиск причины обычно уходит в сторону курса, возвратов и атрибуции, а не в список таблиц запроса.

Привычка, которая закрывает большую часть таких историй, занимает одну строку: прежде чем писать select, назови словами зерно результата. «Одна строка — один заказ». «Одна строка — пользователь за день». «Одна строка — позиция заказа». Дальше каждое соединение проверяется на совместимость с этой фразой, и проверка становится механической.

Связь один-ко-многим размножает не строки, а деньги

Кардинальность описывает, сколько строк одной таблицы соответствует одной строке другой. Вариантов три, и риск у них разный. Один-к-одному безопасен: число строк не меняется, а соединение работает как расширение таблицы колонками. Один-ко-многим — основной источник завышенных метрик. Многие-ко-многим даёт произведение и ломает отчёт настолько заметно, что его обычно ловят сразу.

Чтобы увидеть механику без больших чисел, хватит пяти заказов. Сумма по orders — 17 000, это контрольное число. После соединения с позициями строк становится 11, и SUM(o.amount) по ним даёт 52 000. Заказ №4 на 8 600 содержит четыре позиции и попадает в сумму четырежды — один он добавляет 34 400 из 52 000.

Обрати внимание на коэффициент. Среднее число позиций на заказ здесь 11 / 5 = 2,2, а сумма выросла в 52 000 / 17 000 = 3,06 раза. Расхождение не случайно: сумма умножается на средневзвешенное число совпадений, где весом выступает сама сумма. Крупные заказы почти всегда содержат больше позиций, поэтому искажение выручки стабильно сильнее искажения количества строк. В июльском отчёте та же арифметика: строк стало в 2,4 раза больше, а выручки — в 2,59 раза.

Два соединения подряд перемножаются, а не складываются

В реальном запросе рядом с позициями обычно оказывается третья таблица: события доставки, логи статусов, обращения в поддержку. Каждая из них добавляет свою кардинальность, и множители не суммируются — они перемножаются по каждому заказу отдельно.

Продолжим пример. Добавим к тем же пяти заказам события: у №1 их два, у №2 одно, у №3 три, у №4 два, у №5 ни одного. Строк после второго соединения: 1×2 + 2×1 + 1×3 + 4×2 + 3×1 = 18, где у №5 остаётся три исходные строки, потому что left join не выбрасывает заказ без событий. Сумма по этому набору — 2 400 + 6 800 + 2 700 + 68 800 + 8 700 = 89 400, то есть в 5,26 раза больше контрольных 17 000.

Заказ №4 в финальном наборе занимает 8 строк из 18 и приносит 68 800 из 89 400 — больше трёх четвертей «выручки». Любой срез по этому набору получает смещение в пользу заказов с длинной историей: отчёт по категориям переоценит те, что встречаются в крупных многопозиционных заказах, отчёт по каналам — тот канал, где таких заказов больше. Форма распределения поедет, причём правдоподобно.

Кардинальность проверяется одним запросом до соединения

Угадывать связь по названию таблицы не нужно — она измеряется. Для каждой правой таблицы сравни число строк с числом уникальных значений ключа, по которому собираешься соединять. Равенство означает один-к-одному и безопасный JOIN. Любая разница означает один-ко-многим, и дальше вопрос только в том, сворачивать правую сторону или менять зерно отчёта.

Второй запрос полезнее первого: распределение числа совпадений на ключ. Оно сразу показывает и характер связи, и выбросы. Если 980 заказов имеют одну-две позиции, а у двадцати их по сорок, то средний множитель 2,4 ничего не объясняет, а перекос в отчёте создадут именно эти двадцать заказов.

Ту же проверку стоит применять к левой таблице, если ключ соединения не является её первичным ключом. Классика — соединение заказов с пользователями по email вместо user_id: в users один email встречается дважды из-за регистрации через разные каналы, и каждый заказ удваивается ещё до того, как в запрос приехали позиции.

-- уникален ли ключ справа
select
  count(*)                             as rows_total,
  count(distinct order_id)             as keys,
  count(*) - count(distinct order_id)  as extra_rows
from order_items;

-- как распределены совпадения на один ключ
select positions, count(*) as orders
from (
  select order_id, count(*) as positions
  from order_items
  group by order_id
) as per_order
group by positions
order by positions;

Сумму на заказ считают до соединения, а не после

Отсюда следует главное правило работы с фактами: каждая таблица приводится к зерну отчёта отдельно, и только потом наборы соединяются. Если отчёт отвечает на вопрос про заказы, то и позиции, и события должны прийти в него уже свёрнутыми до order_id — по одной строке на заказ. Тогда связь становится один-к-одному, а JOIN перестаёт влиять на суммы.

Технически это один-два CTE с group by и left join по ключу. Практически это ещё и способ разделить ответственность внутри запроса: в каждом CTE видно своё зерно, и при расхождении числа можно проверять по шагам, а не целиком. left join вместо join здесь обязателен: заказ без позиций или без событий должен остаться в отчёте с нулём, а не исчезнуть из выручки.

Контроль после сборки тоже простой. SUM(o.amount) по финальному набору обязан совпасть с SUM(amount) по orders за тот же период — это тождество, а не совпадение. Если оно не выполняется, какое-то из соединений осталось один-ко-многим, и искать нужно в нём, а не в данных.

Обратная сторона правила: свёрнутый слой отвечает не на все вопросы. В таком отчёте нельзя посчитать выручку по категориям товаров, потому что категория живёт на уровне позиции, а не заказа. Это нормальная ситуация, и решается она не возвратом к сырому соединению, а вторым отчётом с зерном «позиция заказа», где денежная метрика берётся из order_items.amount, а не из orders.amount.

with item_totals as (
  select order_id,
         sum(amount) as item_revenue,
         count(*)    as positions
  from order_items
  group by order_id
),
event_totals as (
  select order_id,
         count(*)         as events,
         max(occurred_at) as last_event_at
  from order_events
  group by order_id
)
select
  o.order_id,
  o.customer_id,
  o.amount,
  coalesce(i.item_revenue, 0) as item_revenue,
  coalesce(i.positions, 0)    as positions,
  coalesce(e.events, 0)       as events
from orders as o
left join item_totals  as i using (order_id)
left join event_totals as e using (order_id)
where o.paid_at >= date '2026-07-01'
  and o.paid_at <  date '2026-08-01';

-- контроль: должно совпасть с суммой по orders за тот же период
-- sum(o.amount) = 3 180 000, строк = 1000

DISTINCT не лечит размножение, а меняет одну ошибку на другую

Когда строк после соединения стало больше, первым под руку попадает select distinct. Иногда он даже возвращает правильное число строк — и именно это делает его опасным: проблема уходит с экрана, оставаясь в запросе.

Дедупликация идёт по всем выведенным колонкам, а не по order_id. Пока в select есть category или item_amount, строки различаются и distinct не убирает ничего, кроме полностью совпавших. А как только ты оставишь только колонки уровня заказа, появится обратная ошибка: два разных заказа одного клиента на одну сумму в один день схлопнутся в один, и выручка окажется заниженной. Из пяти заказов примера достаточно двух по 1 200 — и distinct превратит их в один.

SUM(distinct amount) не спасает по той же причине, только хуже: он складывает уникальные значения суммы, то есть два честных заказа по 1 200 учитывает как 1 200 один раз. Оба приёма обещают исправить зерно, но работают со значениями, а зерно задаётся ключом — поэтому починить его можно только агрегацией по ключу.

LEFT JOIN с условием правой таблицы в WHERE — это INNER JOIN

left join обещает сохранить все строки левой таблицы. Обещание действует до where: фильтр применяется уже к результату соединения, а у несовпавших строк все правые колонки равны NULL. Условие e.event_name = 'purchase' для такой строки даёт UNKNOWN, строка отбрасывается, и соединение тихо становится внутренним.

Разделение простое и работает всегда: всё, что описывает, какая правая строка считается совпадением, живёт в on; в where остаются условия по левой таблице. Исключение — проверка right.key is null, которая как раз и работает с результатом соединения.

Эту же ошибку легко опознать по симптому. Если число строк после left join с фильтром равно числу строк после join, значит фильтр уже отработал как внутреннее соединение, и заказы без событий из отчёта ушли вместе со своей выручкой.

-- LEFT JOIN только на вид: заказы без покупок отфильтрованы
select o.order_id, e.occurred_at
from orders as o
left join events as e on e.order_id = o.order_id
where e.event_name = 'purchase';

-- условие совпадения уехало в ON — заказы без события остались с NULL
select o.order_id, e.occurred_at
from orders as o
left join events as e
  on e.order_id = o.order_id
 and e.event_name = 'purchase';

IN сравнивает значения, EXISTS проверяет существование строки

Часть задач вообще не требует соединения: нужен не набор колонок из второй таблицы, а ответ «да или нет». «Пользователи, у которых есть оплаченный заказ в июле», «заказы, по которым был возврат», «товары, которые хоть раз попали в корзину» — во всех случаях правая таблица нужна как признак, и её поля в отчёт не идут.

IN сравнивает значение с набором значений: u.user_id in (select user_id from orders ...). EXISTS проверяет, вернул ли коррелированный подзапрос хотя бы одну строку, и может остановиться на первой. Оба варианта дают semi-join: левая строка либо остаётся целиком, либо не остаётся, и размножения не происходит по построению. Именно этим они отличаются от join, после которого пользователь с десятью платежами превращается в десять строк.

Масштаб разницы видно на числах: 25 000 пользователей, 68 000 оплаченных заказов. Наивное соединение даёт 68 000 строк, и count(*) по нему — это количество заказов, а не людей. EXISTS возвращает 9 100 строк, по одной на пользователя с покупкой. Довесок select distinct поверх соединения вернёт те же 9 100, но сначала материализует все 68 000 и отсортирует их — результат сойдётся, работа будет сделана лишняя.

Про производительность стоит говорить аккуратно: современный планировщик в PostgreSQL сводит IN (подзапрос) и EXISTS к одному и тому же semi-join, и обещать, что один оператор быстрее, нельзя. Разница между ними лежит в читаемости и в поведении на NULL. IN естественно читается на коротком статическом списке: where plan in ('free', 'pro', 'team'). EXISTS прямо называет вопрос — «существует ли связанная строка», — и в него удобно дописывать условия по окну и статусу.

-- 68 000 строк: зерно результата — заказ, а не пользователь
select u.user_id, u.channel
from users as u
join orders as o on o.user_id = u.user_id
where o.status = 'paid'
  and o.paid_at >= date '2026-07-01'
  and o.paid_at <  date '2026-08-01';

-- 9 100 строк: пользователь остаётся один раз
select u.user_id, u.channel
from users as u
where exists (
  select 1
  from orders as o
  where o.user_id = u.user_id
    and o.status = 'paid'
    and o.paid_at >= date '2026-07-01'
    and o.paid_at <  date '2026-08-01'
);

-- тот же ответ через IN: читается как «входит в список»
select u.user_id, u.channel
from users as u
where u.user_id in (
  select o.user_id
  from orders as o
  where o.status = 'paid'
    and o.paid_at >= date '2026-07-01'
    and o.paid_at <  date '2026-08-01'
);

Как только понадобились колонки справа, это снова JOIN

EXISTS не отдаёт поля подзапроса наружу — select 1 внутри существует именно для того, чтобы это подчеркнуть. Поэтому вопрос «кто покупал» он закрывает, а вопрос «кто покупал и на сколько» — нет.

Граница проходит по тому, что должно оказаться в отчёте. Нужен признак — EXISTS. Нужна сумма, дата первой покупки, число заказов — нужен слой, свёрнутый до user_id, и обычный left join к нему. Это тот же приём, что и с позициями заказа: сначала агрегация до нужного зерна, потом соединение.

Смешанный вариант, который встречается чаще всего и работает хуже всех: join ради одной колонки справа плюс select distinct сверху, чтобы убрать получившиеся дубли. Он даёт правильное число строк, пока выбранная колонка одинакова у всех совпадений, и ломается в первый же раз, когда у пользователя два заказа с разными суммами.

with paid_july as (
  select user_id,
         count(*)     as orders,
         sum(amount)  as revenue,
         min(paid_at) as first_paid_at
  from orders
  where status = 'paid'
    and paid_at >= date '2026-07-01'
    and paid_at <  date '2026-08-01'
  group by user_id
)
select
  u.user_id,
  u.channel,
  p.user_id is not null as has_purchase,
  coalesce(p.orders, 0)  as orders,
  coalesce(p.revenue, 0) as revenue
from users as u
left join paid_july as p using (user_id);

NOT EXISTS отвечает на вопрос «кого нет» и не ломается на дублях

Сегмент «не купили» нельзя получить вычитанием одного отчёта из другого глазами: нужна явная отрицательная проверка. Anti-join возвращает строки левой таблицы, для которых справа не нашлось ни одного совпадения, и в SQL это not exists.

Конструкция собирается из четырёх частей, и каждую стоит назвать вслух. Левая база — кто вообще участвует в вопросе (пользователи, зарегистрированные в первую неделю июля). Совпадение — какая правая строка считается покупкой (event_name = 'purchase'). Окно — в какой период это событие должно было случиться (первые 14 дней от регистрации каждого пользователя). Ключ — чем связаны стороны (user_id). Пропусти любую из четырёх, и запрос ответит на чужой вопрос, оставшись синтаксически безупречным.

Устойчивость not exists к дублям полезна на практике: сколько бы покупок ни было у пользователя, подзапрос интересует только факт существования хотя бы одной. Поэтому результат anti-join всегда содержит ровно по одной строке на объект левой базы — и сумма сегментов сходится с базой без дополнительных усилий. На июльской когорте это 12 000 зарегистрированных = 4 100 с покупкой + 7 900 без неё.

Второе свойство, за которое его выбирают: окно задаётся относительно каждой левой строки. Условие e.occurred_at < u.registered_at + interval '14 days' внутри коррелированного подзапроса читает registered_at того пользователя, который сейчас проверяется. Через фиксированные даты такой сегмент не выразить — у каждого участника когорты своя граница.

select u.user_id, u.channel
from users as u
where u.registered_at >= date '2026-07-01'
  and u.registered_at <  date '2026-07-08'
  and not exists (
    select 1
    from events as e
    where e.user_id = u.user_id
      and e.event_name = 'purchase'
      and e.occurred_at >= u.registered_at
      and e.occurred_at <  u.registered_at + interval '14 days'
  );

NOT IN с одним NULL возвращает ноль строк и выглядит как «таких нет»

Самая дорогая ловушка этой темы выглядит как отсутствие проблемы. Запрос выполняется, ошибок нет, результат пустой — и аналитик делает вывод: пользователей без заказов не осталось.

Разберём на пяти строках. В users лежат id от 1 до 5. В orders — три строки с user_id 1, 3 и NULL (гостевой заказ без привязки к аккаунту). where user_id not in (select user_id from orders) должен вернуть 2, 4 и 5, а возвращает ноль строк.

Причина в том, как not in раскрывается. Для пользователя 2 это 2 <> 1 and 2 <> 3 and 2 <> NULL. Первые два сравнения истинны, третье даёт UNKNOWN, потому что про неизвестное значение нельзя утверждать, что оно не равно двойке. TRUE and TRUE and UNKNOWN — это UNKNOWN, а where пропускает дальше только TRUE. Строка отбрасывается. То же происходит с 4 и 5, и с любым id: достаточно одного NULL в подзапросе, чтобы not in не вернул ничего никогда.

Положительная форма при этом работает. in раскрывается через OR: для пользователя 1 это 1 = 1 or 1 = 3 or 1 = NULL, и первое же TRUE делает всё выражение истинным независимо от UNKNOWN. Поэтому баг живёт только в отрицании — и проявляется ровно в тот момент, когда ты ищешь «кого нет». Заметить его на глаз невозможно: от корректного «таких пользователей не найдено» пустой результат отличить нечем.

Лечится тремя способами. Добавить where user_id is not null в подзапрос — работает, но требует помнить про NULL каждый раз. Переписать на not exists — проверка существования строки не использует сравнение на неравенство, поэтому NULL в orders.user_id просто не создаёт совпадения. Взять except — он сравнивает строки по правилам множеств, где NULL равен NULL. Первый вариант лечит симптом, второй и третий убирают причину.

-- users:  1, 2, 3, 4, 5
-- orders: user_id = 1, user_id = 3, user_id = null

-- 0 строк вместо 2, 4, 5
select user_id from users
where user_id not in (select user_id from orders);

-- что на самом деле вычисляется для user_id = 2
select (2 <> 1) and (2 <> 3) and (2 <> null) as keep_row;  -- null

-- 2, 4, 5 — три рабочих варианта
select user_id from users
where user_id not in (select user_id from orders where user_id is not null);

select u.user_id from users as u
where not exists (select 1 from orders as o where o.user_id = u.user_id);

select user_id from users
except
select user_id from orders;

LEFT JOIN ... IS NULL врёт при трёх типичных неточностях

Второй способ выразить anti-join — соединить через left join и оставить строки, где правая сторона пустая. Он удобнее not exists, когда нужно показать поля правой таблицы или разобраться, почему совпадений нет. Но у него три условия, без которых он даёт неправильный ответ.

Первое: колонка в is null должна быть гарантированно непустой в самой правой таблице. Если проверять e.campaign is null, в сегмент попадут и те, у кого события не было, и те, у кого оно было без заполненной кампании. Годится первичный ключ или ключ соединения.

Второе: все условия совпадения — имя события, статус, окно — обязаны стоять в on. Вынесенное в where условие по правой таблице превращает соединение во внутреннее, и тогда is null в том же where не оставит ни одной строки: одна и та же строка не может одновременно иметь event_name = 'purchase' и пустой user_id. Результат — стабильный ноль, который легко принять за пустой сегмент.

Третье: соединение до фильтра живёт на зерне «пользователь × событие», и если добавить к нему агрегат, получится то же завышение, что с позициями заказа. После where e.user_id is null дубли уходят сами — у несовпавшей левой строки ровно одна правая пара из NULL, — но до этого фильтра набор размножен, и любая промежуточная метрика по нему уже неверна.

select u.user_id, u.channel
from users as u
left join events as e
  on e.user_id = u.user_id
 and e.event_name = 'purchase'
 and e.occurred_at >= u.registered_at
 and e.occurred_at <  u.registered_at + interval '14 days'
where u.registered_at >= date '2026-07-01'
  and u.registered_at <  date '2026-07-08'
  and e.user_id is null;

-- та же попытка с условиями в WHERE: всегда 0 строк
select u.user_id
from users as u
left join events as e on e.user_id = u.user_id
where e.event_name = 'purchase'
  and e.user_id is null;

«Никогда не покупал» и «не купил за 14 дней» — разные сегменты

Большинство ошибок в anti-join не синтаксические, а методологические: запрос корректен, а вопрос подменён. Два условия, которые путают чаще всего, отличаются одной строкой в подзапросе и отвечают на разные задачи.

not exists без временных границ означает «за всю историю событий». Такой сегмент будет сжиматься сам по мере того, как старые пользователи когда-нибудь что-нибудь покупают, и годится разве что для списка тех, кто ни разу не дошёл до оплаты. Сегмент с окном от registered_at означает «не купил в первые две недели после регистрации» — он фиксирован во времени и подходит для сравнения когорт между собой.

Проверить, что реализовано именно нужное условие, помогает один случай: пользователь с покупкой на 15-й день. В сегменте «не купил за 14 дней» он обязан остаться, в сегменте «никогда не покупал» — обязан исчезнуть. Если поведение не то, дело в границах подзапроса, а не в данных.

Вторая методологическая деталь — левая база. «Пользователи без покупки» и «пользователи июльской когорты без покупки» дают разные числа, и разница растёт с возрастом продукта: в первом варианте в сегмент попадают все, кто зарегистрировался вчера и просто не успел. Границы левой базы лучше описывать в том же запросе, а не держать в фильтре дашборда.

• Левая база — кто участвует в вопросе и за какой период зарегистрирован.
• Совпадение — какое событие или статус считается наступившим.
• Окно — абсолютное или относительное, от даты каждой левой строки.
• Ключ — по чему связаны стороны и уникален ли он слева.

Какой инструмент под какой вопрос

Четыре конструкции из этого гайда не конкурируют, а отвечают на разные вопросы. Выбор определяется тем, что должно оказаться в результате, и каким должно остаться зерно.

Что проверить до того, как отчёт уйдёт в дашборд

Соединения почти никогда не падают с ошибкой — они возвращают не то число. Поэтому проверки здесь арифметические: ты сравниваешь полученное с тем, что обязано выполняться по определению.

• Зерно результата названо словами, и после каждого JOIN фраза остаётся правдой.
• Для каждой правой таблицы посчитано count(*) против count(distinct key).
• Число строк до и после каждого соединения зафиксировано, рост объяснён.
• SUM денежной колонки совпадает с суммой по исходной таблице за тот же период.
• Число уникальных ключей слева не изменилось после соединения.
• В left join все условия совпадения стоят в on, а не в where.
• Ни один distinct не используется как средство убрать дубли от соединения.
• В отрицательных проверках нет not in по подзапросу с возможным NULL.
• Сумма взаимоисключающих сегментов равна размеру левой базы.

Практика: один вопрос, четыре реализации

Возьми пять заказов из таблицы выше и собери их руками — в любой базе или в values. Посчитай SUM(amount) по orders, затем присоедини позиции и посчитай снова. Ты должен получить 17 000 и 52 000; если получилось иначе, расходится число позиций, а не логика. Добавь события и убедись, что сумма дошла до 89 400 и 8 из 18 строк принадлежат четвёртому заказу.

Дальше собери тот же отчёт через предварительную агрегацию и проверь, что сумма вернулась к 17 000, а строк снова пять. Сравни два запроса по explain: у второго в плане не будет ни distinct, ни лишнего шага сортировки поверх размноженного набора.

Вторая часть — про отрицание. Сделай список пользователей без покупки четырьмя способами: not exists, left join ... is null, not in и except. Затем добавь в orders одну строку с user_id = null и перезапусти все четыре. Три варианта ответят одинаково, один вернёт пустой результат — и это ровно тот случай, который в рабочем отчёте выглядит как корректный ответ «таких нет».

Последний шаг — окно. Добавь пользователя с покупкой на 15-й день после регистрации и проверь, что он остался в сегменте «не купил за 14 дней» и пропал из сегмента «никогда не покупал». После этого запиши в одну строку, какое именно условие реализует твой запрос: эта формулировка и пойдёт в описание сегмента, которым потом будут пользоваться другие.

Итог и куда дальше

Соединение — не способ добавить таблицу к запросу, а способ сменить зерно результата. Пока зерно совпадает с вопросом отчёта, метрики считаются правильно; как только соединение размножило левую сторону, любая сумма поверх набора отвечает на вопрос, который никто не задавал. Отсюда вся практика: измерить кардинальность до соединения, свернуть дочерние таблицы до нужного ключа, а для вопросов про наличие и отсутствие связи вообще не соединять — использовать exists и not exists.

И одно правило, которое дешевле запомнить, чем вывести заново: not in по подзапросу ломается от единственного NULL и ломается молча. Отрицательные проверки пиши через not exists — тогда у тебя останется на один источник тихих расхождений меньше.