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

← На главную

Приветствуем, коллеги! Работа с запросами в платформе 1С — это ежедневная практика любого разработчика, но именно здесь часто скрываются коварные логические ловушки. Одна из самых классических и часто встречающихся проблем — это когда левое соединение (LEFT JOIN) неожиданно начинает вести себя как внутреннее (INNER JOIN), отсекая нужные строки из итогового результата.

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

---

Анализ проблемы: почему «срезается» номенклатура?

Рассмотрим ситуацию, с которой столкнулся автор темы. Ему необходимо решить бизнес-задачу: определить, по каким заказам покупателей на склады поступила вся необходимая номенклатура (товар полностью зарезервирован) и заказ готов к отгрузке.

Для этого в запросе формируются три временные таблицы:

Заказ — остатки по заказам покупателей.
Остатки — товары, находящиеся в резерве на складах под эти заказы.
ЗаказыПоставщикам — ожидаемые поставки по заказам.

В финальном пакете запроса автор соединяет их левым соединением по заказу и номенклатуре, а в секции ГДЕ накладывает следующее условие:


ГДЕ
    Заказ.КоличествоОстаток <= Остатки.КоличествоОстаток
    И ЗаказыПоставщикам.КоличествоОстаток ЕСТЬ NULL

При тестировании выясняется: в исходном заказе покупателя числится 4 позиции номенклатуры. В резерве (таблица Остатки) под этот заказ есть только 3 позиции. Логически заказ не укомплектован (1 позиции не хватает), и он не должен попасть в итоговую выборку. Однако запрос выводит этот заказ, показывая, что количество позиций номенклатуры в нем равно 3!

Выясним причину этого странного поведения СУБД. Все дело в строке:

Заказ.КоличествоОстаток <= Остатки.КоличествоОстаток

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

Затем СУБД переходит к обработке секции ГДЕ. Секция ГДЕ выполняется после физического соединения таблиц. СУБД начинает проверять условие фильтрации для строки, по которой нет резерва:

Заказ.КоличествоОстаток <= NULL

В реляционных базах данных и языке SQL сравнение любого значения с NULL (будь то равенство, неравенство, больше или меньше) всегда возвращает результат UNKNOWN (ложь для фильтра). Строка, не прошедшая проверку в секции ГДЕ, полностью исключается из итогового результата запроса!

Таким образом, строка с отсутствующей на складе номенклатурой просто стирается из выборки. СУБД «видит» только оставшиеся 3 строки, по которым резерв есть. По ним условие сравнения выполняется успешно, строки группируются, и мы получаем ложноположительный результат: заказ якобы полностью готов, но в нем отображаются только 3 позиции вместо исходных 4.

Важное правило разработки в 1С: Наложение любого прямого условия (кроме проверки на ЕСТЬ NULL) на поля правой (присоединяемой) таблицы в секции ГДЕ неявно превращает ЛЕВОЕ соединение во ВНУТРЕННЕЕ.

---

Синтаксическая ошибка с автоименованием полей

Помимо логической ошибки, в исходном запросе автора присутствует критическая синтаксическая ошибка, которая приведет к аварийному завершению работы запроса. Проанализируем третью временную таблицу:


ВЫБРАТЬ
    ЗаказыПоставщикамОстатки.ЗаказПоставщику.ДокументОснование,
    ЗаказыПоставщикамОстатки.КоличествоОстаток
ПОМЕСТИТЬ ЗаказыПоставщикам
ИЗ
    РегистрНакопления.ЗаказыПоставщикам.Остатки КАК ЗаказыПоставщикамОстатки
...

Обратите внимание на поле ЗаказыПоставщикамОстатки.ЗаказПоставщику.ДокументОснование. Поскольку для него не задан псевдоним через ключевое слово КАК, платформа 1С автоматически назначит этому полю имя по его конечному реквизиту — ДокументОснование.

Однако в финальном запросе при описании связи таблиц автор обращается к этому полю следующим образом:

ПО Заказ.ЗаказПокупателя = ЗаказыПоставщикам.ЗаказПоставщикуДокументОснование

Поля с именем ЗаказПоставщикуДокументОснование во временной таблице ЗаказыПоставщикам не существует! Система выдаст ошибку компиляции запроса. Для исправления этой ситуации необходимо всегда явно задавать псевдонимы при обращении через точку к реквизитам объектов во временных таблицах.

---

Как правильно решить задачу контроля укомплектованности заказа?

Простая замена условия в секции ГДЕ на конструкцию с ЕСТЬNULL не решит проблему. Например, если мы напишем:

Заказ.КоличествоОстаток <= ЕСТЬNULL(Остатки.КоличествоОстаток, 0)

Для отсутствующей позиции условие превратится в КоличествоОстаток <= 0. Поскольку требуемое количество по заказу всегда больше нуля (например, требуется 1 шт.), условие 1 <= 0 вернет ложь, и строка снова отсечется из результата. Мы опять получим неверный отчет.

Рассмотрим два надежных и методологически верных способа решения этой задачи.

Вариант А. Проверка дефицита через секцию ИМЕЮЩИЕ (Наиболее лаконичный)

Этот способ позволяет решить задачу в один проход в финальном запросе. Мы соединяем таблицы левым соединением, вычисляем дефицит (разницу между требуемым количеством и резервом) по каждой строке, а затем группируем результат по заказам. Заказ считается полностью укомплектованным, если максимальный дефицит по любой из его строк равен нулю или меньше.

Рассмотрим пример реализации этого подхода по шагам:


ВЫБРАТЬ
    Заказ.ЗаказПокупателя КАК ЗаказПокупателя
ИЗ
    Заказ КАК Заказ
    
    // Присоединяем остатки резервов по заказу и номенклатуре
    ЛЕВОЕ СОЕДИНЕНИЕ Остатки КАК Остатки
        ПО Заказ.ЗаказПокупателя = Остатки.ДокументРезерва
        И Заказ.Номенклатура = Остатки.Номенклатура
        
    // Присоединяем заказы поставщикам для проверки ожидаемых поступлений
    ЛЕВОЕ СОЕДИНЕНИЕ ЗаказыПоставщикам КАК ЗаказыПоставщикам
        ПО Заказ.ЗаказПокупателя = ЗаказыПоставщикам.ДокументОснование
        
СГРУППИРОВАТЬ ПО
    Заказ.ЗаказПокупателя

ИМЕЮЩИЕ
    // Вычисляем дефицит: Требуется - Резерв. 
    // Если резерва нет (NULL), считаем его равным 0.
    // Максимальный дефицит по всем строкам заказа должен быть <= 0 (то есть дефицита нет ни по одной позиции).
    МАКСИМУМ(Заказ.КоличествоОстаток - ЕСТЬNULL(Остатки.КоличествоОстаток, 0)) <= 0
    
    // Дополнительно проверяем, что по заказу нет ожидаемых (неисполненных) заказов поставщикам
    И МАКСИМУМ(ЕСТЬNULL(ЗаказыПоставщикам.КоличествоОстаток, 0)) = 0

Преимущества метода: Запрос выглядит очень компактно, легко читается и не требует создания дополнительных временных таблиц для фильтрации.

Вариант Б. Метод исключения (Двухэтапный запрос)

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

Выбираем абсолютно все уникальные активные заказы покупателей во временную таблицу.
Во вторую временную таблицу выбираем только "проблемные" заказы — те, по которым есть дефицит хотя бы по одной строке (т.е. зарезервировано меньше, чем требуется) или по которым еще висят активные заказы поставщикам.
В финальной выборке из таблицы всех заказов вычитаем проблемные заказы через левое соединение с фильтром ГДЕ ПроблемныеЗаказы.Заказ ЕСТЬ NULL.

Посмотрим на практическую реализацию этого метода:


// Шаг 1. Получаем список заказов, где есть хоть какой-то дефицит
ВЫБРАТЬ РАЗЛИЧНЫЕ
    Заказ.ЗаказПокупателя КАК ЗаказПокупателя
ПОМЕСТИТЬ ЗаказыСДефицитом
ИЗ
    Заказ КАК Заказ
    ЛЕВОЕ СОЕДИНЕНИЕ Остатки КАК Остатки
        ПО Заказ.ЗаказПокупателя = Остатки.ДокументРезерва
        И Заказ.Номенклатура = Остатки.Номенклатура
ГДЕ
    // Находим строки, где заказано больше, чем реально зарезервировано
    Заказ.КоличествоОстаток > ЕСТЬNULL(Остатки.КоличествоОстаток, 0)

ОБЪЕДИНИТЬ

ВЫБРАТЬ РАЗЛИЧНЫЕ
    ЗаказыПоставщикам.ДокументОснование
ИЗ
    ЗаказыПоставщикам КАК ЗаказыПоставщикам
;

////////////////////////////////////////////////////////////////////////////////
// Шаг 2. Выбираем только те заказы, которые НЕ попали в список проблемных
ВЫБРАТЬ РАЗЛИЧНЫЕ
    ВсеЗаказы.ЗаказПокупателя КАК ЗаказПокупателя
ИЗ
    Заказ КАК ВсеЗаказы
    ЛЕВОЕ СОЕДИНЕНИЕ ЗаказыСДефицитом КАК ЗаказыСДефицитом
        ПО ВсеЗаказы.ЗаказПокупателя = ЗаказыСДефицитом.ЗаказПокупателя
ГДЕ
    // Если заказа нет в таблице дефицитных, значит он полностью готов!
    ЗаказыСДефицитом.ЗаказПокупателя ЕСТЬ NULL

Преимущества метода: СУБД строит максимально эффективные планы запросов, так как операции соединения множеств по индексам происходят мгновенно, а сложные агрегатные функции не используются.

---

Дополнительные рекомендации по оптимизации запроса в 1С

Если оставить исходный запрос автора темы без изменений (даже исправив логику соединения), он будет работать критически медленно на реальной базе данных из-за архитектурных ошибок проектирования запросов. Разберем, как сделать этот запрос профессиональным и быстрым.

1. Использование параметров виртуальных таблиц

Посмотрим, как у автора настроена выборка из виртуальной таблицы остатков:


ИЗ
    РегистрНакопления.ЗаказыПокупателей.Остатки КАК ЗаказыПокупателейОстатки
ГДЕ
    ЗаказыПокупателейОстатки.Номенклатура.Услуга = ЛОЖЬ
    И ЗаказыПокупателейОстатки.ЗаказПокупателя.Ответственный = &Ответственный
    И ЗаказыПокупателейОстатки.ЗаказПокупателя.ВнутреннийСтатусЗаказаПокупателя = ЗНАЧЕНИЕ(Перечисление.СтатусЗаказаПокупателя.Запущен)

В чем критическая ошибка: Фильтрация данных производится в секции ГДЕ. Это заставляет СУБД сначала прочитать абсолютно все остатки по регистру из базы данных в оперативную память, построить гигантскую виртуальную таблицу и только потом начать ее фильтровать.

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

Перепишем запрос правильно:


ИЗ
    РегистрНакопления.ЗаказыПокупателей.Остатки(
        ,
        Номенклатура.Услуга = ЛОЖЬ
            И ЗаказПокупателя.Ответственный = &Ответственный
            И ЗаказПокупателя.ВнутреннийСтатусЗаказаПокупателя = ЗНАЧЕНИЕ(Перечисление.СтатусЗаказаПокупателя.Запущен)
    ) КАК ЗаказыПокупателейОстатки

2. Индексация временных таблиц

В финальном пакете запроса мы соединяем временные таблицы Заказ, Остатки и ЗаказыПоставщикам. Если в этих таблицах будет содержаться более нескольких сотен строк, СУБД начнет выполнять сканирование таблиц (Table Scan), что приведет к колоссальным потерям времени.

Чтобы этого избежать, временные таблицы обязательно нужно проиндексировать по полям связи. Для этого в конце описания создания каждой временной таблицы мы добавляем конструкцию ИНДЕКСИРОВАТЬ ПО:


ВЫБРАТЬ
    ЗаказыПокупателейОстатки.ЗаказПокупателя КАК ЗаказПокупателя,
    ЗаказыПокупателейОстатки.Номенклатура КАК Номенклатура,
    ЗаказыПокупателейОстатки.КоличествоОстаток КАК КоличествоОстаток
ПОМЕСТИТЬ Заказ
ИЗ
    РегистрНакопления.ЗаказыПокупателей.Остатки(
        ,
        Номенклатура.Услуга = ЛОЖЬ
            И ЗаказПокупателя.Ответственный = &Ответственный
            И ЗаказПокупателя.ВнутреннийСтатусЗаказаПокупателя = ЗНАЧЕНИЕ(Перечисление.СтатусЗаказаПокупателя.Запущен)
    ) КАК ЗаказыПокупателейОстатки

ИНДЕКСИРОВАТЬ ПО
    ЗаказПокупателя,
    Номенклатура
;

Аналогично следует проиндексировать таблицу Остатки по полям ДокументРезерва и Номенклатура.

---

Резюме

Мы успешно проанализировали логику работы левых соединений в СУБД, выявили причину "пропажи" строк с номенклатурой и исправили синтаксические ошибки. Помните: при работе с левыми соединениями всегда внимательно следите за тем, какие условия вы помещаете в секцию ГДЕ. Используйте секцию ИМЕЮЩИЕ или метод исключения дефицитных заказов для точного контроля укомплектованности, а также никогда не пренебрегайте параметризацией виртуальных таблиц и индексацией временных таблиц в 1С. Желаем вам быстрой работы ваших запросов!