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

← На главную

При разработке на платформе 1С программисты часто сталкиваются с задачей фильтрации элементов справочника или документов по данным их табличных частей. Одной из самых коварных задач является выборка записей, в которых ни в одной из нескольких табличных частей нет искомого значения. Обычные условия в секции ГДЕ здесь не работают из-за особенностей работы SQL-запросов. В этой статье мы подробно разберем, почему возникают ошибки и как правильно составить запрос несколькими способами.

Анализ типичной ошибки: почему прямое соединение не работает?

Рассмотрим ситуацию, когда разработчик пытается соединить справочник со всеми его табличными частями через ЛЕВОЕ СОЕДИНЕНИЕ и наложить фильтр в секции ГДЕ. Проанализируем, к чему это приводит:


ВЫБРАТЬ 
    Справочник1.Ссылка
ИЗ
    Справочник.Справочник1 КАК Справочник1
        ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Справочник1.Партнеры КАК СпрПартнеры
        ПО Справочник1.Ссылка = СпрПартнеры.Ссылка
ГДЕ
    СпрПартнеры.Партнер <> &Партнер

На первый взгляд кажется, что это должно сработать. Однако, если в табличной части Партнеры у одного элемента справочника есть три строки (Партнер1, Партнер2, Партнер3), и мы ищем отсутствие "Партнер1", запрос все равно вернет этот элемент. Почему? Потому что строки с "Партнер2" и "Партнер3" удовлетворяют условию "не равно", и СУБД включит их в результат. Ситуация осложняется еще больше, когда табличных частей несколько — возникает так называемое декартово произведение, когда количество строк в результате запроса множится в геометрической прогрессии, а логика фильтрации ломается окончательно.

Решение №1: Метод исключения через левое соединение и проверку на NULL

Это один из самых изящных и производительных способов. Суть его заключается в том, что мы пытаемся присоединить только те строки табличных частей, которые содержат запрещенный параметр. Если такая строка не найдена, поле присоединяемой таблицы будет содержать значение NULL.

Разберем этот метод по шагам. Важно перенести условие поиска из секции ГДЕ в условие связи ПО:


ВЫБРАТЬ
    Спр.Ссылка КАК Ссылка
ИЗ
    Справочник.Справочник1 КАК Спр
        ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Справочник1.Партнеры КАК СпрПартнеры
            ПО Спр.Ссылка = СпрПартнеры.Ссылка
                И СпрПартнеры.Партнер = &Партнер
        ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Справочник1.Товары КАК СпрТовары
            ПО Спр.Ссылка = СпрТовары.Ссылка
                И СпрТовары.Номенклатура В (&СписокНоменклатуры)
ГДЕ
    Спр.ДатаНачала <= &ДатаДок
    И Спр.ДатаОкончания >= &ДатаДок
    И СпрПартнеры.Ссылка ЕСТЬ NULL
    И СпрТовары.Ссылка ЕСТЬ NULL

Как это работает: мы говорим системе: "Присоедини ко мне записи ТЧ, где есть наш Партнер. Если ты не нашла таких записей, значит, в колонке СпрПартнеры.Ссылка будет пусто (NULL)". В секции ГДЕ мы оставляем только те элементы основного справочника, для которых соответствие в ТЧ не нашлось. Это гарантирует, что параметра нет ни в одной строке табличной части.

Решение №2: Использование вложенных запросов с объединением

Если логика исключения кажется сложной, можно воспользоваться подходом "от противного". Сначала мы найдем все ссылки, которые нам не подходят (где параметр присутствует), а затем исключим их из основного списка элементов справочника. Посмотрим на пример реализации:


ВЫБРАТЬ
    Спр.Ссылка
ИЗ
    Справочник.Справочник1 КАК Спр
ГДЕ
    Спр.ДатаНачала <= &ДатаДок
    И Спр.ДатаОкончания >= &ДатаДок
    И НЕ Спр.Ссылка В (
        ВЫБРАТЬ Ссылка ИЗ Справочник.Справочник1.Партнеры ГДЕ Партнер = &Партнер
        ОБЪЕДИНИТЬ
        ВЫБРАТЬ Ссылка ИЗ Справочник.Справочник1.Товары ГДЕ Номенклатура В (&СписокНоменклатуры)
        ОБЪЕДИНИТЬ
        ВЫБРАТЬ Ссылка ИЗ Справочник.Справочник1.Склады ГДЕ Склад = &Склад
    )

Проанализируем этот подход: оператор ОБЪЕДИНИТЬ собирает уникальный список всех "плохих" ссылок. Основной запрос выбирает только те элементы, которые не попали в этот "черный список". Этот метод очень нагляден и легок в чтении, однако на огромных объемах данных он может работать медленнее, чем первый вариант.

Решение №3: Оптимизация через временные таблицы

Для высоконагруженных систем или справочников с миллионами записей рекомендуется использовать временные таблицы. Это позволит СУБД построить оптимальный план запроса и использовать индексы.

Сначала создадим временную таблицу ВТ_ИсключаемыеСсылки, куда соберем все ссылки, в которых заданные параметры встречаются хотя бы в одной ТЧ.
Вторым запросом выберем данные из основного справочника, исключив записи, присутствующие в этой временной таблице.


// Шаг 1: Собираем все ссылки, которые НЕ должны попасть в результат
ВЫБРАТЬ Ссылка ПОМЕСТИТЬ ВТ_ИсключаемыеСсылки ИЗ Справочник.Справочник1.Партнеры ГДЕ Партнер = &Партнер;
ИНДЕКСИРОВАТЬ ПО Ссылка;

// Шаг 2: Основная выборка с исключением
ВЫБРАТЬ
    Спр.Ссылка
ИЗ
    Справочник.Справочник1 КАК Спр
        ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ИсключаемыеСсылки КАК Исключения
        ПО Спр.Ссылка = Исключения.Ссылка
ГДЕ
    Спр.ДатаНачала <= &ДатаДок
    И Спр.ДатаОкончания >= &ДатаДок
    И Исключения.Ссылка ЕСТЬ NULL

Важные рекомендации по производительности

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

Индексация: Поля табличных частей, по которым идет поиск (Партнер, Номенклатура, Склад), должны быть проиндексированы в конфигураторе (свойство "Индексировать"). По умолчанию в 1С индексируется только поле Ссылка.
Логика И/ИЛИ: Если вам нужно, чтобы параметра не было ни в одной из ТЧ, используйте И между условиями ЕСТЬ NULL. Если же достаточно отсутствия хотя бы в одной — используйте ИЛИ.
Пустые табличные части: Обратите внимание, что если табличная часть у элемента справочника пуста, то условия с ЕСТЬ NULL отработают корректно (так как в пустой таблице искомого значения точно нет).

Выяснив причину некорректной работы обычных фильтров, мы понимаем, что ключевым моментом является правильное использование теории множеств. Мы либо вычитаем подмножество "плохих" записей из общего списка, либо используем механизмы LEFT JOIN для выявления отсутствующих связей.