При написании запросов в системе «1С:Предприятие» разработчики часто сталкиваются с ситуацией, когда использование ЛЕВОЕ СОЕДИНЕНИЕ (LEFT JOIN) не дает ожидаемого результата: записи из левой таблицы, для которых нет соответствий в правой, исчезают из итоговой выборки. В этой статье мы подробно разберем, почему это происходит, как исправить логику запроса и какие механизмы платформы 8.3 позволяют ускорить получение данных из регистров сведений.
Рассмотрим типичную ситуацию: нам необходимо получить список номенклатуры из документа и соединить его с ценами из регистра сведений. Мы хотим видеть всю номенклатуру, даже если цена для нее не установлена. Автор темы привел следующий код:
ВЫБРАТЬ
ТаблицаНоменклатуры.Номенклатура КАК Номенклатура,
ЦеныНоменклатурыСрезПоследних.Цена КАК Цена,
ЦеныНоменклатурыСрезПоследних.ТипЦен КАК ТипЦен
ИЗ
ТаблицаНоменклатуры КАК ТаблицаНоменклатуры
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних КАК ЦеныНоменклатурыСрезПоследних
ПО ТаблицаНоменклатуры.Номенклатура = ЦеныНоменклатурыСрезПоследних.Номенклатура
ГДЕ
ЦеныНоменклатурыСрезПоследних.ТипЦен = &ТипЦен
Проанализируем, почему этот запрос работает некорректно. Когда мы используем ЛЕВОЕ СОЕДИНЕНИЕ, система сначала формирует временный результат, где для отсутствующих цен в полях Цена и ТипЦен стоят значения NULL. Однако затем вступает в силу условие в секции ГДЕ.
Поскольку выражение NULL = &ТипЦен всегда возвращает «Ложь» (или, точнее, UNKNOWN, что для фильтра равносильно «Ложь»), все строки, в которых цена не была найдена, отсекаются. Таким образом, левое соединение фактически превращается во внутреннее (INNER JOIN). Это классическая ошибка при работе с SQL-запросами.
Самый простой способ исправить ситуацию — перенести фильтрацию по правой таблице непосредственно в условие соединения. Разберем, как изменится логика: в этом случае условие будет применяться в момент сопоставления таблиц, а не после формирования всей выборки.
ВЫБРАТЬ
ТаблицаНоменклатуры.Номенклатура КАК Номенклатура,
ЕСТЬNULL(ЦеныНоменклатурыСрезПоследних.Цена, 0) КАК Цена,
ЦеныНоменклатурыСрезПоследних.ТипЦен КАК ТипЦен
ИЗ
ТаблицаНоменклатуры КАК ТаблицаНоменклатуры
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних КАК ЦеныНоменклатурыСрезПоследних
ПО ТаблицаНоменклатуры.Номенклатура = ЦеныНоменклатурыСрезПоследних.Номенклатура
И ЦеныНоменклатурыСрезПоследних.ТипЦен = &ТипЦен
В данном примере использование ключевого слова И в блоке ПО позволяет системе сначала ограничить записи в правой таблице нужным типом цен, а затем присоединить их к номенклатуре. Если цена нужного типа не найдена, в результат попадет NULL, который мы можем обработать функцией ЕСТЬNULL.
Для программиста 1С наиболее правильным и эффективным способом является использование параметров виртуальной таблицы. Проанализируем ситуацию: зачем заставлять СУБД сначала получать срез последних по всем типам цен, а потом фильтровать их при соединении? Мы можем ограничить выборку еще на этапе обращения к виртуальной таблице СрезПоследних.
Рассмотрим правильную структуру запроса:
ВЫБРАТЬ
ТаблицаНоменклатуры.Номенклатура КАК Номенклатура,
ЕСТЬNULL(ЦеныНоменклатурыСрезПоследних.Цена, 0) КАК Цена
ИЗ
ТаблицаНоменклатуры КАК ТаблицаНоменклатуры
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(, ТипЦен = &ТипЦен) КАК ЦеныНоменклатурыСрезПоследних
ПО ТаблицаНоменклатуры.Номенклатура = ЦеныНоменклатурыСрезПоследних.Номенклатура
Преимущества этого подхода:
СрезПоследних гарантирует, что вы получите актуальную цену именно для указанного типа цен, а не просто последнюю цену вообще, которая может не подойти под условие в ГДЕ.В ходе обсуждения на форуме была затронута важная тема: являются ли виртуальные таблицы «Срез последних» физическими? Выясним это подробнее, так как это напрямую влияет на скорость работы ваших отчетов.
Начиная с версии платформы 8.3.8, для периодических регистров сведений появилась возможность включения режима хранения итогов. Посмотрим, как это работает:
В конфигураторе, на закладке «Прочие» у регистра сведений, можно установить флаг «Разрешить итоги: Срез последних». Рассмотрим последствия этого действия:
СрезПоследних без указания даты (на текущий момент), платформа считывает данные напрямую из этой таблицы, не выполняя сложный расчет с поиском максимального периода.Проанализируем ситуацию с «глюкавостью», упомянутую участниками форума. В ранних релизах действительно могли возникать проблемы с обновлением этих итогов при удалении записей или специфических наборах данных. Однако в современных версиях механизмы отлажены. Важно помнить: если вы указываете конкретную дату в параметрах виртуальной таблицы, система, скорее всего, вернется к стандартному (более медленному) алгоритму расчета через основную таблицу регистра.
Подводя итог нашему разбору, выделим несколько золотых правил для разработчика 1С:
ЛЕВОГО СОЕДИНЕНИЯ в секции ГДЕ, если только вашей целью не является сознательное превращение соединения во внутреннее.Условие, Период) вместо фильтрации в ГДЕ или ПО, если это возможно.ЕСТЬNULL для замены пустых значений на 0 или пустые строки, чтобы избежать ошибок в дальнейших вычислениях.ЛЕВОГО СОЕДИНЕНИЯ.Разберем пример с индексированием временной таблицы для максимальной производительности:
ВЫБРАТЬ
Товары.Номенклатура КАК Номенклатура
ПОМЕСТИТЬ ВТ_Номенклатура
ИЗ
Документ.РеализацияТоваровУслуг.Товары КАК Товары
ГДЕ
Товары.Ссылка = &Ссылка
;
ИНДЕКСИРОВАТЬ ПО
Номенклатура
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_Номенклатура.Номенклатура,
ЕСТЬNULL(Цены.Цена, 0) КАК Цена
ИЗ
ВТ_Номенклатура КАК ВТ_Номенклатура
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&ТекущаяДата, ТипЦен = &ТипЦен) КАК Цены
ПО ВТ_Номенклатура.Номенклатура = Цены.Номенклатура
Соблюдение этих принципов позволит вам писать не только корректные, но и быстрые запросы, которые будут надежно работать даже на крупных базах данных.