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

← На главную

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

Рассмотрим подробно, почему возникает такая ошибка, проанализируем поведение СУБД при работе со значениями NULL и разберем несколько способов корректной настройки запроса.

Анализ исходной ситуации и текста запроса

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


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

Экспериментальным путем было установлено, что новый сотрудник (основное место работы) пропадает из результата при включении условий отбора по полю ВложенныйЗапрос.Состояние.

Выясним причину: как работает NULL в языке запросов 1С

Проанализируем механику работы оператора ЛЕВОЕ СОЕДИНЕНИЕ и секции ГДЕ:

В конфигурации ЗУП 2.5 при повторном приеме на работу или переходе с совместительства создается новый элемент справочника СотрудникиОрганизаций.
В регистре сведений СостояниеРаботниковОрганизаций записи появляются при проведении документов отклонений (больничные, отпуска) или кадровых документов. Если по новому сотруднику никаких неявок или отпусков еще не регистрировалось, в этом регистре запись по данному физическому лицу отсутствует.
При выполнении ЛЕВОЕ СОЕДИНЕНИЕ для основного регистра данные находятся, а из присоединяемого регистра подставляется значение NULL.
Когда в секции ГДЕ применяется условие вида Состояние <> Значение, СУБД сравнивает NULL <> Значение. В трехзначной логике SQL результат любого сравнения с NULL дает неопределенность (UNKNOWN), что секцией ГДЕ трактуется как ЛОЖЬ.

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

Разберем по шагам способы решения проблемы

Существует несколько правильных подходов для сохранения строк основной таблицы при фильтрации по присоединяемым регистрам.

Способ 1. Добавление проверки ЕСТЬ NULL в условии ГДЕ

Самое простое решение — явно указать в секции ГДЕ, что полное отсутствие записи в правой таблице (значение NULL) также является допустимым результатом:


ГДЕ
    ВложенныйЗапрос.ПричинаИзмененияСостояния <> ЗНАЧЕНИЕ(Перечисление.ПричиныИзмененияСостояния.Увольнение)
    И (ВложенныйЗапрос.Состояние ЕСТЬ NULL
        ИЛИ (ВложенныйЗапрос.Состояние <> ЗНАЧЕНИЕ(Перечисление.СостоянияРаботникаОрганизации.ОтпускПоБеременностиИРодам)
            И ВложенныйЗапрос.Состояние <> ЗНАЧЕНИЕ(Перечисление.СостоянияРаботникаОрганизации.ОтпускПоУходуЗаРебенком)))

Способ 2. Использование функции ЕСТЬNULL()

Вместо разворачивания скобок с оператором ИЛИ можно подменить значение NULL на заведомо нейтральное значение (например, пустую ссылку перечисления) с помощью функции ЕСТЬNULL():


ГДЕ
    ВложенныйЗапрос.ПричинаИзмененияСостояния <> ЗНАЧЕНИЕ(Перечисление.ПричиныИзмененияСостояния.Увольнение)
    И ЕСТЬNULL(ВложенныйЗапрос.Состояние, ЗНАЧЕНИЕ(Перечисление.СостоянияРаботникаОрганизации.ПустаяСсылка)) <> ЗНАЧЕНИЕ(Перечисление.СостоянияРаботникаОрганизации.ОтпускПоБеременностиИРодам)
    И ЕСТЬNULL(ВложенныйЗапрос.Состояние, ЗНАЧЕНИЕ(Перечисление.СостоянияРаботникаОрганизации.ПустаяСсылка)) <> ЗНАЧЕНИЕ(Перечисление.СостоянияРаботникаОрганизации.ОтпускПоУходуЗаРебенком)

Способ 3. Перенос условий в предложение соединения ПО

Если условие фильтрации правой таблицы перенести непосредственно в конструкцию ПО при описании левого соединения, строки основной таблицы не будут удаляться из выборки, даже если они не удовлетворяют условию правой таблицы:


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

Способ 4. Перенос отбора в параметры виртуальной таблицы

Наиболее оптимальный с точки зрения производительности вариант — исключение лишних записей непосредственно на уровне виртуальной таблицы СрезПоследних. В этом случае СУБД считывает из базы меньший объем данных:


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

При таком подходе проверка на NULL в блоке ГДЕ вовсе не потребуется, а производительность запроса увеличится за счет снижения нагрузки на сервер баз данных.