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