SQL JOIN на собеседовании — число строк и завышенные суммы

SELECT o.order_id, p.product_id
FROM orders o
LEFT JOIN products p
  ON p.seller_id = o.seller_id;

До добавления JOIN сумма продаж сходилась с отчётом. После — выросла больше чем вдвое. Откуда взялись лишние деньги?

Допустим, заказ стоит €40 и содержит два товара. Присоединив товары, вы получите две строки с одной и той же суммой заказа — €40. Если сложить этот столбец, в отчёт попадёт €80 вместо €40. JOIN выполнил условие; ошибка в том, какие суммы вы решили складывать.

Ниже — минимальное и максимальное число строк для разных видов SQL JOIN. Это пригодится и на собеседовании аналитика, и когда коллега спросит, почему ваши отчёты не сходятся.

Пример

В учебном маркетплейсе Meridian 9 071 оплаченный заказ с общей стоимостью товаров (GMV) €552 645. В каждом заказе от одной до четырёх строк товаров — в среднем 1,79. Нам нужен GMV, но в запросе заодно присоединены товары — скажем, чтобы потом фильтровать по продукту:

SELECT COUNT(*)                AS rows_after_join,
       SUM(o.items_total_eur)  AS gmv_eur
FROM meridian.orders AS o
JOIN meridian.order_items AS i ON i.order_id = o.order_id
WHERE o.status = 'paid';
-- rows_after_join: 16 192   gmv_eur: 1 239 938

9 071 заказ превратился в 16 192 строки. Заказ с тремя товарами теперь встречается трижды, и его items_total_eur прибавляется трижды. Результат, €1 239 938, в 2,2 раза больше настоящего.

Вопрос на SQL-собеседовании: в двух таблицах по десять строк. Сколько будет после JOIN?

Это классический вопрос для аналитика: какое минимальное и максимальное число строк вернут INNER, LEFT, RIGHT, FULL и CROSS JOIN? Начните с уточнений: по какому условию соединяют таблицы и могут ли ключи повторяться? По одному числу строк нельзя определить точный результат.

Для таблицы ниже условие — left.key = right.key, ключи могут повторяться. В каждой таблице ровно десять строк. После соединения нет WHERE, DISTINCT, группировки или ограничения числа строк.

Для FULL OUTER JOIN отсутствие совпадений даст 20 строк, а не десять: сохранятся все строки обеих таблиц. Минимум десять получится при десяти парах один к одному. При обычном сравнении = значения NULL друг с другом не совпадают.

Чтобы получить максимум, поставьте 1 во всех ключах обеих таблиц. Каждый из десяти заказов совпадёт с десятью товарами: 10 × 10 = 100 пар. Именно этот случай разобран в интерактивной задаче выше. Если ключи уникальны и у каждой строки есть пара, INNER, LEFT, RIGHT и FULL JOIN вернут по десять строк.

Попробуйте в DuckDB:

WITH l AS (SELECT range AS row_id, 1 AS join_key FROM range(10)),
     r AS (SELECT range AS row_id, 1 AS join_key FROM range(10))
SELECT COUNT(*) AS joined_rows
FROM l LEFT JOIN r ON l.join_key = r.join_key;
-- 100

Пустая таблица, уникальные ключи, фильтры или другое условие соединения меняют границы. Правила совпадений описаны в документации DuckDB.

Как заметить

• Считайте строки до и после соединения. Если строк стало больше, а вы этого не ждали, — это размножение.
• Определите, что означает одна строка. Заказ, товар в заказе или клиент за день? Запишите это рядом с названием каждой таблицы, прежде чем их соединять.
• Сверяйтесь с итогом, которому доверяете. Если GMV внезапно вдвое больше прошлой недели, заподозрите JOIN, прежде чем праздновать.

Три способа исправить

1. Сначала агрегировать, потом соединять. Сведите сторону «много» к одной строке на ключ:

WITH items AS (
  SELECT order_id, SUM(qty) AS units
  FROM meridian.order_items
  GROUP BY order_id
)
SELECT SUM(o.items_total_eur) AS gmv_eur, SUM(i.units) AS units
FROM meridian.orders AS o
JOIN items AS i ON i.order_id = o.order_id
WHERE o.status = 'paid';
-- gmv_eur: 552 645

2. Не соединяйте то, что не нужно. Если вопрос про заказы, суммируйте одну таблицу заказов. Многие удвоенные итоги рождаются из JOIN, оставленного «на всякий случай».

3. Считайте уникальные ключи, когда считаете сущности. COUNT(DISTINCT o.order_id) даст правильное число заказов и после соединения. Осторожно: безопасного SUM(DISTINCT ...) для денег нет — у двух разных заказов может быть одна и та же сумма.

Обратная ловушка: соединения, которые теряют строки

Обычный JOIN молча выкидывает строки без пары. Соедините заказы с таблицей продавцов, где нескольких продавцов нет, — и эти заказы пропадут из всех итогов. Используйте LEFT JOIN, когда левая таблица должна остаться целой, и снова сравнивайте число строк.

Потренируйтесь предсказывать до запуска запроса

В курсе Atterna Start INNER и LEFT JOIN появляются на 8-й день, а 9-й день посвящён заказам, соединённым со строками товаров, и завышенным суммам. Вы предсказываете результат, запускаете запросы и проверяете его на вымышленном маркетплейсе Meridian. Понимание того, что означает одна строка, важнее, чем запоминание схемы с кругами.

Посмотреть программу Start. Дни 1–2 бесплатны без аккаунта; бесплатный аккаунт открывает остальные, включая полные занятия по JOIN в дни 8 и 9. Две мини-ситуации на этой странице тоже бесплатны. Платные уровни — кейс-раунды и симулятор Meridian: сравнить их можно на странице уровней.

Частые вопросы

Как отвечать на вопрос о числе строк после JOIN на собеседовании?

Сначала назовите условия. Для двух таблиц по десять строк, равенства ключей, допустимых повторов и без последующих фильтров: INNER JOIN даёт 0–100 строк; LEFT, RIGHT и FULL OUTER JOIN — 10–100; CROSS JOIN — ровно 100. Затем объясните один пример: если все ключи равны 1, каждая из десяти строк совпадёт с десятью другими. При дополнительных ограничениях границы нужно пересчитать.

Почему JOIN возвращает больше строк, чем было в таблице?

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

Как посчитать сумму после JOIN без двойного счёта?

Сначала сведите сторону «много» к одной строке на ключ в подзапросе или CTE, потом соединяйте; или обойдитесь без соединения, если его столбцы не нужны. COUNT(DISTINCT ...) подходит для подсчёта сущностей, но не для суммы денег.

Убрать дубли после JOIN с помощью DISTINCT?

SELECT DISTINCT может убрать нужные строки вместе с лишними. Сначала проверьте, почему они повторяются. Если вам нужна одна строка на заказ, сгруппируйте товары по заказам перед соединением.

Где потренировать ловушки JOIN?

В курсе Atterna Start ловушкам соединений посвящён целый день на данных маркетплейса, и каждый ответ проверяется автоматически. В проверке уровня есть вопрос ровно об этом.