Соединить три таблицы модели «Ромашки» и предсказать результат: найти строки без пары через LEFT JOIN … IS NULL и не попасть в размножение строк при соединении один-ко-многим.
Соединяем и не теряем голову
Три таблицы, «сироты» и размножение строк
Короткое повторение — для тех, кто не проходил курс «Аналитика данных с нуля». INNER JOIN оставляет только строки, у которых нашлась пара. LEFT JOIN оставляет все строки левой таблицы (той, что записана до LEFT JOIN), а у строк без пары поля правой таблицы равны NULL.
Зачем SQL системному аналитику: проверить модель и требования на настоящих данных, не дожидаясь разработчика.
Три таблицы. У каждого JOIN своё условие ON по паре «внешний ключ → первичный ключ»:
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
LEFT JOIN couriers k ON k.courier_id = o.courier_id
Курьеров присоединяем через LEFT: у новых заказов курьера ещё нет, и INNER JOIN молча выбросил бы такие заказы.
Анти-соединение находит «сирот» и пропуски — строки левой таблицы без пары:
SELECT o.order_id FROM orders o
LEFT JOIN payments p ON p.order_id = o.order_id
WHERE p.payment_id IS NULL
Результат — заказы без единой записи об оплате. NULL проверяют только через IS NULL: условие «= NULL» не бывает истинным никогда, даже для NULL.
Размножение строк. Соединили orders (одна строка на заказ) с order_items (N позиций) — строка заказа повторится N раз. SUM(o.total_amount) завысит выручку, COUNT(*) посчитает позиции, а не заказы. Лекарство: заказы считать через COUNT(DISTINCT o.order_id), а суммы заказов — до соединения с позициями.
COUNT(*) считает все строки, COUNT(поле) — только строки, где поле не NULL.
Ловушки JOIN
| Ловушка | Симптом | Как правильно |
|---|---|---|
| INNER вместо LEFT | пропали заказы без курьера | LEFT JOIN |
| = NULL | ноль строк | IS NULL |
| SUM после соединения 1:N | сумма больше настоящей | считать суммы до JOIN |
| COUNT(*) после соединения 1:N | считаются позиции, а не заказы | COUNT(DISTINCT o.order_id) |
Конспект урока
Три таблицы, «сироты» и размножение строк
Короткое повторение — для тех, кто не проходил курс «Аналитика данных с нуля». INNER JOIN оставляет только строки, у которых нашлась пара. LEFT JOIN оставляет все строки левой таблицы (той, что записана до LEFT JOIN), а у строк без пары поля правой таблицы равны NULL.
Зачем SQL системному аналитику: проверить модель и требования на настоящих данных, не дожидаясь разработчика.
Три таблицы. У каждого JOIN своё условие ON по паре «внешний ключ → первичный ключ»:
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
LEFT JOIN couriers k ON k.courier_id = o.courier_id
Курьеров присоединяем через LEFT: у новых заказов курьера ещё нет, и INNER JOIN молча выбросил бы такие заказы.
Анти-соединение находит «сирот» и пропуски — строки левой таблицы без пары:
SELECT o.order_id FROM orders o
LEFT JOIN payments p ON p.order_id = o.order_id
WHERE p.payment_id IS NULL
Результат — заказы без единой записи об оплате. NULL проверяют только через IS NULL: условие «= NULL» не бывает истинным никогда, даже для NULL.
Размножение строк. Соединили orders (одна строка на заказ) с order_items (N позиций) — строка заказа повторится N раз. SUM(o.total_amount) завысит выручку, COUNT(*) посчитает позиции, а не заказы. Лекарство: заказы считать через COUNT(DISTINCT o.order_id), а суммы заказов — до соединения с позициями.
COUNT(*) считает все строки, COUNT(поле) — только строки, где поле не NULL.