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

Пишете запрос. Добавляете WHERE orders.created_at >= '2024-04-01', чтобы отрезать всё, что не апрель. Запускаете.

В таблице клиентов восемь тысяч строк. В выгрузке почему‑то шесть. Две тысячи человек куда‑то испарились. И не абы какие: пропали ровно те, ради кого вы и городили LEFT JOIN — клиенты без заказов.

Неприятнее всего тут не сама пропажа. Неприятнее то, что запрос выглядит безупречно. Вы смотрите на него и не видите ошибки. Её там и нет, в смысле синтаксиса. База не ругнулась, ничего красным не подсветилось. Просто строк меньше, чем нужно, а это глазами не ловится: выгрузка не пустая, шесть тысяч живых записей, всё выглядит настоящим.

Сейчас разберёмся, куда ушли эти две тысячи.

С LEFT JOIN всё в порядке, правда

Снимем подозрение с главного обвиняемого сразу.

LEFT JOIN делает ровно то, что обещает. Берёт каждую строку левой таблицы. Ищет ей пару справа. Нашёл — склеил. Не нашёл, и вот тут самое важное, строку всё равно оставил, просто колонки правой таблицы забил NULL‑ами.

Клиент без заказов никуда не девается. Он доезжает до результата живым, только вместо данных о заказе у него пустота.

То есть LEFT JOIN свою часть выполнил. Все клиенты были на месте. А потом пришёл WHERE.

Запрос читается не так, как выполняется

Мы читаем SQL сверху вниз: сначала SELECT, потом FROM, потом WHERE. И кажется логичным, что выполняется он в том же порядке. Не выполняется.

База сначала берётся за FROM и JOIN. Склеивает таблицы, строит промежуточный результат. И только когда он готов, по нему проходит WHERE и выкидывает ненужные строки.

Все идет к моменту, когда WHERE берётся за дело, LEFT JOIN давно закончил. В промежуточном результате уже лежат наши клиенты без заказов — с честными NULL в колонках orders.

И вот WHERE смотрит на такую строку через своё условие:

SELECT c.id, c.name, o.id AS order_id, o.created_at
FROM clients c
LEFT JOIN orders o ON o.client_id = c.id
WHERE o.created_at >= '2024-04-01';

У клиента без заказов o.created_at — это NULL. И вопрос на сообразительность: NULL >= '2024-04-01' — это правда или ложь?

Ни то ни другое. Это UNKNOWN, «неизвестно», третье значение в логике SQL. Сравнить дату с тем, чего нет, нельзя, вот база и разводит руками.

А WHERE пускает дальше только то, что строго TRUE. UNKNOWN для него — то же самое, что FALSE: на выход. И каждая строка клиента без заказов тихо вылетает.

Что остаётся? Только клиенты с заказом, да ещё подходящим по дате. То есть ровно то, что вернул бы INNER JOIN. Вы написали LEFT, получили INNER, и никто вас не предупредил.

Почему это так легко проглядеть

Синтаксис чистый, оптимизатор доволен. Результат не пустой, в нём тысячи строк, и каждая выглядит как надо. Чтобы заподозрить неладное, нужно заранее знать, сколько строк ты ждёшь, и сверить. А кто это делает каждый раз?

В код‑ревью такое часто пропускается. Коллега видит LEFT JOIN, видит понятный фильтр по дате, ставит апрув. Всё же логично написано.

А отчёт потом не падает, он просто врёт. «Клиентов, которые в апреле ничего не купили, у нас нет», красивый вывод из запроса, где этих клиентов вырезали ещё до того, как кто‑то взялся их считать.

ON и WHERE спрашивают у базы разное

Чинится всё переносом одного условия. Из WHERE — в ON:

SELECT c.id, c.name, o.id AS order_id, o.created_at
FROM clients c
LEFT JOIN orders o
  ON o.client_id = c.id
  AND o.created_at >= '2024-04-01';

С виду — косметика, условие переехало на две строчки выше. На деле поменялось всё.

Условие в ON — это часть правила, по которому ищется пара. Оно отвечает на вопрос «что вообще считать совпадением». Теперь заказ подходит клиенту, только если совпал и по client_id, и по апрелю. У клиента без апрельских заказов пары нет, и LEFT JOIN, как мы уже выяснили, в таком случае оставляет строку с NULL. А WHERE её больше не трогает: фильтра по дате в WHERE теперь просто нет. Клиент остаётся.

Условие в WHERE совсем другое. Это фильтр по уже готовому, склеенному результату. И вопрос здесь другой: «что оставить из того, что получилось». А NULL‑строкам в этом фильтре не выжить, любое сравнение с колонкой правой таблицы их срежет.

Вот и вся разница. ON и WHERE — не два кармана, куда можно сунуть одно и то же условие. Это два разных вопроса. ON спрашивает: что считать парой? WHERE спрашивает: что оставить в конце?

Иногда условие в WHERE — это правильно

Только не уносите отсюда правило «фильтр по правой таблице в WHERE — всегда плохо». Не всегда.

Нужны только клиенты с апрельскими заказами? Пишите INNER JOIN и живите спокойно. Маскировать INNER‑логику под LEFT JOIN с фильтром в WHERE плохо не потому, что не работает — работает. Плохо потому, что следующий человек прочитает LEFT JOIN и поверит ему.

А иногда NULL‑строка в WHERE нужна вам совершенно сознательно. Например, когда надо найти клиентов вообще без заказов:

SELECT c.id, c.name
FROM clients c
LEFT JOIN orders o ON o.client_id = c.id
WHERE o.id IS NULL;

LEFT JOIN притащил всех. WHERE o.id IS NULL оставил только тех, кому пара не нашлась. У всего этого есть имя — антиджойн, способ найти «то, чего нет».

И заметьте: работает он за счёт того же поведения, которое в начале статьи ломало нам выгрузку. LEFT JOIN создаёт NULL‑строки, WHERE умеет по ним фильтровать. Одно и то же свойство — то баг, то фича.

Разница в намерении. В антиджойне вы фильтруете по IS NULL, то есть прямо говорите «лови отсутствие пары». А в сломанном запросе фильтр по дате цепляет NULL‑строку случайно, как побочку, про которую автор и не думал.

Куда же делись две тысячи клиентов

Теперь понятно. LEFT JOIN их не терял — он добросовестно довёл всех до промежуточного результата, с NULL‑ами вместо заказов. Их выкинул WHERE: сравнил NULL с датой, получил UNKNOWN, а UNKNOWN для WHERE — это «не пускать».

С собой унести мысль смотреть на запрос: ON и WHERE задают базе разные вопросы. ON — «что здесь пара?». WHERE — «что оставить, когда всё склеилось?».

Как только эти вопросы перестанут сливаться в один, LEFT JOIN перестанет вас подводить.

И каждый раз, встретив в WHERE условие на колонку правой таблицы рядом с LEFT JOIN, вы поймаете себя на мысли: так, а вот тут LEFT только что превратился в INNER.

Кажется, что с SQL всё давно понятно, пока один WHERE внезапно не превращает LEFT JOIN в INNER JOIN. Если хотите проверить, насколько уверенно ориентируетесь в таких нюансах, попробуйте пройти бесплатный тест по SQL для разработчиков и аналитиков.

Если тема SQL и баз данных вам близка, приходите и на бесплатные открытые уроки:

• 27 мая, 19:00 — «SQL: Обобщенное табличное выражение (CTE) — как писать сложные запросы просто».
  _{Поговорим о том, как делать сложные SQL-запросы читаемыми и поддерживаемыми.}

• 2 июня, 20:00 — «Ты — индекс в Postgres, Я — индекс в ClickHouse. Мы — разные».
  _{Разберем, почему индексы в разных СУБД работают по-разному и как это влияет на производительность запросов.}

Больше открытых уроков и материалов по backend, SQL и инфраструктуре публикуем на канале OTUS в MAX.