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_tenant | order_id | payment_id | payment_tenant | amount_kopecks |
|---|---|---|---|---|
| north | 41 | 901 | north | 12000 |
| north | 41 | 902 | south | 8000 |
| north | 41 | 904 | east | 3000 |
| south | 41 | 901 | north | 12000 |
| south | 41 | 902 | south | 8000 |
| south | 41 | 904 | east | 3000 |
Платёж 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 | order_id | payment_id | amount_kopecks |
|---|---|---|---|
| north | 41 | 901 | 12000 |
| south | 41 | 902 | 8000 |
Это 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;
| tenant_id | order_id | customer | payment_id | paid_kopecks |
|---|---|---|---|---|
| north | 41 | Ира | 901 | 12000 |
| north | 42 | Оля | NULL | NULL |
| south | 41 | Лев | 902 | 8000 |
| south | 43 | Мира | NULL | NULL |
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_id | tenant_id | order_id |
|---|---|---|
| 904 | east | 41 |
Это запись east/41, а не заказ north/41. На практике такой результат повод проверить загрузку, ограничения внешнего ключа и модель данных. Само ON не заменяет разграничение доступа между магазинами — оно лишь восстанавливает верную связь.
Попробуйте сами
Добавьте платёж ('south', 43, 'captured') с новым payment_id в VALUES. Сколько строк покажет второй запрос? Сохранит ли третий строку south/43 с NULL? Проверьте предположение в бесплатной SQLite-песочнице SQL Studio. Для соседней проблемы полезен разбор дубликатов и повторов платёжных событий и статья о детализации после JOIN.
Для самостоятельной практики здесь достаточно бесплатного SQLite-тренажёра. Если нужен отдельный последовательный курс на MySQL 8, а не на SQLite, можно посмотреть курс SQL для аналитика на Stepik: функции и некоторые детали синтаксиса у движков отличаются.