Как отфильтровать пустые значения и незаполненные ссылки в запросе 1С при левом соединении

При построении отчетов и выборок в 1С часто возникает задача отфильтровать записи, которые еще не распределены или не имеют связанных сведений во внешней таблице. Например, когда необходимо получить список заказов покупателей, для которых еще не назначен автомобиль в регистре сведений РаспределениеЗаказовПоАвтомобилям.

Разберем по шагам, почему обычная проверка на ЕСТЬ NULL или простое сравнение с пустой ссылкой могут не срабатывать, и рассмотрим правильные варианты построения условий в языке запросов 1С.

Выясним причину проблемы: NULL и пустые ссылки

Главная сложность заключается в разграничении двух разных понятий платформы 1С:

1. Значение NULL: образуется в результате выполнения оператора ЛЕВОЕ СОЕДИНЕНИЕ, когда в присоединяемой таблице полностью отсутствует запись, соответствующая условию связи.
2. Пустая ссылка (ПустаяСсылка): запись в присоединяемой таблице существует, но сам реквизит (например, Автомобиль) не заполнен и содержит пустую ссылку справочника.

Кроме того, в языке запросов 1С используется трехзначная логика. Любое прямое сравнение со значением NULL (например, ВложенныйЗапрос.Автомобиль = ЗНАЧЕНИЕ(Справочник.ТипыТранспортныхСредств.ПустаяСсылка)) возвращает результат НЕОПРЕДЕЛЕНО, который в секции ГДЕ трактуется как ЛОЖЬ. В итоге строки, образованные при левом соединении, просто отсекаются из результата выборки.

Способ 1. Использование функции ЕСТЬNULL с подстановкой пустой ссылки

Проанализируем решение, которое помогло автору вопроса. С помощью функции ЕСТЬNULL мы подменяем значение NULL (если запись не найдена во внешней таблице) на пустую ссылку нужного справочника, а затем сравниваем итоговое значение с той же пустой ссылкой.

Посмотрим на пример запроса:

ВЫБРАТЬ
    Заказы.Маршрут КАК Маршрут,
    Заказы.Накладная КАК Накладная,
    Заказы.Заказ КАК Заказ,
    ВложенныйЗапрос.Автомобиль КАК Машина,
    Заказы.Вес КАК Вес,
    Заказы.СуммаДокумента КАК Сумма,
    Заказы.Объем КАК Объем,
    Заказы.Контрагент КАК Контрагент,
    Заказы.Агент КАК Агент,
    Заказы.КонтрагентНаименование КАК КонтрагентНаименование,
    ВложенныйЗапрос.Автомобиль.Наименование КАК АвтомобильНаименование
ИЗ
    Заказы КАК Заказы
    ЛЕВОЕ СОЕДИНЕНИЕ (
        ВЫБРАТЬ
            Распределение.Автомобиль КАК Автомобиль,
            Распределение.ЗаказПокупателя КАК ЗаказПокупателя,
            Распределение.Реализация КАК Реализация
        ИЗ
            РегистрСведений.РаспределениеЗаказовПоАвтомобилям КАК Распределение
    ) КАК ВложенныйЗапрос
    ПО Заказы.Заказ = ВложенныйЗапрос.ЗаказПокупателя
    И Заказы.Ссылка = ВложенныйЗапрос.Реализация
ГДЕ
    ЕСТЬNULL(ВложенныйЗапрос.Автомобиль, ЗНАЧЕНИЕ(Справочник.ТипыТранспортныхСредств.ПустаяСсылка)) = ЗНАЧЕНИЕ(Справочник.ТипыТранспортныхСредств.ПустаяСсылка)
УПОРЯДОЧИТЬ ПО
    Маршрут,
    АвтомобильНаименование УБЫВ,
    КонтрагентНаименование
ИТОГИ
    МИНИМУМ(Машина),
    СУММА(Вес),
    СУММА(Сумма),
    КОЛИЧЕСТВО(Контрагент),
    МАКСИМУМ(Агент)
ПО
    Маршрут

В данном случае выражение ЕСТЬNULL(ВложенныйЗапрос.Автомобиль, ЗНАЧЕНИЕ(Справочник.ТипыТранспортныхСредств.ПустаяСсылка)) гарантирует, что мы всегда получим тип ссылки справочника, который затем корректно сравнивается в условии.

Способ 2. Явное комбинированное условие (Оптимизированный вариант)

Рассмотрим более производительное решение. Использование функций (таких как ЕСТЬNULL) над полями в секции ГДЕ может препятствовать использованию индексов СУБД. Чтобы оптимизировать выполнение запроса, рекомендуется разбить условие на два явных логических блока через оператор ИЛИ.

Пример составления условия:

ГДЕ
    ВложенныйЗапрос.Автомобиль ЕСТЬ NULL
    ИЛИ ВложенныйЗапрос.Автомобиль = ЗНАЧЕНИЕ(Справочник.ТипыТранспортныхСредств.ПустаяСсылка)

Такой подход обрабатывает оба возможных сценария:

1. ВложенныйЗапрос.Автомобиль ЕСТЬ NULL — отбирает заказы, по которым вообще нет записей в регистре сведений.
2. ВложенныйЗапрос.Автомобиль = ЗНАЧЕНИЕ(...) — отбирает записи, которые есть в регистре, но поле с автомобилем не заполнено.

Способ 3. Проверка ключевого поля связи или использование оператора НЕ В

Если задача состоит исключительно в отборе заказов, которые еще не распределены (то есть записи в присоединяемой таблице отсутствуют), проще и надежнее проверять ключевое поле связи на значение NULL:

ГДЕ
    ВложенныйЗапрос.ЗаказПокупателя ЕСТЬ NULL

Также можно применить конструкцию с оператором НЕ В без использования ЛЕВОЕ СОЕДИНЕНИЕ:

ГДЕ
    Заказы.Заказ НЕ В (
        ВЫБРАТЬ
            Распределение.ЗаказПокупателя
        ИЗ
            РегистрСведений.РаспределениеЗаказовПоАвтомобилям КАК Распределение
    )

Важные рекомендации при работе с регистрами сведений

При соединении таблиц заказов с регистрами сведений обратите внимание на следующие нюансы:

1. Задвоение итогов: Если в регистре сведений на один заказ может приходиться несколько записей, левое соединение удвоит строки исходной таблицы Заказы. Это приведет к искажению суммовых и количественных показателей в секции ИТОГИ. Для предотвращения этой проблемы предварительно сворачивайте данные регистра во вложенном запросе с помощью СГРУППИРОВАТЬ ПО или ВЫБРАТЬ РАЗЛИЧНЫЕ.
2. Составные типы данных: Если проверяемый реквизит имеет составной тип, незаполненное значение может быть не только пустой ссылкой конкретного справочника, но и значением НЕОПРЕДЕЛЕНО. В таком случае в условие следует добавить проверку: ВложенныйЗапрос.Автомобиль = НЕОПРЕДЕЛЕНО.