|
Professor Seleznov
|
Задача знакомая, очень знакомая. Нужна выгрузка: все клиенты и их заказы за апрель. Все — значит все, включая тех, кто за месяц так ничего и не купил. Отсюда 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 и баз данных вам близка, приходите и на бесплатные открытые уроки:
Больше открытых уроков и материалов по backend, SQL и инфраструктуре публикуем на канале OTUS в MAX.-Источник
|
