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

При решении задач складского учета, ценообразования и маркетинга часто возникает необходимость найти элементы справочника, для которых еще не созданы соответствующие записи в регистре сведений (например, номенклатуру без цен, без штрихкодов или без сопутствующих товаров). Разберем, как правильно построить такой запрос, какие типичные ошибки допускают разработчики и в чем заключается ключевая разница между условиями в секциях ПО и ГДЕ.

Типичные ошибки при построении запроса

Рассмотрим две самые распространенные ошибки, с которыми сталкиваются при попытке решить эту задачу:

1. Неоднозначность имени поля. Если имя поля совпадает с именем псевдонима одной из таблиц (например, в регистре сведений есть реквизит или измерение Номенклатура, и справочник называется Номенклатура), обращение вида Номенклатура.Ссылка вызовет ошибку компиляции запроса «Неоднозначное поле». Всегда задавайте явные и уникальные псевдонимы для каждой таблицы в секции ИЗ (например, СпрНоменклатура).
2. Помещение проверки на NULL в секцию ПО. Попытка написать ПО Регистр.Номенклатура ЕСТЬ NULL является принципиальной ошибкой в логике работы реляционных соединений. Такое условие проверяет исходные данные в присоединяемой таблице, а не факт отсутствия соответствия.

Классическое решение через ЛЕВОЕ СОЕДИНЕНИЕ

Разберем стандартный и наиболее понятный алгоритм решения. Для получения элементов, которых нет во второй таблице, основной (левой) таблицей должен выступать Справочник.Номенклатура, а присоединяемой (правой) — РегистрСведений. Сопоставление выполняется по равенству ссылок, а отсечение существующих записей — в секции ГДЕ.

Посмотрим на пример правильного текста запроса:

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

Проанализируем, как работает этот запрос:

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

В чем разница между условиями в секциях «ПО» и «ГДЕ»?

Выясним причину, почему нельзя писать условие проверки на NULL внутри соединения ПО:

1. Секция ПО (ON): описывает предикат сопоставления. СУБД пытается найти в регистре сведений строки, удовлетворяющие выражению. Если написать ПО СопутствующиеТовары.Номенклатура ЕСТЬ NULL, СУБД будет искать в регистре физически пустые записи. Однако при левом соединении строки левой таблицы (справочника) всё равно останутся в выборке, даже если условие не выполнилось. В итоге результат будет содержать всю номенклатуру, а отсечения существующих позиций не произойдет.
2. Секция ГДЕ (WHERE): применяется к уже сформированному результату соединения таблиц. Именно здесь проверка любого обязательного поля присоединенной таблицы на ЕСТЬ NULL отсекает строки, для которых нашлись реальные данные в регистре.

Альтернативный вариант: оператор НЕ В с подзапросом

Другой способ получить тот же результат — использование условия вложенного подзапроса с оператором НЕ В. Рассмотрим этот пример:

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

Такой вариант читается декларативно проще, однако при очень больших объемах регистра может уступать по производительности левому соединению, если СУБД построит неоптимальный план выполнения.

Оптимизация и важные особенности платформы 1С

Разберем практические нюансы, которые необходимо учитывать при написании запросов к справочникам и регистрам в реальных базах данных:

1. Иерархия справочника. Справочник Номенклатура почти всегда является иерархическим. Если не добавить условие НЕ СпрНоменклатура.ЭтоГруппа, в результирующий список попадут папки (группы), так как в регистре они обычно не фигурируют.
2. Пометка на удаление. Чтобы исключить элементы, помеченные на удаление, обязательно используем фильтр НЕ СпрНоменклатура.ПометкаУдаления.
3. Индексация в регистре сведений. Скорость левого соединения критически зависит от индексов. Если поле Номенклатура является первым измерением непериодического регистра, СУБД использует кластерный индекс. Если же поле является вторым измерением или реквизитом, необходимо убедиться, что для него включено свойство «Ведущее» или «Индексировать», иначе запрос вызовет полное сканирование таблицы регистра (Table Scan).
4. Периодичность регистра. Если регистр периодический (например, ЦеныНоменклатуры), прямое обращение к физической таблице проверит наличие записей за всю историю. Если необходимо узнать, нет ли актуальной записи на определенную дату, соединяться следует не с физической таблицей регистра, а с виртуальной таблицей СрезПоследних.
5. Работа с временными таблицами. При работе с миллионными справочниками наиболее производительный подход — предварительно выгрузить уникальные ссылки номенклатуры из регистра во временную таблицу через МенеджерВременныхТаблиц, проиндексировать ее по полю номенклатуры и только затем выполнять ЛЕВОЕ СОЕДИНЕНИЕ.
6. Права доступа (RLS). При включенном ограничении доступа на уровне записей чтение регистра пользователем с урезанными правами может скрыть существующие записи. В результате номенклатура попадет в выборку как «отсутствующая», хотя физически она в регистре есть. Если требуется гарантированная системная проверка, запрос следует выполнять в привилегированном режиме с помощью метода УстановитьПривилегированныйРежим(Истина).