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