Как правильно использовать полное соединение в запросах 1С для сверки данных?

← На главную

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

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

Анализ проблемы: почему пропадают данные при полном соединении?

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


ВЫБРАТЬ
    ТЗ.КодУПП,
    ТЗ.НаименованиеУПП,
    ТЗ.КодДоговорУПП,
    ТЗ.НаименованиеДоговорУПП,
    ДоговорыКонтрагентов.Владелец.Код КАК КодБК,
    ДоговорыКонтрагентов.Владелец.Наименование КАК НаименованиеБК,
    ДоговорыКонтрагентов.Код КАК КодДоговорБК,
    ДоговорыКонтрагентов.Наименование КАК НаименованиеДоговорБК
ИЗ
    ТЗ КАК ТЗ
        ПОЛНОЕ СОЕДИНЕНИЕ Справочник.ДоговорыКонтрагентов КАК ДоговорыКонтрагентов
        ПО ТЗ.КодУПП = ДоговорыКонтрагентов.Владелец.Код
            И ТЗ.КодДоговорУПП = ДоговорыКонтрагентов.Код
ГДЕ
    ДоговорыКонтрагентов.ПометкаУдаления = ЛОЖЬ
    И ДоговорыКонтрагентов.ЭтоГруппа = ЛОЖЬ
    И ДоговорыКонтрагентов.Владелец.ЭтоГруппа = ЛОЖЬ
    И ДоговорыКонтрагентов.Владелец.ПометкаУдаления = ЛОЖЬ

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

Когда СУБД выполняет полное внешнее соединение, для тех записей из первой таблицы (ТЗ), которым не нашлось пары во второй таблице (Справочник), все поля второй таблицы заполняются значением NULL. Следовательно, поле ДоговорыКонтрагентов.ПометкаУдаления для таких "одиноких" записей будет содержать NULL.

Далее срабатывает секция ГДЕ. Система проверяет условие: NULL = ЛОЖЬ. Результат этого выражения — ложь. Таким образом, условие в секции ГДЕ безжалостно отсекает все строки, которые не имеют пары. Из-за этого наше ПОЛНОЕ СОЕДИНЕНИЕ фактически деградирует и превращается во ВНУТРЕННЕЕ СОЕДИНЕНИЕ, оставляя только те записи, которые присутствуют одновременно в обеих базах.

Решение 1: Перенос условий соединения

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

Посмотрим на исправленный вариант запроса:


ВЫБРАТЬ
    ТЗ.КодУПП,
    ТЗ.НаименованиеУПП,
    ТЗ.КодДоговорУПП,
    ТЗ.НаименованиеДоговорУПП,
    ДоговорыКонтрагентов.Владелец.Код КАК КодБК,
    ДоговорыКонтрагентов.Владелец.Наименование КАК НаименованиеБК,
    ДоговорыКонтрагентов.Код КАК КодДоговорБК,
    ДоговорыКонтрагентов.Наименование КАК НаименованиеДоговорБК
ИЗ
    ТЗ КАК ТЗ
        ПОЛНОЕ СОЕДИНЕНИЕ Справочник.ДоговорыКонтрагентов КАК ДоговорыКонтрагентов
        ПО ТЗ.КодУПП = ДоговорыКонтрагентов.Владелец.Код
            И ТЗ.КодДоговорУПП = ДоговорыКонтрагентов.Код
            И ДоговорыКонтрагентов.ПометкаУдаления = ЛОЖЬ
            И ДоговорыКонтрагентов.ЭтоГруппа = ЛОЖЬ
            И ДоговорыКонтрагентов.Владелец.ЭтоГруппа = ЛОЖЬ
            И ДоговорыКонтрагентов.Владелец.ПометкаУдаления = ЛОЖЬ

Этот запрос отработает корректно и вернет все записи. Однако, стоит отметить, что с точки зрения стандартов разработки 1С и производительности СУБД, перегружать условие соединения логическими фильтрами — не лучшая практика (Best Practice).

Решение 2: Фильтрация до соединения (Best Practice)

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


// Подготовка данных справочника
ВЫБРАТЬ
    ДоговорыКонтрагентов.Код КАК КодДоговора,
    ДоговорыКонтрагентов.Наименование КАК НаименованиеДоговора,
    ДоговорыКонтрагентов.Владелец.Код КАК КодВладельца,
    ДоговорыКонтрагентов.Владелец.Наименование КАК НаименованиеВладельца
ПОМЕСТИТЬ ВТ_ДоговорыТекущейБазы
ИЗ
    Справочник.ДоговорыКонтрагентов КАК ДоговорыКонтрагентов
ГДЕ
    НЕ ДоговорыКонтрагентов.ПометкаУдаления
    И НЕ ДоговорыКонтрагентов.ЭтоГруппа
    И НЕ ДоговорыКонтрагентов.Владелец.ЭтоГруппа
    И НЕ ДоговорыКонтрагентов.Владелец.ПометкаУдаления
;
// Соединение двух подготовленных наборов
ВЫБРАТЬ
    ТЗ.КодУПП,
    ТЗ.КодДоговорУПП,
    ВТ_ДоговорыТекущейБазы.КодВладельца КАК КодБК,
    ВТ_ДоговорыТекущейБазы.КодДоговора КАК КодДоговорБК
ИЗ
    ТЗ КАК ТЗ
        ПОЛНОЕ СОЕДИНЕНИЕ ВТ_ДоговорыТекущейБазы КАК ВТ_ДоговорыТекущейБазы
        ПО ТЗ.КодУПП = ВТ_ДоговорыТекущейБазы.КодВладельца
            И ТЗ.КодДоговорУПП = ВТ_ДоговорыТекущейБазы.КодДоговора

Такой запрос читается гораздо легче, работает предсказуемо и позволяет оптимизатору СУБД строить более эффективный план выполнения.

Как красиво вывести результат в отчет СКД с помощью ЕСТЬNULL

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

Для того чтобы объединить значения в одну колонку, обязательно нужно использовать функцию ЕСТЬNULL. Проанализируем ее применение:


ВЫБРАТЬ
    ЕСТЬNULL(ТЗ.КодУПП, ВТ_Договоры.КодВладельца) КАК ИтоговыйКодВладельца,
    ЕСТЬNULL(ТЗ.КодДоговорУПП, ВТ_Договоры.КодДоговора) КАК ИтоговыйКодДоговора,
    ТЗ.КодДоговорУПП КАК ДоговорВБазе1,
    ВТ_Договоры.КодДоговора КАК ДоговорВБазе2
// ... (остальной текст запроса)

Функция работает просто: если запись пришла из ТЗ и поле ТЗ.КодУПП заполнено, берется оно. Если записи в ТЗ не было (поле равно NULL), система подставит код из справочника текущей базы. Это позволяет сформировать красивую и логичную структуру данных для последующей группировки в схеме компоновки данных.

Решение 3: Сверка данных через ОБЪЕДИНЕНИЕ (Альтернативный и безопасный путь)

Хотя полное соединение решает задачу, для сверки двух баз данных часто применяется более производительный и защищенный от ошибок метод — использование оператора ОБЪЕДИНИТЬ ВСЕ.

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

Делаем запрос к первой таблице (ТЗ) и добавляем в выборку фиктивное числовое поле-индикатор (например, 1 КАК БазаИсточник).
Делаем аналогичный запрос ко второй таблице (Справочник) и добавляем индикатор с противоположным знаком (например, -1 КАК БазаИсточник).
Связываем эти два запроса через ОБЪЕДИНИТЬ ВСЕ.
Оборачиваем получившееся объединение во вложенный запрос (или помещаем во временную таблицу).
В основном запросе выполняем группировку данных по ключевым полям (КодВладельца, КодДоговора), а наше поле-индикатор суммируем.

Посмотрим на пример такого элегантного решения:


ВЫБРАТЬ
    ДанныеСверки.КодВладельца КАК КодВладельца,
    ДанныеСверки.КодДоговора КАК КодДоговора,
    СУММА(ДанныеСверки.Индикатор) КАК Статус
ИЗ
    (ВЫБРАТЬ
        ТЗ.КодУПП КАК КодВладельца,
        ТЗ.КодДоговорУПП КАК КодДоговора,
        1 КАК Индикатор
    ИЗ
        ТЗ КАК ТЗ
    
    ОБЪЕДИНИТЬ ВСЕ
    
    ВЫБРАТЬ
        ДоговорыКонтрагентов.Владелец.Код,
        ДоговорыКонтрагентов.Код,
        -1
    ИЗ
        Справочник.ДоговорыКонтрагентов КАК ДоговорыКонтрагентов
    ГДЕ
        НЕ ДоговорыКонтрагентов.ПометкаУдаления
        // ... (остальные условия)
    ) КАК ДанныеСверки

СГРУППИРОВАТЬ ПО
    ДанныеСверки.КодВладельца,
    ДанныеСверки.КодДоговора

Как интерпретировать результат такого запроса?

В результате мы получим плоскую таблицу со всеми договорами и полем Статус. Анализировать его очень легко:

Если сумма (Статус) равна 0 — запись идентична и присутствует в обеих базах (1 + (-1) = 0).
Если сумма равна 1 — запись есть только в первой базе (ТЗ), а во второй отсутствует.
Если сумма равна -1 — запись есть только во второй базе (текущем справочнике), но ее нет в ТЗ.
Если сумма больше 1 или меньше -1 — это явный сигнал о наличии дублей в одной из баз по ключевым полям.

Этот метод избавляет разработчика от необходимости бороться с NULL, исключает риск размножения строк при ошибках связи и работает, как правило, быстрее полного соединения. Результат такого запроса идеально ложится в СКД, позволяя пользователю с помощью простых отборов фильтровать расхождения (отбор: Статус <> 0).