В чем разница между условиями в `ON` и `WHERE` при использовании `JOIN` в SQL?

Ответ

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

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

Для INNER JOIN разница часто незаметна: Оптимизатор СУБД может обработать оба запроса одинаково, так как INNER JOIN оставляет только строки, удовлетворяющие всем условиям.

-- Эти запросы вернут одинаковый результат
SELECT u.name, o.product
FROM users u
INNER JOIN orders o ON u.id = o.user_id AND o.status = 'completed';

SELECT u.name, o.product
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed';

Ключевое различие проявляется в OUTER JOIN (например, LEFT JOIN):

Представим, что мы хотим получить всех пользователей и их заказы, если они есть.

Условие в ON (Правильный способ): Фильтрует записи из правой таблицы (orders) перед присоединением. Если у пользователя нет заказов со статусом completed, он все равно останется в результате, а поля заказа будут NULL.
```
SELECT u.name, o.product
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed';
```
Условие в WHERE (Неправильный способ, если нужны все пользователи): Сначала LEFT JOIN создает набор, где для пользователей без заказов поля orders равны NULL. Затем WHERE o.status = 'completed' отфильтровывает все строки, где это условие ложно, включая те, где o.status равен NULL. В итоге LEFT JOIN эффективно превращается в INNER JOIN.
```
SELECT u.name, o.product
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed'; -- Отфильтрует пользователей без заказов
```

Итог:

Условия, определяющие связь между таблицами, помещайте в ON.
Условия, фильтрующие итоговый результат, помещайте в WHERE.