Как в запросе 1С получить элементы справочника, по которым нет движений в регистре?

← На главную

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

В чем заключается ошибка при сравнении по наименованию

Проанализируем типичный запрос, в котором возникла проблема:


ВЫБРАТЬ
    Кошельки.Наименование КАК Наименование
ИЗ
    Справочник.Кошельки КАК Кошельки
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.РегистрПоступления КАК РегистрПоступления
        ПО Кошельки.Наименование = РегистрПоступления.Кошелек
ГДЕ
    РегистрПоступления.Кошелек ЕСТЬ NULL

Выясним причину некорректной работы. Поле Кошельки.Наименование имеет строковый тип данных (Строка), тогда как измерение РегистрПоступления.Кошелек в регистре накопления обычно имеет ссылочный тип (СправочникСсылка.Кошельки). В платформе 1С строковое значение никогда не равно ссылке. В результате условие в блоке ПО не выполняется ни для одной строки. При выполнении левого соединения ко всем строкам таблицы справочника справа присоединяются пустые значения NULL, и условие ГДЕ РегистрПоступления.Кошелек ЕСТЬ NULL оказывается истинным для абсолютно всех элементов справочника.

Способ 1. Использование левого соединения по ссылочному полю

Рассмотрим правильный вариант запроса с использованием соединения. Чтобы соединение сработало корректно, мы должны связывать таблицы по одинаковым типам данных — по полю Ссылка:


ВЫБРАТЬ
    Кошельки.Ссылка КАК Ссылка,
    Кошельки.Наименование КАК Наименование
ИЗ
    Справочник.Кошельки КАК Кошельки
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.РегистрПоступления КАК РегистрПоступления
        ПО Кошельки.Ссылка = РегистрПоступления.Кошелек
ГДЕ
    РегистрПоступления.Кошелек ЕСТЬ NULL
    И НЕ Кошельки.ПометкаУдаления

В этом примере левое соединение находит совпадения по ссылке. Для тех элементов справочника, по которым в регистре нет записей, поле РегистрПоступления.Кошелек принимает значение NULL, и именно они попадают в итоговую выборку. Также мы добавили условие НЕ Кошельки.ПометкаУдаления, чтобы исключить помеченные на удаление элементы.

Способ 2. Использование условия ГДЕ НЕ ... В (ВЫБРАТЬ ...)

Посмотрим на альтернативный вариант выборки через подзапрос с предикатом НЕ В. В таком варианте мы выбираем список уникальных ссылок из регистра и отсекаем их из справочника:


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

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

Дополнительные практические рекомендации

Для создания надежных запросов в реальных конфигурациях разберем несколько важных моментов:

Вид движения и виртуальные таблицы: Если в регистре накопления хранятся и приходы, и расходы, а нам нужно найти элементы именно без расходов, чтение физической таблицы регистра без фильтра посчитает приход за движение. В таких случаях следует использовать виртуальную таблицу Обороты регистра накопления либо явно фильтровать физическую таблицу по условию ВидДвижения = ЗНАЧЕНИЕ(ВидДвиженияНакопления.Расход).
Условия по периоду: Если мы ищем движения за определенный временной интервал в запросе с левым соединением, условие по периоду необходимо указывать строго в секции ПО (например, ПО Кошельки.Ссылка = Регистр.Кошелек И Регистр.Период МЕЖДУ &ДатаНачала И &ДатаОкончания). Если перенести фильтр даты в секцию ГДЕ, СУБД преобразует левое соединение во внутреннее, и все элементы без движений будут удалены из результата.
Иерархия справочника: Если справочник имеет иерархическую структуру (группы и элементы), папки справочника не имеют движений в регистре и попадут в результат. Чтобы отсечь папки, добавим условие НЕ Кошельки.ЭтоГруппа.
Оптимизация на больших базах: Если таблица регистра содержит миллионы записей, левое соединение со всей таблицей может замедлить выполнение. Наиболее производительный паттерн — предварительно выбрать уникальные ссылки во временную таблицу, проиндексировать ее с помощью конструкции ИНДЕКСИРОВАТЬ ПО, после чего соединять справочник с подготовленной временной таблицей.