Запрос из 2х таблиц

Запрос из двух таблиц

JOIN сопоставляет строки таблиц по условию.

SELECT u.id AS user_id,
       u.email,
       o.id AS order_id,
       o.total
FROM users AS u
JOIN orders AS o
  ON o.user_id = u.id;

Пользователь с тремя заказами появится три раза. Это не дубликаты SQL, а правильная кардинальность отношения «один ко многим».

INNER JOIN

Возвращает только пары, для которых условие ON истинно.

SELECT o.id, i.sku, i.quantity
FROM orders AS o
INNER JOIN items AS i
  ON i.order_id = o.id;

LEFT JOIN

Сохраняет все строки слева; при отсутствии пары столбцы справа равны NULL.

SELECT u.id, COUNT(o.id) AS order_count
FROM users AS u
LEFT JOIN orders AS o
  ON o.user_id = u.id
GROUP BY u.id;

Нужно считать o.id, а не COUNT(*): у пользователя без заказов все равно есть одна дополненная строка.

ON и WHERE при LEFT JOIN

-- Сохраняет пользователей без оплаченных заказов.
LEFT JOIN orders AS o
  ON o.user_id = u.id
 AND o.state = 'PAID'

-- Отбрасывает строки, где o отсутствует:
-- фактически остаются только пользователи с paid-заказом.
LEFT JOIN orders AS o ON o.user_id = u.id
WHERE o.state = 'PAID'

Опасность размножения

Если одновременно присоединить items и еще одну связь «один ко многим», число строк может стать произведением количеств. Перед SUM нужно понимать гранулярность: иногда дочерние наборы агрегируют отдельно и только потом соединяют.

Ловушки

• Пропущенное условие ON создает декартово произведение.
• SELECT * дает неоднозначные имена и ненужные данные.
• FK не гарантирует, что индекс на дочернем ключе уже есть.
• JOIN не гарантирует порядок результата.

Связано: INNER JOIN, OUTER JOIN, Многотабличные запросы операция JOIN, Проблема декартова произведения Cartesian product problem в Hibernate.

Вопросы

Почему пользователь повторяется? Каждой подходящей паре строк соответствует строка результата.

Чем INNER отличается от LEFT? LEFT сохраняет несовпавшие строки левой таблицы.

Почему фильтр правой таблицы в WHERE опасен? Он удаляет NULL-дополненные строки и меняет смысл outer join.

Как считать детей после LEFT JOIN? COUNT(child.id).

Как избежать умножения сумм? Проверить кардинальность и при необходимости предварительно агрегировать каждую связь.