Как правильно соединить остатки и цены в запросе 1С, чтобы не пропадали товары с нулевым остатком?

← На главную

В практике разработки на платформе 1С:Предприятие 8 одна из самых частых задач — построение отчета, объединяющего данные из нескольких регистров. Классический пример: нам нужно вывести список всех товаров, на которые установлены цены, и рядом отобразить их текущие остатки на складе. При этом бизнес-логика часто требует, чтобы товар попадал в отчет даже в том случае, если его остаток равен нулю, но цена при этом задана.

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

Анализируем причину проблемы: почему LEFT JOIN превращается в INNER JOIN

Давайте проанализируем исходный код запроса, который вызвал затруднения. В нем используется конструкция:


ИЗ
    РегистрСведений.СтоимостьНоменклатуры.СрезПоследних КАК СтоимостьНоменклатурыСрезПоследних
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.Склад.Остатки(&НаДату, ) КАК СкладОстатки
    ПО (СтоимостьНоменклатурыСрезПоследних.Номенклатура = СкладОстатки.Номенклатура)
ГДЕ
    СкладОстатки.Отдел = &Отдел
    И СтоимостьНоменклатурыСрезПоследних.Размер = СкладОстатки.Размер
    И СтоимостьНоменклатурыСрезПоследних.Цвет = СкладОстатки.Цвет

Выясним причину, по которой этот запрос работает неверно. Когда мы используем ЛЕВОЕ СОЕДИНЕНИЕ, система берет все записи из левой таблицы (цены) и пытается подобрать к ним соответствующие записи из правой таблицы (остатки). Если остатка нет, во всех полях правой таблицы (включая СкладОстатки.Отдел, СкладОстатки.Размер и СкладОстатки.Цвет) будет стоять значение NULL.

Проблема кроется в секции ГДЕ. Когда SQL-сервер выполняет проверку СкладОстатки.Отдел = &Отдел, он подставляет NULL на место отдела для отсутствующих остатков. Выражение NULL = &Отдел всегда возвращает результат Ложь (или Unknown). В итоге все строки, для которых не нашлось остатка, отсекаются этим условием. Таким образом, ваше «Левое соединение» превратилось во «Внутреннее соединение».

Решение №1: Перенос фильтров в параметры виртуальной таблицы

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

Посмотрим, как изменится текст запроса:


ИЗ
    РегистрСведений.СтоимостьНоменклатуры.SрезПоследних КАК СтоимостьНоменклатурыСрезПоследних
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.Склад.Остатки(&НаДату, Отдел = &Отдел) КАК СкладОстатки
    ПО СтоимостьНоменклатурыСрезПоследних.Номенклатура = СкладОстатки.Номенклатура
    И СтоимостьНоменклатурыСрезПоследних.Размер = СкладОстатки.Размер
    И СтоимостьНоменклатурыСрезПоследних.Цвет = СкладОстатки.Цвет

В данном примере мы сделали две важные вещи:

Условие Отдел = &Отдел перенесено во второй параметр виртуальной таблицы Остатки. Теперь система сначала отфильтрует остатки по складу, а уже потом будет соединять их с ценами.
Условия по измерениям Размер и Цвет перенесены из блока ГДЕ в блок ПО (условие связи). Это критически важно: теперь, если размер или цвет в остатках не найдены, строка из таблицы цен все равно останется в результате, просто поле остатка будет пустым.

Решение №2: Обработка пустых значений функцией ЕСТЬNULL

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

Проанализируем, как использовать функцию ЕСТЬNULL для улучшения результата. В блоке ВЫБРАТЬ следует заменить прямое обращение к полю остатка на конструкцию:


ВЫБРАТЬ
    СтоимостьНоменклатурыСрезПоследних.Номенклатура КАК Наименование,
    ЕСТЬNULL(СкладОстатки.КолВоОстаток, 0) КАК Остаток,
    СтоимостьНоменклатурыСрезПоследних.Розничная КАК Цена

Функция ЕСТЬNULL проверяет: если первый параметр равен NULL, она возвращает второй параметр (в нашем случае 0). Это гарантирует стабильность работы кода при расчете итогов или выгрузке в табличный документ.

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

Рассмотрим дополнительные аспекты, которые помогут сделать ваш запрос не только правильным, но и быстрым.

1. Использование временных таблиц. Если ваш запрос становится сложным (например, добавляется третье соединение с регистром «Акции»), настоятельно рекомендуем использовать временные таблицы. Сначала выберите цены во временную таблицу, проиндексируйте её по номенклатуре, а затем соединяйте с остатками. Это значительно облегчает работу планировщика SQL-сервера.


ВЫБРАТЬ
    Цены.Номенклатура,
    Цены.Цена
ПОМЕСТИТЬ ВТ_Цены
ИЗ
    РегистрСведений.СтоимостьНоменклатуры.СрезПоследних КАК Цены;
    
ИНДЕКСИРОВАТЬ ПО
    ВТ_Цены.Номенклатура;

2. Проблема «Чтения через точку». В исходном запросе автора часто встречается обращение СтоимостьНоменклатурыСрезПоследних.Номенклатура.Код. Помните, что каждое такое обращение — это неявное левое соединение со справочником на уровне СУБД. Если вам нужно много реквизитов номенклатуры (код, штрих-код, единица измерения), лучше один раз явно соединиться со справочником Номенклатура или выбрать эти данные на первом этапе во временную таблицу.

3. Соответствие характеристик. Если в одном регистре Цвет заполнен, а в другом — нет (имеет значение Справочник.Цвета.ПустаяСсылка), то условие СтоимостьНоменклатуры.Цвет = СкладОстатки.Цвет не сработает, так как NULL не равен пустой ссылке. Всегда следите за чистотой данных и единообразием ведения учета по аналитическим разрезам (Размер, Цвет, Характеристика).

4. Выбор ведущей таблицы. В данной задаче «ведущей» является таблица цен. Но если бы вам нужно было увидеть абсолютно все товары, даже те, на которые не установлена цена и которых нет на складе, за основу (самую левую таблицу) следовало бы взять справочник Номенклатура, к которому последовательно присоединять цены и остатки.

Итоговый правильный вариант запроса

Сведем все исправления в один качественный программный код:


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

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ЦеныОстатки.Номенклатура,
    ЦеныОстатки.Цена,
    ЦеныОстатки.Остаток,
    ЕСТЬNULL(Акции.Скидка, 0) КАК Скидка
ИЗ
    ЦеныОстатки КАК ЦеныОстатки
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Акции КАК Акции
    ПО ЦеныОстатки.Номенклатура = Акции.Номенклатура

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