При написании сложных запросов в 1С:Предприятие разработчики часто сталкиваются с необходимостью исключить определенный набор данных из выборки. Казалось бы, оператор НЕ В (...) идеально подходит для этой задачи. Однако, когда этот оператор применяется внутри конструкции ЛЕВОЕ СОЕДИНЕНИЕ вместе с подзапросом, результаты могут оказаться неожиданными, а производительность — низкой. В этой статье мы подробно разберем, почему так происходит, и рассмотрим правильные методы решения задачи.
Рассмотрим ситуацию, с которой столкнулся автор. Имеется запрос, который выбирает данные из временной таблицы и пытается присоединить к ним сведения из регистра, исключая при этом определенные договоры по условию.
Исходный текст проблемного фрагмента запроса выглядел примерно так:
ВЫБРАТЬ
...
ИЗ
ВТ_ФильтрСубконто КАК ВТ_ФильтрСубконто
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.СоответствиеБДПГНиНомГрупп КАК СоответствиеБДПГНиНомГрупп
ПО ВТ_ФильтрСубконто.НаименованиеБДПГН = СоответствиеБДПГНиНомГрупп.НаименованиеБДПГН
И СоответствиеБДПГНиНомГрупп.Фильтр = "Подразделение в договоре"
И НЕ ПОДСТРОКА(ВТ_ФильтрСубконто.ДоговорКонтрагентаРегистрационнныйНомер, 6, 3) В
(ВЫБРАТЬ
СоответствиеБДПГНиНомГрупп.ЗначениеФильтра
ИЗ
РегистрСведений.СоответствиеБДПГНиНомГрупп
ГДЕ
СоответствиеБДПГНиНомГрупп.Фильтр = "Подразделение в договоре")
ГДЕ
СоответствиеБДПГНиНомГрупп.НаименованиеБДПГН ЕСТЬ NULL
В результате выборка возвращала не те данные, которые ожидал разработчик. Строки, которые должны были отфильтроваться, не попадали в исключение должным образом или запрос работал нестабильно. Давайте проанализируем причины этого поведения.
Первое, что нам нужно понять — как работает ЛЕВОЕ СОЕДИНЕНИЕ. Оператор берет все записи из левой таблицы (в данном случае ВТ_ФильтрСубконто) и пытается найти соответствия в правой таблице на основе условий, указанных после ключевого слова ПО.
Если мы добавляем условие фильтрации (в том числе НЕ В) в секцию ПО, мы не фильтруем записи левой таблицы. Мы лишь указываем, при каких условиях данные из правой таблицы должны быть присоединены.
ПО выполняется (то есть договор не входит в список исключений), данные правой таблицы присоединяются.ПО не выполняется (договор входит в список исключений), данные правой таблицы не присоединяются, и поля правой таблицы в этой строке принимают значение NULL.Далее, в секции ГДЕ автор проверяет: СоответствиеБДПГНиНомГрупп.НаименованиеБДПГН ЕСТЬ NULL. Это условие отберет:
НЕ В (так как для них соединение не сработало).Такая логика становится запутанной и часто приводит к ошибкам интерпретации данных, особенно если в правой таблице могут быть свои нюансы.
Это одна из самых коварных ловушек в SQL и языке запросов 1С. Если подзапрос:
(ВЫБРАТЬ СоответствиеБДПГНиНомГрупп.ЗначениеФильтра ...)
вернет хотя бы одно значение NULL, то результат выражения Поле НЕ В (Список) никогда не будет ИСТИНА. Он будет либо ЛОЖЬ, либо НЕОПРЕДЕЛЕНО (UNKNOWN).
В языке запросов 1С любое сравнение с NULL дает NULL (кроме проверки ЕСТЬ NULL). Следовательно, если в вашем регистре сведений есть "битая" запись с пустым значением фильтра, условие НЕ В перестанет работать корректно для всех строк выборки.
Рекомендация: Если вы все-таки используете подзапросы (хотя мы это не рекомендуем в данном контексте), всегда добавляйте проверку на заполненность:
ГДЕ ЗначениеФильтра ЕСТЬ НЕ NULL
Использование подзапроса внутри условия соединения (ON) — это тяжелая операция для СУБД. Платформа 1С транслирует такой запрос в SQL, часто используя конструкции вроде OUTER APPLY или циклы (Nested Loops). Для каждой строки левой таблицы СУБД может пытаться выполнить проверку по подзапросу.
Это приводит к тому, что:
Давайте рассмотрим, как переписать этот запрос, чтобы он работал корректно, предсказуемо и быстро. Существует два основных подхода ("золотых стандарта") в разработке на 1С.
Если список исключений (значений фильтра) не очень большой (например, десятки или сотни значений, но не тысячи), самым эффективным способом будет получить этот список заранее и передать его в запрос как параметр.
Этап 1: Получение массива значений в коде 1С
ЗапросФильтр = Новый Запрос;
ЗапросФильтр.Текст =
"ВЫБРАТЬ
| РС.ЗначениеФильтра
|ИЗ
| РегистрСведений.СоответствиеБДПГНиНомГрупп КАК РС
|ГДЕ
| РС.Фильтр = ""Подразделение в договоре""
| И РС.ЗначениеФильтра ЕСТЬ НЕ NULL";
МассивИсключений = ЗапросФильтр.Выполнить().Выгрузить().ВыгрузитьКолонку("ЗначениеФильтра");
Этап 2: Передача параметра в основной запрос
Теперь мы можем использовать простое и быстрое условие в секции ГДЕ основного запроса, убрав сложную логику из соединения.
ВЫБРАТЬ
...
ИЗ
ВТ_ФильтрСубконто КАК ВТ_ФильтрСубконто
// Оставляем соединение только для получения данных, если они нужны
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.СоответствиеБДПГНиНомГрупп КАК СоответствиеБДПГНиНомГрупп
ПО ВТ_ФильтрСубконто.НаименованиеБДПГН = СоответствиеБДПГНиНомГрупп.НаименованиеБДПГН
ГДЕ
// Простое и понятное условие фильтрации
НЕ ПОДСТРОКА(ВТ_ФильтрСубконто.ДоговорКонтрагентаРегистрационнныйНомер, 6, 3) В (&МассивИсключений)
Этот подход значительно упрощает чтение кода и снимает нагрузку с СУБД, так как список значений передается в запрос как константа.
Если список исключений велик или формируется сложным образом, следует использовать временные таблицы. Это классический паттерн "Исключение соединением".
Шаг 1: Помещаем фильтр во временную таблицу
ВЫБРАТЬ РАЗЛИЧНЫЕ
РС.ЗначениеФильтра КАК ЗначениеИсключения
ПОМЕСТИТЬ ВТ_Исключения
ИЗ
РегистрСведений.СоответствиеБДПГНиНомГрупп КАК РС
ГДЕ
РС.Фильтр = "Подразделение в договоре"
ИНДЕКСИРОВАТЬ ПО
ЗначениеИсключения
;
Шаг 2: Левое соединение с проверкой на NULL
В основном запросе мы делаем левое соединение с этой временной таблицей по нашему ключевому полю. Если соединение произошло — значит, запись есть в списке исключений, и нам она не нужна. Мы отбираем только те строки, где соединение не удалось (значение ЕСТЬ NULL).
ВЫБРАТЬ
ВТ_ФильтрСубконто.*
ИЗ
ВТ_ФильтрСубконто КАК ВТ_ФильтрСубконто
// Пытаемся найти совпадение в списке исключений
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Исключения КАК ВТ_Исключения
ПО ПОДСТРОКА(ВТ_ФильтрСубконто.ДоговорКонтрагентаРегистрационнныйНомер, 6, 3) = ВТ_Исключения.ЗначениеИсключения
ГДЕ
// Оставляем только те строки, которые НЕ нашлись в таблице исключений
ВТ_Исключения.ЗначениеИсключения ЕСТЬ NULL
В тексте запроса используется функция ПОДСТРОКА для поля соединения. Обратите внимание, что использование функций над полями в условии соединения (ON) может препятствовать использованию индексов, что замедляет запрос.
Если есть возможность, лучше заранее подготовить данные во временной таблице ВТ_ФильтрСубконто, выделив нужную часть номера договора в отдельное поле (например, КодПодразделения), проиндексировать его и соединять таблицы уже по чистому полю без вызова функций.
ПО (ON) левого соединения. Это приводит к ошибкам логики и падению производительности.НЕ В чувствителен к значениям NULL в списке. Всегда контролируйте чистоту данных.ЛЕВОЕ СОЕДИНЕНИЕ ... ГДЕ ... ЕСТЬ NULL для фильтрации больших объемов данных.