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

Условие

При каких типах соединения условие из WHERE можно перенести в ON с гарантированным сохранением результата запроса?

Решение

Только для INNER JOIN (и CROSS JOIN с явным условием — фактически тот же INNER).

Для OUTER JOIN-ов (LEFT/RIGHT/FULL) перенос условия из WHERE в ON меняет смысл запроса.

Почему так

ON определяет, какие пары строк считаются совпадением. После ON для outer-join добавляются «висячие» строки ведущей таблицы с NULL в полях ведомой. И только потом применяется WHERE.

Пример. LEFT JOIN

-- Вариант 1: условие в WHERE
SELECT u.id, o.id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid';

Это эквивалентно INNER JOIN — пользователи без заказов отсеются, потому что o.status у них NULL, и NULL = 'paid' → UNKNOWN → строка не проходит фильтр.

-- Вариант 2: то же условие, но в ON
SELECT u.id, o.id
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.id
 AND o.status = 'paid';

Здесь пользователи без оплаченных заказов останутся в результате с NULL в полях o. То есть результат принципиально отличается.

Для INNER JOIN

SELECT *
FROM a INNER JOIN b ON a.id = b.a_id
WHERE b.flag = 1;
 
-- эквивалентно
SELECT *
FROM a INNER JOIN b ON a.id = b.a_id AND b.flag = 1;

Оба запроса дадут одинаковый набор строк, потому что INNER JOIN не оставляет «висячих» — фильтр и в ON, и в WHERE срабатывает одинаково.

Подводные камни

1. «Оптимизировать» перенося WHERE в ON для LEFT JOIN — самая частая ошибка джунов. Потеряете строки или получите несовпадающий счёт пользователей/заказов.
2. Условие на ведущую таблицу. Даже для outer-join: условие на левую (ведущую) таблицу в ON не отфильтрует её строки — они останутся со связкой NULL справа. В WHERE они отсеются. Это разные запросы.
3. IS NULL и outer-join. Условие WHERE o.id IS NULL после LEFT JOIN — это идиома «найти строки без пары». Перенесёте в ON — потеряете антипаттерн.

Эталонный ответ

Только при INNER JOIN. Для LEFT/RIGHT/FULL JOIN перенос меняет результат.