Запросы и соединения — Примеры

Пример 1 — Три таблицы, один отчёт

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

select id, customer_id from orders;
-- затем — по одному разу на каждую возвращённую строку:
select count(*) from order_items where order_id = 42;

Решение

select o.id,
       c.email,
       count(i.id) as item_count
from orders o
join customers c on c.id = o.customer_id
left join order_items i on i.order_id = o.id
group by o.id, c.email
order by o.id;

База сводит три набора за один проход, а не возвращается к ним по разу на каждый заказ, и внешнее соединение сохраняет заказ без позиций: подсчёт по собственному столбцу позиции даёт для него ноль, а не единицу, потому что подсчёт столбца игнорирует пустые значения, а подсчёт строк — нет. Обычно же ошибаются с count(*): он считает строку-заглушку внешнего соединения и сообщает об одной позиции для пустого заказа.

Пример 2 — Итог, который размножился

Итог по возвратам вышел втрое больше нужного. Запрос соединяет заказы с их позициями, а затем суммирует, и именно это соединение дало для одного заказа результат ниже. Назовите отношение, которое размножило строки, и напишите инструкцию, агрегирующую до соединения.

select o.id, o.total, i.price from orders o
join order_items i on i.order_id = o.id
where o.id = 42;                                 (пояснительно)

 id | total | price
 42 | 90.00 | 30.00
 42 | 90.00 | 30.00
 42 | 90.00 | 30.00

Решение

-- сторона «многие» сводится к одной строке на заказ до соединения
select o.id, o.total, t.items_total
from orders o
join (select order_id, sum(price) as items_total
      from order_items
      group by order_id) t on t.order_id = o.id;

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