Почему левое соединение в запросе 1С работает как внутреннее и как это исправить?

← На главную

При работе с языком запросов 1С одной из самых распространенных ошибок как среди начинающих, так и среди опытных разработчиков является так называемое «схлопывание» левого соединения до внутреннего. Ситуация выглядит стандартно: нам необходимо получить полный список записей из левой (основной) таблицы и дополнить его данными из правой (сопутствующей) таблицы. Однако на практике запрос возвращает исключительно те строки, для которых нашлись соответствия в обеих таблицах.

В этой статье мы подробно разберем механику этой ошибки на физическом уровне работы СУБД, рассмотрим два эффективных способа решения проблемы и научимся писать оптимальные, быстродействующие запросы в соответствии со стандартами разработки фирмы «1С».

В чем суть проблемы? Анализируем исходный запрос

Рассмотрим практическую задачу: нам нужно получить остатки номенклатуры на складах и одновременно вывести значение определенного дополнительного свойства для каждой номенклатуры (например, «Цвет» или «Производитель»). Свойство хранится в периодическом регистре сведений ЗначенияСвойствОбъектов.

Разработчик создает следующий запрос:


ВЫБРАТЬ
    ТоварыНаСкладахОстатки.Номенклатура КАК Номенклатура,
    ЗначенияСвойствОбъектов.Значение КАК Значение
ИЗ
    РегистрНакопления.ТоварыНаСкладах.Остатки(&ДатаКон, ) КАК ТоварыНаСкладахОстатки
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЗначенияСвойствОбъектов КАК ЗначенияСвойствОбъектов
        ПО ТоварыНаСкладахОстатки.Номенклатура = ЗначенияСвойствОбъектов.Объект
ГДЕ
    ЗначенияСвойствОбъектов.Sвойство = &Свойство

Логика разработчика понятна: мы берем все остатки (левая таблица) и «приклеиваем» к ним свойства (правая таблица) по ссылке номенклатуры. А чтобы получить не все свойства подряд, а только нужное нам (например, «Цвет»), в секции ГДЕ устанавливается фильтр по конкретному свойству.

Однако при запуске этого запроса происходит странное: номенклатура, у которой свойство «Цвет» не задано (или отсутствует в регистре), вообще исчезает из результата запроса! Левое соединение превратилось во внутреннее.

Почему это происходит? Физический смысл работы СУБД

Чтобы понять причину, давайте проанализируем последовательность выполнения операций СУБД (MS SQL Server, PostgreSQL или другой базы данных) при обработке этого запроса:

Этап соединения (FROM + JOIN): СУБД берет левую таблицу (остатки) и пытается сопоставить ее с правой таблицей (регистр свойств) по условию, указанному после ключевого слова ПО. Если для какой-то номенклатуры в регистре нет записи со свойством, СУБД все равно сохраняет строку с номенклатурой в промежуточном результате, но во все поля правой таблицы (включая ЗначенияСвойствОбъектов.Свойство и ЗначенияСвойствОбъектов.Значение) записывает специальное маркерное значение NULL (отсутствие данных).
Этап фильтрации (WHERE / ГДЕ): После того как таблицы соединены, СУБД приступает к отсечению строк по условиям секции ГДЕ. Она берет каждую строку промежуточного результата и проверяет условие: ЗначенияСвойствОбъектов.Свойство = &Свойство.

Здесь и кроется ловушка. Для позиций номенклатуры, у которых свойства не было, в поле ЗначенияСвойствОбъектов.Свойство записано значение NULL. СУБД пытается выполнить операцию сравнения:

NULL = &Свойство (например, Ссылка на элемент справочника «Свойства объектов»)

Согласно стандарту SQL и трехзначной логике (True, False, Unknown), любое сравнение значения NULL с любым конкретным значением всегда возвращает результат UNKNOWN (ложь при фильтрации). Условие фильтрации не выполняется. В итоге СУБД безжалостно отбрасывает из финальной выборки все строки, где правая таблица вернула NULL. Мы получаем классическое внутреннее соединение (INNER JOIN).

Золотое правило разработки в 1С: Любое жесткое условие (за исключением проверки на NULL), наложенное на поля правой таблицы в секции ГДЕ при левом соединении, автоматически превращает это соединение во внутреннее.

---

Решение №1: Перенос условия в секцию связи ПО (Быстрый способ)

Самый простой и очевидный способ исправить логическую ошибку — перенести отбор по свойству непосредственно в условие соединения таблиц. В этом случае фильтрация правой таблицы произойдет ДО того, как будет сформирован итоговый набор строк с NULL.

Давайте посмотрим, как изменится текст нашего запроса:


ВЫБРАТЬ
    ТоварыНаСкладахОстатки.Номенклатура КАК Номенклатура,
    ЗначенияСвойствОбъектов.Значение КАК Значение
ИЗ
    РегистрНакопления.ТоварыНаСкладах.Остатки(&ДатаКон, ) КАК ТоварыНаСкладахОстатки
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЗначенияСвойствОбъектов КАК ЗначенияСвойствОбъектов
        ПО ТоварыНаСкладахОстатки.Номенклатура = ЗначенияСвойствОбъектов.Объект
        И ЗначенияСвойствОбъектов.Свойство = &Свойство

При такой структуре запроса СУБД действует иначе:

При соединении таблиц СУБД ищет в правой таблице только те записи, у которых поле Объект совпадает с номенклатурой И поле Свойство строго равно нашему параметру &Свойство.
Если такая запись найдена, из нее берется значение свойства.
Если такой записи нет, соединение все равно происходит, а в поле Значение подставляется NULL. Номенклатура при этом не пропадает из результата запроса.

---

Решение №2: Использование Временных Таблиц (Рекомендуемый стандарт 1С)

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

Согласно стандартам безопасной и производительной разработки фирмы «1С», для обеспечения высокой скорости работы запроса необходимо предварительно подготовить данные правой таблицы во временной таблице. Разберем этот профессиональный подход по шагам.

Шаг 1. Создаем временную таблицу с отфильтрованными свойствами. Мы выбираем только те свойства, которые соответствуют нашему параметру, и сразу же индексируем временную таблицу по полю связи:


ВЫБРАТЬ
    ЗначенияСвойствОбъектов.Объект КАК Объект,
    ЗначенияСвойствОбъектов.Значение КАК Значение
ПОМЕСТИТЬ ВТ_СвойстваНоменклатуры
ИЗ
    РегистрСведений.ЗначенияСвойствОбъектов КАК ЗначенияСвойствОбъектов
ГДЕ
    ЗначенияСвойствОбъектов.Свойство = &Свойство

ИНДЕКСИРОВАТЬ ПО
    Объект
;

Шаг 2. Выполняем легкое соединение остатков с подготовленной временной таблицей. Поскольку во временной таблице находятся только нужные нам свойства и она проиндексирована, левое соединение отработает мгновенно:


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

Проанализируем преимущества этого подхода:

Максимальная скорость: Выборка из регистра сведений в секции ГДЕ выполняется очень быстро, так как СУБД использует существующие индексы регистра.
Простой план запроса: Левое соединение происходит с очень маленькой временной таблицей (в ней содержатся свойства только для нужного вида), что исключает тяжелые операции сканирования физических таблиц на диске.
Отсутствие блокировок: Минимизируется время блокировок таблиц базы данных при параллельной работе пользователей.

---

Обработка значений NULL

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

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

Функция ЕСТЬNULL(Поле, ЗначениеЗамены) проверяет значение в первом параметре. Если там находится NULL, она возвращает значение из второго параметра. В качестве значения замены рекомендуется использовать пустую ссылку соответствующего типа данных:

ЕСТЬNULL(ВТ_СвойстваНоменклатуры.Значение, ЗНАЧЕНИЕ(Справочник.ЗначенияСвойствОбъектов.ПустаяСсылка))

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

ЕСТЬNULL(ВТ_СвойстваНоменклатуры.Значение, "")

---

Полный пример реализации во встроенном языке 1С

Давайте посмотрим, как выглядит законченный программный код формирования и выполнения такого оптимального запроса во встроенном языке 1С:Предприятие:


Запрос = Новый Запрос;
Запрос.Текст = 
    "ВЫБРАТЬ
    |	ЗначенияСвойствОбъектов.Объект КАК Объект,
    |	ЗначенияСвойствОбъектов.Значение КАК Значение
    |ПОМЕСТИТЬ ВТ_СвойстваНоменклатуры
    |ИЗ
    |	РегистрСведений.ЗначенияСвойствОбъектов КАК ЗначенияСвойствОбъектов
    |ГДЕ
    |	ЗначенияСвойствОбъектов.Свойство = &Свойство
    |
    |ИНДЕКСИРОВАТЬ ПО
    |	Объект
    |;
    |
    |////////////////////////////////////////////////////////////////////////////////
    |ВЫБРАТЬ
    |	ТоварыНаСкладахОстатки.Номенклатура КАК Номенклатура,
    |	ЕСТЬNULL(ВТ_СвойстваНоменклатуры.Значение, ЗНАЧЕНИЕ(Справочник.ЗначенияСвойствОбъектов.ПустаяСсылка)) КАК ЗначениеСвойства
    |ИЗ
    |	РегистрНакопления.ТоварыНаСкладах.Остатки(&ДатаКон, ) КАК ТоварыНаСкладахОстатки
    |		ЛЕВОЕ СОЕДИНЕНИЕ ВТ_СвойстваНоменклатуры КАК ВТ_СвойстваНоменклатуры
    |			ПО ТоварыНаСкладахОстатки.Номенклатура = ВТ_СвойстваНоменклатуры.Объект";

// Установка параметров запроса
Запрос.УстановитьПараметр("ДатаКон", Новый Граница(КонецДня(ТекущаяДата()), ВидГраницы.Включая));
Запрос.УстановитьПараметр("Свойство", ПланыВидовХарактеристик.СвойстваОбъектов.НайтиПоНаименованию("Цвет"));

// Выполнение запроса и вывод результатов
РезультатЗапроса = Запрос.Выполнить();
ВыборкаДетальныеЗаписи = РезультатЗапроса.Выбрать();

Пока ВыборкаДетальныеЗаписи.Следующий() Цикл
    // Обработка полученных данных
    Сообщить(ШаблонФоматирования("Номенклатура: %1, Цвет: %2", 
        ВыборкаДетальныеЗаписи.Номенклатура, 
        ВыборкаДетальныеЗаписи.ЗначениеСвойства));
КонецЦикла;

Подведем итоги: Краткий чек-лист разработчика

Чтобы избежать ошибок при проектировании левых соединений, всегда проверяйте свои запросы по следующим пунктам:

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