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

Вариант с условием в WHERE исключает клиента с id = 2: для него после LEFT JOIN поля заказа равны NULL, а условие o.status = 'paid' не является истинным. Фактически такой запрос теряет главное свойство внешнего соединения и по этому условию ведёт себя как INNER JOIN.

Вариант с условием в ON сохраняет клиента id = 2, подставляя ему NULL вместо данных заказа. Условие в ON определяет, какие строки считаются совпавшими, а условие в WHERE фильтрует уже сформированный результат соединения.

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

Внешние соединения нужны для задач, где требуется сохранить строки одной таблицы даже при отсутствии соответствия в другой: клиентов без заказов, товары без продаж или сотрудников без назначенных задач. Обычный INNER JOIN такие строки удаляет, поэтому SQL получил механизм LEFT JOIN, RIGHT JOIN и FULL JOIN.

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

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

После LEFT JOIN для клиента id = 2 не находится заказ со статусом paid. СУБД всё равно создаёт строку результата, но значения столбцов o в ней равны NULL.

Затем выражение o.status = 'paid' вычисляется как UNKNOWN, а не как TRUE: сравнение NULL с любым значением не даёт истину. WHERE оставляет только строки, для которых условие истинно, поэтому клиент исчезает.

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

Логически запрос с LEFT JOIN обрабатывается примерно так:

Для каждой строки clients ищутся совпадения по условию ON.
Если совпадений нет, создаётся одна строка с NULL в столбцах правой таблицы.
Выполняется фильтрация WHERE.
Формируется результирующий набор.

В первом варианте статус фильтруется после сохранения строк без совпадений. Поэтому результат содержит только строку (where, 1, paid).

Во втором варианте статус входит в условие соединения. Заказ со статусом cancelled не считается совпадением, но сам клиент сохраняется благодаря LEFT JOIN. Результат содержит (on, 1, paid) и (on, 2, NULL).

Эквивалентная запись первого варианта через внутреннее соединение выглядит так:

SELECT c.id, o.status
FROM clients c
JOIN orders o
  ON o.client_id = c.id
WHERE o.status = 'paid';

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

Если фильтр относится только к левой таблице, например c.id > 10, его размещение в ON и WHERE для LEFT JOIN также может отличаться по смыслу: в ON он влияет на поиск совпадения, но не обязательно удаляет левую строку, а в WHERE удаляет её из результата.

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

В отчёте требовалось показать всех клиентов и дату последнего успешного платежа. Разработчик добавил WHERE p.status = 'success' после LEFT JOIN таблицы платежей. Клиенты без успешных платежей пропали, из-за чего отчёт занижал число клиентов.

Рассматривались два варианта. Перенос фильтра в ON был простым и сохранял клиентов без платежей, но при наличии нескольких успешных платежей мог вернуть несколько строк на клиента. Коррелированный подзапрос с MAX возвращал одну строку на клиента, однако мог быть менее очевидным для сопровождения и требовал проверки плана выполнения на больших данных.

Выбрали предварительную агрегацию платежей в производной таблице и затем LEFT JOIN к ней:

SELECT c.id, p.last_success
FROM clients c
LEFT JOIN (
    SELECT client_id, MAX(paid_at) AS last_success
    FROM payments
    WHERE status = 'success'
    GROUP BY client_id
) p ON p.client_id = c.id;

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

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

Что произойдёт, если заменить условие o.status = 'paid' на o.status IS NULL в WHERE?

Такой фильтр, наоборот, оставит клиентов, для которых после LEFT JOIN нет заказа со статусом, удовлетворяющим условиям ON. Но это не всегда означает отсутствие заказов вообще: у клиента могут быть заказы, отсеянные условием в ON. Например, если в ON указано o.status = 'paid', клиент с одним cancelled-заказом будет выглядеть как клиент без подходящего заказа.
Можно ли безопасно перенести предикат из WHERE в ON при INNER JOIN?

Для обычного INNER JOIN условия в ON и WHERE обычно задают одинаковый результат, если речь идёт о предикате без побочных эффектов и с обычной логикой SQL. Внутреннее соединение всё равно оставляет только строки, имеющие совпадение и проходящие фильтр. Для внешнего соединения это преобразование уже не является семантически нейтральным.
Почему проверка WHERE o.status <> 'paid' не находит строки с NULL?

Потому что SQL использует трёхзначную логику. Для NULL выражение o.status <> 'paid' также даёт UNKNOWN, а WHERE пропускает только TRUE. Чтобы включить строки без заказа, условие нужно записать явно, например o.status <> 'paid' OR o.status IS NULL, учитывая при этом, какие строки были отфильтрованы в ON.