Куда пропали клиенты после LEFT JOIN в PostgreSQL

Один фильтр по статусу заказа даёт разный отчёт в ON и в WHERE. На трёх покупателях проверим, куда попадают клиент без заказов и клиент с отменой.

Обновлено

Магазину нужен отчёт по всем клиентам с количеством оплаченных заказов. У тех, кто ничего не оплатил, должен стоять ноль. Для такого отчёта условие o.status = 'paid' поставь в ON: так оно определит, какие заказы присоединить к клиенту. В WHERE тот же фильтр удалит клиентов, у которых после соединения нет строки со статусом paid.

Три клиента, которые различают два запроса

Вера оплатила два заказа, Лев отменил единственный, Ада ещё ничего не заказывала. Создай временные таблицы и выполняй запросы в одном подключении PostgreSQL.

CREATE TEMP TABLE clients (id int PRIMARY KEY, name text);
CREATE TEMP TABLE orders (id int PRIMARY KEY, client_id int, status text);
INSERT INTO clients VALUES (1, 'Вера'), (2, 'Лев'), (3, 'Ада');
INSERT INTO orders VALUES (11, 1, 'paid'), (12, 1, 'paid'), (13, 2, 'cancelled');

Сначала соедини таблицы по клиенту и отфильтруй результат.

SELECT c.name, count(o.id) AS paid_orders
FROM clients AS c
LEFT JOIN orders AS o ON o.client_id = c.id
WHERE o.status = 'paid'
GROUP BY c.id, c.name
ORDER BY c.id;

В отчёте одна строка: Вера, 2. У Льва была пара, но со статусом cancelled. Для Ады соединение создало строку с NULL в полях заказа. Ни для одной из этих строк условие в WHERE не дало TRUE.

Как вернуть нули в отчёт

SELECT c.name, count(o.id) AS paid_orders
FROM clients AS c
LEFT JOIN orders AS o ON o.client_id = c.id AND o.status = 'paid'
GROUP BY c.id, c.name
ORDER BY c.id;

Теперь в отчёте у Веры 2 оплаты, у Льва и Ады по 0. У Льва и Ады нет подходящей пары. Оба остаются в отчёте, а count(o.id) пропускает пустой идентификатор. Если заменить его на count(*), у каждого получится единица: строка клиента есть, даже когда заказа нет.

Проверь обходной вариант

В первом запросе замени фильтр на WHERE o.status = 'paid' OR o.id IS NULL. Ада вернётся, Лев по-прежнему пропадёт. Его отменённый заказ совпал по client_id, поэтому строка с пустым идентификатором для Льва вообще не создавалась. Именно для этого в тестовых данных нужны оба клиента с нулём оплат.

В уроке про LEFT JOIN можно применить тот же приём для поиска покупателей без заказов. Примеры проверены в PostgreSQL 17.9. Соединения таблиц в документации PostgreSQL