JOIN по двум ключам: куда попали чужие платежи?

В обоих магазинах есть заказ №41. Ошибка в одном условии соединения подмешивает к отчёту чужие платежи — и SQL не сообщает об этом.

SQL Studio / Разборы SQL / JOIN по двум ключам: куда попали чужие платежи?

Как соединить две таблицы по нескольким полям? В условии ON укажите оба равенства через AND: o.tenant_id = p.tenant_id AND o.order_id = p.order_id. Здесь order_id уникален только внутри магазина. Если оставить его единственным условием, запись одного магазина совпадёт с одноимённым заказом другого. Страница показывает не абстрактную декартову таблицу, а ошибочное присвоение денег конкретному заказу.

Два магазина используют номер 41

В первом наборе sample_orders хранится одна строка на сочетание (tenant_id, order_id). Для магазина north заказ 41 принадлежит Ире и стоит 12 000 копеек, для south заказ 41 принадлежит Льву и стоит 8 000. Таблица sample_payments хранит платёжные события; failed не считаем полученными деньгами. Есть и подозрительный платёж магазина east, заказа для которого в наборе нет.

Ошибка: соединять только по order_id

Запрос запускается без ошибок. Однако у каждого из двух заказов №41 появятся три captured-платежа из разных магазинов.

WITH sample_orders(tenant_id, order_id, customer, amount_kopecks) AS (
  VALUES ('north', 41, 'Ира', 12000),
         ('south', 41, 'Лев', 8000),
         ('north', 42, 'Оля', 5000),
         ('south', 43, 'Мира', 7000)
),
sample_payments(payment_id, tenant_id, order_id, status, amount_kopecks) AS (
  VALUES (901, 'north', 41, 'captured', 12000),
         (902, 'south', 41, 'captured', 8000),
         (903, 'north', 42, 'failed', 5000),
         (904, 'east', 41, 'captured', 3000)
)
SELECT o.tenant_id AS order_tenant, o.order_id,
       p.payment_id, p.tenant_id AS payment_tenant,
       p.amount_kopecks
FROM sample_orders AS o
JOIN sample_payments AS p ON p.order_id = o.order_id
WHERE p.status = 'captured'
ORDER BY o.tenant_id, o.order_id, p.payment_id;
Неверное совпадение: шесть связей вместо двух
order_tenantorder_idpayment_idpayment_tenantamount_kopecks
north41901north12000
north41902south8000
north41904east3000
south41901north12000
south41902south8000
south41904east3000

Платёж 901 попал не только к своему заказу north/41, но и к south/41. Та же ошибка с 902 и 904. Если сложить суммы этих шести строк, получится 46 000 копеек, хотя платежи по существующим заказам двух магазинов дают только 20 000. Фильтр status = 'captured' сам по себе это не исправляет.

Исправление: два равенства внутри ON

Теперь каждая пара сравнивается сразу по магазину и локальному номеру заказа. Предикат с AND — не опциональная проверка, а часть определения связи. Важное условие: пара (tenant_id, order_id) действительно должна обозначать один заказ, что можно подкрепить составным PRIMARY KEY или UNIQUE в физической таблице.

WITH sample_orders(tenant_id, order_id, customer, amount_kopecks) AS (
  VALUES ('north', 41, 'Ира', 12000),
         ('south', 41, 'Лев', 8000),
         ('north', 42, 'Оля', 5000),
         ('south', 43, 'Мира', 7000)
),
sample_payments(payment_id, tenant_id, order_id, status, amount_kopecks) AS (
  VALUES (901, 'north', 41, 'captured', 12000),
         (902, 'south', 41, 'captured', 8000),
         (903, 'north', 42, 'failed', 5000),
         (904, 'east', 41, 'captured', 3000)
)
SELECT o.tenant_id, o.order_id,
       p.payment_id, p.amount_kopecks
FROM sample_orders AS o
JOIN sample_payments AS p
  ON p.tenant_id = o.tenant_id
 AND p.order_id = o.order_id
WHERE p.status = 'captured'
ORDER BY o.tenant_id, o.order_id;
Верные платежи: две строки с совпадающим tenant_id
tenant_idorder_idpayment_idamount_kopecks
north4190112000
south419028000

Это INNER JOIN, поэтому отсутствие captured-платежа исключает заказ. Если отчёт должен содержать и неоплаченные заказы, требуется другой вариант, а не искусственное присоединение чужого платежа.

Пограничный случай: сохранить заказ без captured

Запись north/42 имеет только неудачную попытку, а south/43 не имеет платёжной строки. Для перечня всех заказов перенесём фильтр статуса в ON и используем LEFT JOIN:

WITH sample_orders(tenant_id, order_id, customer, amount_kopecks) AS (
  VALUES ('north', 41, 'Ира', 12000),
         ('south', 41, 'Лев', 8000),
         ('north', 42, 'Оля', 5000),
         ('south', 43, 'Мира', 7000)
),
sample_payments(payment_id, tenant_id, order_id, status, amount_kopecks) AS (
  VALUES (901, 'north', 41, 'captured', 12000),
         (902, 'south', 41, 'captured', 8000),
         (903, 'north', 42, 'failed', 5000),
         (904, 'east', 41, 'captured', 3000)
)
SELECT o.tenant_id, o.order_id, o.customer,
       p.payment_id, p.amount_kopecks AS paid_kopecks
FROM sample_orders AS o
LEFT JOIN sample_payments AS p
  ON p.tenant_id = o.tenant_id
 AND p.order_id = o.order_id
 AND p.status = 'captured'
ORDER BY o.tenant_id, o.order_id;
Все четыре заказа; NULL там, где нет captured-платежа
tenant_idorder_idcustomerpayment_idpaid_kopecks
north41Ира90112000
north42ОляNULLNULL
south41Лев9028000
south43МираNULLNULL

NULL в payment_id здесь означает отсутствие подходящего captured-события, а не нулевой платёж. Если вынести p.status = 'captured' в WHERE, обе пустые строки исчезнут. Разница между условиями ON и WHERE подробно показана в материале про потерю строк в LEFT JOIN.

Контроль целостности: платёж, которому не нашлось заказа

После исправления лишний платёж не должен «приклеиваться» к существующему заказу. Отдельной анти-проверкой найдём событие с неизвестной парой ключей:

WITH sample_orders(tenant_id, order_id, customer, amount_kopecks) AS (
  VALUES ('north', 41, 'Ира', 12000),
         ('south', 41, 'Лев', 8000),
         ('north', 42, 'Оля', 5000),
         ('south', 43, 'Мира', 7000)
),
sample_payments(payment_id, tenant_id, order_id, status, amount_kopecks) AS (
  VALUES (901, 'north', 41, 'captured', 12000),
         (902, 'south', 41, 'captured', 8000),
         (903, 'north', 42, 'failed', 5000),
         (904, 'east', 41, 'captured', 3000)
)
SELECT p.payment_id, p.tenant_id, p.order_id
FROM sample_payments AS p
LEFT JOIN sample_orders AS o
  ON o.tenant_id = p.tenant_id
 AND o.order_id = p.order_id
WHERE o.order_id IS NULL
ORDER BY p.payment_id;
Платёж с отсутствующим составным ключом заказа
payment_idtenant_idorder_id
904east41

Это запись east/41, а не заказ north/41. На практике такой результат повод проверить загрузку, ограничения внешнего ключа и модель данных. Само ON не заменяет разграничение доступа между магазинами — оно лишь восстанавливает верную связь.

Попробуйте сами

Добавьте платёж ('south', 43, 'captured') с новым payment_id в VALUES. Сколько строк покажет второй запрос? Сохранит ли третий строку south/43 с NULL? Проверьте предположение в бесплатной SQLite-песочнице SQL Studio. Для соседней проблемы полезен разбор дубликатов и повторов платёжных событий и статья о детализации после JOIN.


Для самостоятельной практики здесь достаточно бесплатного SQLite-тренажёра. Если нужен отдельный последовательный курс на MySQL 8, а не на SQLite, можно посмотреть курс SQL для аналитика на Stepik: функции и некоторые детали синтаксиса у движков отличаются.