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