SQL Ready | Базы Данных on Telegram: “Почему перенос условия из ON в WHERE меняет сема…”

Почему перенос условия из ON в WHERE меняет семантику LEFT JOIN

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

Рассмотрим запрос, который должен возвращать всех пользователей и количество их завершённых заказов:

SELECT
    u.id,
    COUNT(o.id) AS completed_orders
FROM users u
LEFT JOIN orders o
    ON o.user_id = u.id
WHERE o.status = 'completed'
GROUP BY u.id;

Этот запрос исключает пользователей без завершённых заказов. Предикат o.status = 'completed' является null-rejecting: он не может вернуть TRUE, когда o.status содержит NULL. Поэтому строки, дополненные NULL при внешнем соединении, отбрасываются.

Для сохранения всех пользователей ограничение на статус необходимо включить в условие соединения:

SELECT
    u.id,
    COUNT(o.id) AS completed_orders
FROM users u
LEFT JOIN orders o
    ON o.user_id = u.id
   AND o.status = 'completed'
GROUP BY u.id;

Теперь пользователь без завершённых заказов остаётся в результате с количеством 0. Здесь принципиально использование COUNT(o.id): агрегат игнорирует NULL, тогда как COUNT(*) учитывает и строку, созданную внешним соединением при отсутствии совпадений.

В PostgreSQL аналогичную задачу можно решить через агрегатный FILTER, сохранив соединение со всеми заказами:

SELECT
    u.id,
    COUNT(o.id) FILTER (
        WHERE o.status = 'completed'
    ) AS completed_orders
FROM users u
LEFT JOIN orders o
    ON o.user_id = u.id
GROUP BY u.id;

Различие между этими вариантами становится существенным при вычислении нескольких агрегатов. FILTER позволяет применять независимые условия к разным агрегатам без повторного соединения таблиц:

SELECT
    u.id,
    COUNT(o.id) FILTER (
        WHERE o.status = 'completed'
    ) AS completed_orders,
    COUNT(o.id) FILTER (
        WHERE o.status = 'pending'
    ) AS pending_orders
FROM users u
LEFT JOIN orders o
    ON o.user_id = u.id
GROUP BY u.id;

Есть и важное следствие для оптимизатора PostgreSQL. Если условие в WHERE исключает все строки с NULL со стороны правой таблицы, внешнее соединение в некоторых случаях может быть преобразовано во внутреннее. Такое преобразование сохраняет семантику исходного запроса, но не исправляет ошибочно выбранную логику фильтрации.

Проверить фактический план выполнения можно через EXPLAIN:

EXPLAIN (ANALYZE, BUFFERS)
SELECT
    u.id,
    COUNT(o.id)
FROM users u
LEFT JOIN orders o
    ON o.user_id = u.id
WHERE o.status = 'completed'
GROUP BY u.id;

В плане может появиться Hash Join вместо Hash Left Join или другой тип внутреннего соединения. Конкретный алгоритм зависит от статистики, индексов и оценок стоимости, поэтому наличие LEFT JOIN в sql не гарантирует сохранение внешнего соединения в физическом плане.

🔥 Перенос предиката между ON и WHERE при внешнем соединении — семантическое преобразование, а не просто изменение записи sql. Null-rejecting предикаты могут устранять строки без совпадений и создавать условия для упрощения LEFT JOIN до INNER JOIN оптимизатором.

➡️ SQL Ready | #практика