JOIN в SQL: как соединять таблицы и находить причины дублей

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

Разберем INNER JOIN и LEFT JOIN на небольшом учебном наборе данных. Затем проверим два частых источника проблем: повторение строк левой таблицы и фильтр, который исключает клиентов без подходящего заказа.

Начните со смысла строки и ключей

В таблице customers одна строка означает одного клиента. Поле id уникально: клиент 1 — Анна, клиент 2 — Борис, клиент 3 — Вера. В таблице orders одна строка означает один заказ. Ее поле customer_id указывает, кому принадлежит заказ, и может повторяться: один клиент способен оформить несколько заказов.

Все имена, суммы и заказы здесь условные. У Анны два заказа, у Бориса один, а у Веры нет заказов. Связь customers.id = orders.customer_id отражает эту ситуацию. Соединять таблицы по имени клиента было бы ненадежно: разные люди могут иметь одинаковые имена, а написание может меняться.

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

INNER JOIN оставляет совпавшие пары

Для получения заказов с именами клиентов напишем запрос. Псевдонимы c и o сокращают обращения к таблицам, а условие ON задает связь между ними.

SELECT c.id AS customer_id, c.name, o.id AS order_id, o.amount
FROM customers AS c
INNER JOIN orders AS o ON o.customer_id = c.id
ORDER BY c.id, o.id;

На учебных данных результат содержит три строки: два заказа Анны и один заказ Бориса. Вера не появляется, поскольку совпавшего заказа для нее нет. Это ожидаемое поведение, а не потеря клиента из исходной таблицы.

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

LEFT JOIN сохраняет клиентов без заказов

Если нужно увидеть и клиентов без заказов, используйте LEFT JOIN. Он сохраняет строки левой таблицы; при отсутствии совпадения поля правой части получают NULL.

SELECT c.id AS customer_id, c.name, o.id AS order_id, o.amount
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
ORDER BY c.id, o.id;

Теперь получатся четыре строки: две для Анны, одна для Бориса и одна для Веры с NULL в полях заказа. LEFT JOIN не означает «ровно одна строка на клиента». Он сохраняет клиента, но выводит отдельную строку для каждого найденного совпадения.

Именно поэтому повтор имени Анны еще не является дублем. Строки различаются идентификатором заказа и представляют разные факты. Применение DISTINCT к имени скроет детализацию, но не ответит на вопрос, какую сумму заказов нужно посчитать.

Почему фильтры ON и WHERE дают разный результат

Предположим, нужен список всех клиентов и только их оплаченных заказов. Условие статуса можно включить в соединение:

SELECT c.name, o.id AS order_id
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id AND o.status = 'paid'
ORDER BY c.id;

В результат попадут Анна с заказом 11, Борис с заказом 13 и Вера без заказа. Если перенести o.status = 'paid' в WHERE после LEFT JOIN, строка Веры будет исключена: условие не выполняется для NULL. Такой запрос подойдет для другого вопроса, но не сохранит всех клиентов.

При разборе чужого запроса уточняйте, какую совокупность автор собирался сохранить. Недостаточно увидеть слово LEFT и решить, что все строки первой таблицы обязательно останутся после последующих фильтров.

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

Для общей суммы заказов можно сгруппировать результат по клиенту. COUNT(o.id) считает найденные заказы, а не строки с пустой правой частью. COALESCE заменяет отсутствие суммы на ноль для вывода отчета.

SELECT c.id, c.name,
       COUNT(o.id) AS orders_count,
       COALESCE(SUM(o.amount), 0) AS total_amount
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;

Для Анны ожидаются 2 заказа и сумма 2000, для Бориса — 1 и 500, для Веры — 0 и 0. Если добавить к запросу еще одну таблицу с несколькими строками на заказ, сумма может повториться. Перед расширением запроса снова проверьте уровень детализации и при необходимости предварительно агрегируйте данные.

Полезная проверка состоит из трех вопросов: сколько клиентов ожидалось, сколько заказов найдено и сходится ли итоговая сумма с исходными заказами? Затем просмотрите один конкретный customer_id. На маленькой выборке причина лишних совпадений обычно видна лучше, чем в общем отчете.

В программе Нетологии «SQL и получение данных» прямо указаны типы JOIN и работа с уникальными и неуникальными значениями. При выборе обучения ищите задания, где нужно не только написать запрос, но и объяснить число строк и проверить расчеты после соединения.