LEFT JOIN теряет строки из-за WHERE — пример и исправление

SQL Studio / Разборы SQL / Почему LEFT JOIN теряет строки после WHERE

Причина: условие WHERE o.status = 'delivered' после LEFT JOIN отбрасывает строки, у которых справа нет совпадений. Для таких строк значения o.* равны NULL, а сравнение NULL = 'delivered' не считается истинным.

Данные: четыре клиента и шесть заказов

В бесплатной SQL Studio у Анны есть два доставленных заказа, у Бориса — один, у Веры — один. Данил пока ничего не заказал. Задача: вывести всех клиентов и их доставленные заказы, если они есть.

1. Неверный запрос: WHERE убрал Данила

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

Запрос даёт четыре строки с заказами 201, 203, 206 и 205. У Данила справа NULL, поэтому фильтр исключает его. Формально LEFT JOIN выполнен, но итог для нашей бизнес-задачи неверен.

2. Переносим условие в ON

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

Теперь появляется пятая строка — Данил, order_id = NULL. Условие в ON выбирает подходящие заказы для присоединения, но не удаляет клиентов без подходящего заказа.

Сравнение результата
Проверка	WHERE	ON
Строк в результате	4	5
Клиенты без доставок	Пропали	Сохранены
Данил	Нет	order_id = NULL

3. Если нужна ровно одна строка на клиента

Перенос условия в ON не гарантирует уникальности клиентов: Анна появится дважды, потому что доставок две. Для отчёта «клиент — количество доставок» сначала агрегируем заказы.

WITH delivered AS (
  SELECT customer_id, COUNT(*) AS delivered_n
  FROM orders
  WHERE status = 'delivered'
  GROUP BY customer_id
)
SELECT c.customer_id, c.name,
       COALESCE(d.delivered_n, 0) AS delivered_n
FROM customers AS c
LEFT JOIN delivered AS d ON d.customer_id = c.customer_id
ORDER BY c.customer_id;

Результат: Анна — 2, Борис — 1, Вера — 1, Данил — 0. Здесь одна строка на клиента и нет искусственного увеличения количества клиентов.

4. Когда WHERE после LEFT JOIN — правильное решение

Если бизнес-вопрос другой — «кто вообще не делал заказов?» — нужно, наоборот, отобрать строки без совпадений:

SELECT c.customer_id, c.name
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL
ORDER BY c.customer_id;

Получим только Данила. Проверяем гарантированно ненулевой идентификатор заказа. Если использовать поле, где NULL допустим у существующей строки, вывод может быть ошибочным.

Нет заказов ≠ нет доставленных заказов

Запрос выше ищет клиентов вообще без заказов. Клиент, у которого есть только отменённые или обрабатываемые заказы, в него не попадёт. Если вопрос — «у кого нет ни одной доставки?», ограничивайте совпадения статусом в ON и только потом ищите NULL у гарантированно ненулевого ключа заказа.

SELECT c.customer_id, c.name
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id AND o.status = 'delivered'
WHERE o.order_id IS NULL
ORDER BY c.customer_id;

На нынешней учебной базе обе проверки возвращают Данила, но на наборе с клиентом, имеющим только отменённый заказ, результаты будут разными. Проверьте именно такой контрпример.

Практика

В тренажёре есть похожая задача: посчитать количество сессий у каждого клиента, включая нули.

Задание: сессии, включая нули →Запустить эти запросы

Ещё: почему JOIN увеличивает суммы · как диагностировать дубли.

Официальная справка: SQLite SELECT, MySQL Outer Join Simplification.

Для последовательной практики на MySQL 8 — курс «SQL для аналитика» на Stepik с итоговой работой и возможностью получить сертификат.