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

В условии соединения фильтр определяет, какие строки правой таблицы могут сопоставиться с каждой строкой левой, не удаляя саму левую строку. Фильтр после внешнего соединения применяется уже к результату: если для несопоставленной строки справа стоят дополненные значениями NULL, предикат обычно становится UNKNOWN, и строка удаляется.

Поэтому перенос условия между этими местами может превратить LEFT JOIN по смыслу в соединение, похожее на INNER JOIN.

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

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

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

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

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

Если условие активности применить после LEFT JOIN, строки без заказа будут содержать справа NULL. Проверка активности для такого значения не даст TRUE, поэтому строка клиента исчезнет. Это нарушит требование «показать всех клиентов».

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

Условие в ON участвует в сопоставлении строк. Для каждой строки левой таблицы СУБД ищет справа только строки, удовлетворяющие этому условию; если подходящих строк нет, левая строка сохраняется, а правые атрибуты заполняются значениями NULL.

Условие в WHERE применяется после формирования результата внешнего соединения. В SQL строка проходит фильтр только при результате TRUE; результаты FALSE и UNKNOWN отбрасываются. Поэтому предикат по правому столбцу для дополненной строки обычно исключает её.

SELECT c.id, o.id, o.status
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id
 AND o.status = 'active';

Здесь клиент без активного заказа сохраняется. Если проверку o.status = 'active' перенести в WHERE, такой клиент будет исключён, поскольку у него o.status имеет значение NULL.

Для INNER JOIN размещение предиката, относящегося к соединяемым таблицам, обычно не меняет логический результат, хотя оптимизатор может выбрать разные эквивалентные планы. Для LEFT JOIN, RIGHT JOIN и FULL OUTER JOIN место условия является частью семантики запроса.

Иногда фильтр после соединения нужен намеренно: например, требуется оставить только клиентов, у которых действительно есть активный заказ. Тогда изменение результата с сохранением всех клиентов на отбор только сопоставленных клиентов является не ошибкой, а явным бизнес-условием.

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

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

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

Ещё один вариант — заранее отфильтровать заказы во вложенном запросе или CTE, а затем соединить их с магазинами. Он явно отделяет подготовку данных от соединения, но может быть менее удобен для простого запроса. На практике выбирают ON или предварительную фильтрацию по читаемости и сложности дальнейшей логики, проверяя план выполнения на больших объёмах данных.

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

Всегда ли перенос условия из ON в WHERE меняет результат?

Нет. Для INNER JOIN предикат, использующий только соединяемые таблицы, обычно логически эквивалентен в ON и WHERE. Существенная разница появляется у внешних соединений, потому что они сначала сохраняют несопоставленные строки и дополняют их справа значениями NULL.

Как сохранить строки без совпадения, если фильтр уже должен находиться в WHERE?

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

Что произойдёт при фильтрации столбца левой таблицы в ON у LEFT JOIN?

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