LEFT JOIN есть, а клиенты пропали: как WHERE меняет результат соединения

В отчёте должны быть все клиенты, включая тех, у кого нет оплаченных заказов. Запрос использует LEFT JOIN, загрузка проходит без ошибок, но часть клиентов всё равно исчезает.

Причина может быть в одной строке: условие по правой таблице поставили в WHERE. А попытка исправить его через OR … IS NULL возвращает не всех, кого ожидали.

Разберём учебный пример: четыре клиента, заказы в двух статусах и витрина с одной строкой на клиента. Посмотрим, где теряются строки, почему COUNT(*) показывает несуществующий заказ и как проверить не только сумму, но и состав результата. Все SQL-примеры ниже проверены в SQLite. Это пример логики данных, а не история из проекта.

Что именно должен показывать отчёт

Пусть задача сформулирована так: показать каждого клиента, количество его оплаченных заказов и их общую сумму. Клиент без оплаченных заказов должен остаться с нулевыми показателями.

В примере paid — условный статус заказа. Отдельные платежи, возвраты, частичные оплаты и бухгалтерская выручка не моделируются. Суммы хранятся целыми числами в копейках; валюта одна.

Вот данные для пустой базы:

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status TEXT NOT NULL,
    amount_kopecks INTEGER NOT NULL
);

INSERT INTO customers VALUES
    (1, 'Анна'),
    (2, 'Борис'),
    (3, 'Вера'),
    (4, 'Глеб');

INSERT INTO orders VALUES
    (101, 1, 'paid', 10000),
    (102, 1, 'cancelled', 5000),
    (103, 2, 'cancelled', 7000),
    (104, 4, 'paid', 20000),
    (105, 4, 'paid', 30000);

У Анны есть оплаченный и отменённый заказы. У Бориса — только отменённый. У Веры нет заказов вообще. У Глеба два оплаченных заказа.

Для итоговой витрины ожидаем четыре строки: у Анны один оплаченный заказ на 100 рублей, у Глеба — два на 500 рублей. У Бориса и Веры — нули. Разница между последними двумя клиентами скоро окажется важной.

LEFT JOIN не отменяет фильтр в WHERE

Начнём с запроса, который выглядит естественно:

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

В результате будут только Анна и Глеб. Борис и Вера исчезнут по разным причинам.

У Бориса соединение нашло заказ 103. Но его статус cancelled, поэтому строка не проходит WHERE.

Для Веры соединение не нашло ни одного заказа. LEFT JOIN добавляет для неё строку, в которой поля правой стороны заполнены NULL. Однако условие NULL = ‘paid’ не истинно: результат сравнения — NULL. WHERE такую строку тоже исключает.

По смыслу результата именно этот запрос оставляет только клиентов с оплаченной строкой справа, как соответствующий INNER JOIN. Это не утверждение, что любой WHERE превращает LEFT JOIN во внутреннее соединение: всё зависит от условия. Фильтр по самой таблице клиентов может быть совершенно уместным.

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

Почему OR … IS NULL исправляет только часть проблемы

Добавим условие, которое должно «вернуть клиентов без заказа»:

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 = 'paid' OR o.order_id IS NULL
ORDER BY c.customer_id, o.order_id;

Вера вернётся, а Борис — нет.

У Веры нет совпадений по ON, поэтому возникает строка с o.order_id = NULL. Вторая часть WHERE её пропускает.

У Бориса совпадение есть: реальный отменённый заказ. Его order_id равен 103, а не NULL. Обе части WHERE не выполняются, и клиент исчезает.

LEFT JOIN не создаёт дополнительную пустую строку после того, как WHERE удалил все найденные заказы. Пустая строка появляется для отсутствующих совпадений по условию соединения. Это другое событие.

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

Отбирать заказы нужно в условии соединения

Для нашей задачи критерий совпадения состоит из двух частей: заказ принадлежит клиенту и имеет нужный статус.

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

Теперь Борис и Вера останутся. Для обоих нет подходящего заказа по полному условию ON, поэтому справа будут NULL.

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

Тот же принцип можно реализовать предварительным отбором заказов в подзапросе. Важно, что набор подходящих заказов определяется до решения о том, есть ли совпадение у клиента.

Но переносить все условия из WHERE в ON механически нельзя. Сначала нужно решить, кого должен сохранять отчёт. Если бизнесу нужны только клиенты с оплатой, их исключение из результата может быть правильным — тогда это другая задача.

Следующая ловушка: COUNT(*) считает и пустую строку справа

Сгруппируем результат и сравним два счётчика:

SELECT
    c.customer_id,
    COUNT(*) AS joined_rows,
    COUNT(o.order_id) AS paid_order_count,
    SUM(o.amount_kopecks) AS paid_amount_kopecks
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
   AND o.status = 'paid'
GROUP BY c.customer_id
ORDER BY c.customer_id;

У Анны оба счётчика равны 1. У Глеба оба равны 2.

А у Бориса и Веры joined_rows равен 1, paid_order_count — 0, сумма — NULL.

COUNT(*) считает строки результата соединения, включая сохранённую строку клиента с NULL справа. COUNT(o.order_id) считает только ненулевые значения идентификатора заказа. В нашем наборе order_id — первичный ключ INTEGER PRIMARY KEY, поэтому у реального заказа он не бывает NULL.

Для другой схемы нельзя без проверки заменить идентификатор произвольным полем: если это поле допускает NULL у существующей записи, счётчик пропустит и её.

Собираем витрину с обещанной детализацией

Теперь оформим расчёт в представление:

CREATE VIEW customer_paid_mart AS
SELECT
    c.customer_id,
    c.name,
    COUNT(o.order_id) AS paid_order_count,
    COALESCE(SUM(o.amount_kopecks), 0) AS paid_amount_kopecks
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
   AND o.status = 'paid'
GROUP BY c.customer_id, c.name;

SELECT
    customer_id, name, paid_order_count, paid_amount_kopecks
FROM customer_paid_mart
ORDER BY customer_id;

Получим:

• Анна: 1 заказ, 10000 копеек;

• Борис: 0 заказов, 0 копеек;

• Вера: 0 заказов, 0 копеек;

• Глеб: 2 заказа, 50000 копеек.

Здесь COALESCE реализует правило учебной задачи: нет подходящего заказа — сумма равна нулю. Это правило не доказывает полноту загрузки. Если заказы не доехали в хранилище, такой же ноль может означать отсутствие данных, а не отсутствие покупок. В рабочей витрине для этого нужен отдельный контроль полноты поставки.

Отчёт также намеренно больше не различает Бориса и Веру по наличию отменённых заказов. Если это различие нужно потребителю, потребуется отдельный показатель или другой набор данных. Фильтр не должен незаметно менять смысл признака «есть заказы» на «есть оплаченные заказы».

Правильная денежная сумма ещё не означает правильный отчёт

На наших данных и ошибочный вариант с WHERE, и правильная витрина дадут общую сумму оплаченных заказов 60000 копеек. Исчезнувшие клиенты не добавляли в неё ничего.

Поэтому сверка только денежного итога не обнаружит потерю клиентов. Нужна проверка состава результата.

SELECT customer_id
FROM customers
EXCEPT
SELECT customer_id
FROM customer_paid_mart;

Ожидаемый результат — пустой набор. Если подставить сюда витрину, построенную с ошибочным WHERE, проверка вернёт клиентов 2 и 3.

Уникальность проверяется отдельно:

SELECT customer_id, COUNT(*) AS row_count
FROM customer_paid_mart
GROUP BY customer_id
HAVING COUNT(*) <> 1;

Результат тоже должен быть пустым. Проверка уникальности сама по себе не ловит пропавшего клиента: отсутствующий ключ не образует группу. Сверка состава и сверка количества повторов отвечают на разные вопросы.

Полезный минимальный набор данных для такой задачи включает клиента без заказов, клиента только с неподходящими заказами, клиента с одним подходящим заказом и клиента с несколькими. Именно случай «заказы есть, но ни один не подходит» отличает корректное решение от OR … IS NULL.

А если нужны только клиенты без оплаченных заказов?

Это уже другой отчёт. Его намерение можно явно записать через NOT EXISTS:

SELECT c.customer_id, c.name
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
      AND o.status = 'paid'
)
ORDER BY c.customer_id;

Получим Бориса и Веру: наличие отменённого заказа не мешает попасть в список. Мы проверяем отсутствие оплаченных заказов, а не отсутствие любых заказов.

Здесь NOT EXISTS выбран ради ясности условия. Это не обещание, что он всегда быстрее другого корректного варианта: производительность нужно проверять на своей базе, данных и индексах.

Что проверить перед выдачей отчёта

Рядом с запросом стоит зафиксировать три вещи: кого он обязан сохранить, какие записи справа считаются подходящими и что означает ноль в результате.

После этого легче заметить ошибку. Условие ON определяет совпадения, WHERE отбирает уже полученный результат, а COUNT(*) не знает, что сохранённую строку клиента нельзя считать заказом.

Само наличие LEFT JOIN в тексте запроса ещё не гарантирует, что нужные клиенты дойдут до отчёта.