Фильтрация в ON и WHERE при LEFT JOIN

Три случая совпадения и отсутствия данных при LEFT JOIN

Для изучения фильтрации при соединении подготовим базу с клиентами и заказами в SQLite:

CREATE TABLE clients (
    client_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    client_id INTEGER NOT NULL,
    status TEXT NOT NULL,
    amount INTEGER NOT NULL,
    FOREIGN KEY (client_id) REFERENCES clients(client_id)
);

INSERT INTO clients (client_id, name) VALUES
(1, 'Алиса'),
(2, 'Борис'),
(3, 'Вера'),
(4, 'Григорий');

INSERT INTO orders (order_id, client_id, status, amount) VALUES
(10, 1, 'paid', 500),
(11, 1, 'paid', 300),
(12, 1, 'new', 150),
(13, 2, 'new', 200),
(14, 4, 'paid', 100);

Выполним базовый LEFT JOIN без дополнительных условий по статусу:

SELECT c.client_id, c.name, o.order_id, o.status, o.amount
FROM clients c
LEFT JOIN orders o ON c.client_id = o.client_id
ORDER BY c.client_id, o.order_id;

Результат:

client_id	name	order_id	status	amount
1	Алиса	10	paid	500
1	Алиса	11	paid	300
1	Алиса	12	new	150
2	Борис	13	new	200
3	Вера	NULL	NULL	NULL
4	Григорий	14	paid	100

В этой выборке видны три разных исхода соединения:

• Полное отсутствие связанных строк: у Веры нет ни одной записи в таблице orders. LEFT JOIN сохраняет строку левой таблицы и заполняет все столбцы правой таблицы значениями NULL.
• Полное совпадение: у Бориса есть заказ 13, у Григория — заказ 14. Каждая строка находит соответствие по client_id.
• Размножение строк: у Алисы три заказа, поэтому в выводе появляются три строки с ее данными.

Когда бизнес-задача требует учесть только оплаченные закалы (status = 'paid'), но при этом вывести каждого клиента компании, возникает вопрос: в какой части запроса проверять статус?

Фильтрация в предложении ON

Чтобы ограничить строки правой таблицы, но сохранить всех клиентов из левой, дополнительное условие помещают внутрь предложения ON.

Синтаксис такого соединения:

SELECT c.client_id, c.name, o.order_id, o.status, o.amount
FROM clients c
LEFT JOIN orders o ON c.client_id = o.client_id AND o.status = 'paid'
ORDER BY c.client_id, o.order_id;

Результат запроса:

client_id	name	order_id	status	amount
1	Алиса	10	paid	500
1	Алиса	11	paid	300
2	Борис	NULL	NULL	NULL
3	Вера	NULL	NULL	NULL
4	Григорий	14	paid	100

Предикат в секции ON вычисляется в момент поиска пар для соединения. Механизм работает следующим образом:

• Для Веры заказов нет вообще. Условие c.client_id = o.client_id не выполняется ни для одной строки orders. Так как это LEFT JOIN, строка Веры сохраняется с NULL во всех полях заказа.
• Для Бориса заказ с order_id = 13 существует, но имеет статус 'new'. Предикат o.status = 'paid' возвращает FALSE. Составное условие ON не выполнено ни для одной строки orders. База данных считает, что подходящей правой строки нет, и формирует строку с NULL в полях заказа — точно так же, как для Веры.
• Для Алисы заказ 12 со статусом 'new' отсекается, а заказы 10 и 11 со статусом 'paid' удовлетворяют условию ON. Алиса получает две строки в результате, а строка с order_id = 12 отбрасывается без генерации NULL, поскольку совпадения найдены.

Предикат в ON определяет только то, какие именно строки правой таблицы подходят для присоединения. Он не исключает строки левой таблицы.

Ловушка фильтрации правой таблицы в WHERE

Частая ошибка — перенос фильтра по правой таблице в блок WHERE:

SELECT c.client_id, c.name, o.order_id, o.status, o.amount
FROM clients c
LEFT JOIN orders o ON c.client_id = o.client_id
WHERE o.status = 'paid'
ORDER BY c.client_id, o.order_id;

Результат:

client_id	name	order_id	status	amount
1	Алиса	10	paid	500
1	Алиса	11	paid	300
4	Григорий	14	paid	100

Клиенты Борис и Вера полностью исчезли из вывода. Запрос фактически сработал как INNER JOIN.

Причина кроется в логическом порядке выполнения операций. Блок WHERE применяется после того, как FROM и LEFT JOIN построили объединенную таблицу.

• До блока WHERE строка Веры содержит NULL в столбце o.status. Выражение NULL = 'paid' дает UNKNOWN. Секция WHERE оставляет только строки, для которых предикат равен TRUE. Строка отбрасывается.
• До блока WHERE строка Бориса содержит 'new' в столбце o.status. Выражение 'new' = 'paid' дает FALSE. Строка отбрасывается.

Любое равенство или неравенство вида o.column = value отсекает строки, где o.column равен NULL. Исключением служат предикаты, явно рассчитанные на работу с отсутствующими значениями, такие как WHERE o.order_id IS NULL (anti-join для поиска клиентов без заказов). Для отбора только тех заказов, которые должны войти в соединение без потери клиентов, условие обязательно должно находиться в ON.

Подсчёт совпадений: COUNT(ключа) против COUNT(*)

Когда к результату LEFT JOIN применяется группировка, возникает различие между COUNT(*) и подсчетом по первичному ключу правой таблицы.

Выполним агрегацию по клиентам:

SELECT 
    c.client_id, 
    c.name, 
    COUNT(*) AS total_rows, 
    COUNT(o.order_id) AS paid_count, 
    COALESCE(SUM(o.amount), 0) AS total_paid
FROM clients c
LEFT JOIN orders o ON c.client_id = o.client_id AND o.status = 'paid'
GROUP BY c.client_id, c.name
ORDER BY c.client_id;

Результат:

client_id	name	total_rows	paid_count	total_paid
1	Алиса	2	2	800
2	Борис	1	0	0
3	Вера	1	0	0
4	Григорий	1	1	100

Значения агрегатов объясняются правилами работы со значениями NULL:

• COUNT(*) считает физические строки, попавшие в группу. У Бориса и Веры после LEFT JOIN сформировалось по одной строке с пустыми полями заказа. Для каждого из них COUNT(*) возвращает 1. Использовать COUNT(*) для подсчета событий или заказов при внешнем соединении нельзя: он показывает наличие строки в таблице, а не наличие факта покупки.
• COUNT(o.order_id) проверяет конкретный столбец и игнорирует значения NULL. Так как у Бориса и Веры в столбце order_id находится NULL, функция возвращает 0.
• SUM(o.amount) при отсутствии строк со значениями (только NULL) возвращает NULL. Обертка COALESCE(SUM(o.amount), 0) заменяет NULL на числовой 0 для клиентов без подходящих заказов.

Проверка COUNT(ключ_правой_таблицы) = 0 подтверждает отсутствие совпадений по условию ON, в то время как COUNT(*) для каждого сохраненного клиента всегда будет равен минимум единице.