LEFT JOIN: условие в WHERE против ON

Любимый каверзный вопрос: что вернёт LEFT JOIN, если условие написать в WHERE вместо ON.

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

В чём подвох

Возьмём «всех клиентов и их заказы дороже 400». Кажется, что условие amount > 400 можно поставить хоть в ON, хоть в WHERE. Но результат будет разным — и это ломает логику LEFT JOIN.

Условие в WHERE — LEFT превращается в INNER

Если поставить фильтр по правой таблице в WHERE, строки клиентов без подходящих заказов получают NULL в amount, а потом WHERE amount > 400 их отсекает (NULL не больше 400). LEFT JOIN фактически вырождается в INNER.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO customers (id, name) VALUES (1,'Аня'), (2,'Борис'), (3,'Вера');

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, amount INTEGER);
INSERT INTO orders (customer_id, amount) VALUES (1,500), (1,300), (2,200);

-- Условие в WHERE: Вера (нет заказов) и Борис (заказ 200) пропадут
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.amount > 400
ORDER BY c.name;

Вывод:

Аня|500

Осталась только Аня с заказом 500. Веру (нет заказов) и Бориса (заказ 200, не > 400) фильтр в WHERE выкинул вместе с их NULL — внешняя часть LEFT JOIN потеряна.

Условие в ON — LEFT сохраняется

Если перенести amount > 400 в ON, оно влияет только на поиск пары. Клиенты без подходящего заказа всё равно останутся — с NULL вместо суммы.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO customers (id, name) VALUES (1,'Аня'), (2,'Борис'), (3,'Вера');

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, amount INTEGER);
INSERT INTO orders (customer_id, amount) VALUES (1,500), (1,300), (2,200);

-- Условие в ON: все клиенты остаются, подставляется только заказ > 400
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.amount > 400
ORDER BY c.name;

Вывод:

Аня|500
Борис|
Вера|

Теперь видны все три клиента. У Ани — заказ 500. У Бориса (заказ 200 не прошёл) и Веры (заказов нет) amount = NULL. Это и есть настоящий смысл «все клиенты + крупные заказы, если есть».

Правило

Условие на левую таблицу можно писать в WHERE — оно безопасно. А вот условие на правую (внешнюю) таблицу LEFT JOIN ставьте в ON, если хотите сохранить строки без пары. В WHERE такое условие превращает LEFT в INNER.

Итог

Фильтр по правой таблице в WHERE отсекает строки с NULL — LEFT JOIN вырождается в INNER.
Тот же фильтр в ON сохраняет все строки левой таблицы, подставляя NULL.
Хотите «все слева + опциональные данные справа по условию» — условие на правую таблицу пишите в ON.