Краткий ответ

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

Исторический контекст

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

Разделение ON и WHERE отражает разные этапы обработки запроса. ON определяет, какие строки считаются совпавшими при соединении, а WHERE отбирает строки уже сформированного промежуточного результата.

Постановка проблемы

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

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

Подробное решение

Логический порядок можно упростить так: сначала формируются источники в FROM, выполняется соединение с учётом ON, затем результат фильтруется через WHERE. Это логическая модель; оптимизатор вправе физически менять порядок операций, если сохраняет наблюдаемый результат.

WITH customers(id) AS (VALUES (1), (2)),
     orders(customer_id, status) AS (VALUES (1, 'closed'))
SELECT 'ON' AS variant, c.id, o.status
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id AND o.status = 'open'
UNION ALL
SELECT 'WHERE', c.id, o.status
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id
WHERE o.status = 'open';

В первой части клиент с идентификатором 1 сохраняется, но получает NULL справа: заказ есть, однако он не подходит условию status = 'open'. Клиент с идентификатором 2 также сохраняется с NULL, потому что подходящего заказа нет.

Во второй части строки с NULL в o.status не проходят фильтр WHERE. Поэтому оба клиента исчезают. Для обычного предиката, который не принимает NULL как истинное значение, фильтр по правой таблице в WHERE часто превращает LEFT JOIN в эквивалент INNER JOIN.

Это не универсальное правило для любого выражения. Например, условие WHERE o.status IS NULL специально сохраняет строки без соответствия, а сложное условие с OR может иметь собственную семантику. Нужно анализировать не только расположение предиката, но и его поведение на NULL, количество совпадений и требуемый результат.

Ситуация из практики

В отчёте нужно было показать всех клиентов и количество их активных подписок. Сначала разработчик добавил проверку активности подписки в WHERE; клиенты без активных подписок исчезли, поэтому отчёт показывал только клиентов с ненулевым результатом.

Рассматривались два варианта. Перенос условия в ON сохранял клиентов без активных подписок и позволял получить нулевое количество, но требовал внимательно выбрать агрегат: COUNT(*) посчитал бы искусственную строку внешнего соединения, тогда как COUNT(subscription.id) считает только реальные подписки. Альтернативой был INNER JOIN, но он сразу нарушал требование показывать клиентов без подписок.

Выбрали фильтрацию активных подписок в ON и подсчёт по ненулевому идентификатору подписки. Это сохранило полный список клиентов и корректные нулевые значения; дополнительно проверили план и индексы по ключу клиента и признаку активности.

Что кандидаты часто упускают

Всегда ли условие по правой таблице в WHERE превращает LEFT JOIN в INNER JOIN?

Нет. Это зависит от предиката. Условие, которое допускает строки с NULL, например проверка IS NULL или специально составленное логическое выражение, может сохранить часть строк без соответствия. Поэтому корректнее говорить: обычный фильтр, требующий истинного значения правого столбца, устраняет null-дополненные строки.

Можно ли заменить условие в ON на условие в WHERE через OR-проверку NULL?

Не во всех случаях. При соединении по ключу LEFT JOIN может создать реальные строки справа, которые не удовлетворяют нужному статусу. Такие строки не имеют NULL в правых столбцах, поэтому выражение вроде проверки статуса с OR right.id IS NULL их удалит. Если же условие находится в ON, неподходящие строки не участвуют в соединении, и для левой строки может быть создана null-дополненная строка. Результаты различаются.

Как расположение предиката влияет на агрегаты?

Оно влияет на набор строк до агрегации. Фильтр в ON сохраняет левую строку и оставляет справа NULL, поэтому COUNT(right.id) даст ноль для отсутствующих совпадений. При фильтре в WHERE такая строка может исчезнуть полностью; кроме того, COUNT(*) и COUNT(right.id) ведут себя по-разному даже при фильтре в ON. Поэтому нужно отдельно определить, требуется ли считать всех клиентов, все строки соединения или только реальные записи правой таблицы.