Информатика · объяснение и практика

Почему условие в WHERE может убрать незаполненную часть LEFT JOIN

Составляем список учеников с завершёнными работами, сравниваем фильтр ON и WHERE и объясняем, почему отсутствие совпадения превращается в NULL.

Синие фигурные скобки держат между собой оранжевый квадрат.

В теме Таблицы, базы данных и SQL · Учебная карта

Главная мысль

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

Сначала решите, кого нужно сохранить

В списке ученики Ася, Борис и Вера. У Аси одна завершённая работа, у Бориса только черновик, у Веры работ пока нет. Нужно показать всех троих, а справа — только завершённые работы, если они есть. Условие завершённости помещаем в ON вместе с совпадением идентификатора ученика.

Ася получит свою готовую работу. Борис не имеет подходящей завершённой записи, поэтому правая часть будет заполнена NULL. Вера получит такую же незаполненную правую часть. Оба ученика останутся в списке. NULL здесь означает отсутствие подходящего совпадения в результате соединения, а не обязательно отсутствие любых работ в исходной таблице.

Пример

При фильтре готовности в ON вывод содержит Асю с работой, Бориса с NULL и Веру с NULL.

У Бориса черновик существует, но не подходит условию соединения. Это отличается от утверждения, что у него вообще нет записей.

Что меняется после переноса условия

Теперь оставим в ON только совпадение ученика, а готовность перенесём в WHERE. Сначала Борис соединится с черновиком, а Вера получит NULL. Затем фильтр оставит только строки, где готовность равна требуемому значению. Черновик Бориса не пройдёт, а сравнение NULL с обычным значением не даст истину.

В результате останется только Ася. Сам LEFT JOIN всё ещё сохранил исходных учеников на своём этапе, но последующий WHERE их удалил. Поэтому выражение «левое соединение всегда показывает всех слева» нужно относить к результату самого соединения, а не к любому запросу с последующими фильтрами.

Почему добавление OR IS NULL бывает недостаточным

Попытка дописать в WHERE «готово или идентификатор работы IS NULL» сохранит Веру, у которой совпадений совсем нет. Но Борис уже сопоставлен с черновиком: его идентификатор работы не NULL, готовность ложна, значит, он опять исчезнет. Нужное поведение нельзя восстановить таким условием для всех ситуаций.

При требовании сохранить всех учеников и показать только готовые работы фильтр подходящих работ должен действовать при соединении или в заранее отфильтрованной правой таблице. Если у ученика несколько завершённых работ, он появится несколько раз: LEFT JOIN не гарантирует одну строку на ученика. Отдельно решают, нужен ли список всех работ, последняя работа или агрегированный итог. Эта детализация столь же важна, как место фильтра.

От чтения к действию

А теперь — вы

Ученик имеет только незавершённую работу. Нужно сохранить его в списке всех учеников, но показывать справа только завершённые работы. Где применить фильтр завершённости?

Частые вопросы

Всегда ли нужно переносить правые фильтры в ON?

Нет: если нужно вывести только учеников с готовой работой, отбор результата может быть намеренным. Место условия выбирают по требуемому смыслу списка.

NULL справа означает нулевую оценку?

Нет: отсутствие подходящей работы и оценка 0 — разные состояния. Подстановка нуля допустима лишь как осознанное правило вывода, не как объяснение смысла NULL.

Источники и работа с материалом

Объяснение, примеры и вопросы написаны для erudeet. Факты и определения сверены по источникам ниже; это ссылки для самостоятельной проверки.

Продолжить разбираться