Как устранить дублирование строк при ЛЕВОМ СОЕДИНЕНИИ в запросах 1С?

← На главную

При работе с запросами в платформе «1С:Предприятие 8» разработчики часто сталкиваются с ситуацией, когда при объединении таблиц с помощью конструкции ЛЕВОЕ СОЕДИНЕНИЕ (LEFT JOIN) результирующая таблица неожиданно увеличивается в объеме, а строки начинают дублироваться. Это классическая проблема реляционных баз данных, которая может приводить к искажению отчетов, неверным расчетам и снижению производительности системы.

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

Выясняем причину: почему ЛЕВОЕ СОЕДИНЕНИЕ удваивает строки?

Давайте вспомним базовый принцип работы связи ЛЕВОЕ СОЕДИНЕНИЕ. Этот механизм выбирает все записи из левой (первичной) таблицы и пытается сопоставить им записи из правой (присоединяемой) таблицы по указанному условию связи (после ключевого слова ПО).

Многие разработчики ошибочно полагают, что левое соединение всегда сохраняет исходное количество строк левой таблицы. Однако это верно только в том случае, если связь между таблицами имеет отношение «один к одному» или «многие к одному». Если же связь переходит в формат «один ко многим», СУБД начинает дублировать строки левой таблицы.

Рассмотрим конкретные технические причины, по которым это происходит:

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

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

Решение 1. Использование составного ключа связи в запросе

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

ПО ТАБЛИЦА_АГЕНТОВ.КонтактноеЛицо = ТАБЛИЦА_КОНТАКТОВ.КонтактноеЛицо

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

Посмотрим на пример правильного построения запроса с составным ключом:


ВЫБРАТЬ
    ТАБЛИЦА_АГЕНТОВ.НомерЗаказа КАК НомерЗаказа,
    ТАБЛИЦА_АГЕНТОВ.Контрагент КАК Контрагент,
    ТАБЛИЦА_АГЕНТОВ.КонтактноеЛицо КАК КонтактноеЛицо,
    ТАБЛИЦА_КОНТАКТОВ.Представление КАК Представление
ИЗ
    ТАБЛИЦА_АГЕНТОВ КАК ТАБЛИЦА_АГЕНТОВ
    ЛЕВОЕ СОЕДИНЕНИЕ ТАБЛИЦА_КОНТАКТОВ КАК ТАБЛИЦА_КОНТАКТОВ
        ПО ТАБЛИЦА_АГЕНТОВ.Контрагент = ТАБЛИЦА_КОНТАКТОВ.Контрагент
        И ТАБЛИЦА_АГЕНТОВ.КонтактноеЛицо = ТАБЛИЦА_КОНТАКТОВ.КонтактноеЛицо

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

Решение 2. Предварительное агрегирование (свертка) правой таблицы

Что делать, если в правой таблице (ТАБЛИЦА_КОНТАКТОВ) на одного контрагента и одно контактное лицо все равно приходится несколько записей (например, несколько телефонов)? В этом случае составной ключ связи не спасет от дублей. Нам нужно предварительно сгруппировать правую таблицу во временной таблице, оставив для каждого ключа только одну уникальную запись.

Рассмотрим подробнее процесс свертки правой таблицы во временную таблицу перед соединением:


// Шаг 1. Создаем уникальную свернутую таблицу контактов
ВЫБРАТЬ
    ВТ_Контакты.Контрагент КАК Контрагент,
    ВТ_Контакты.КонтактноеЛицо КАК КонтактноеЛицо,
    МАКСИМУМ(ВТ_Контакты.Представление) КАК Представление
ПОМЕСТИТЬ ВТ_КонтактыСвернутая
ИЗ
    ТАБЛИЦА_КОНТАКТОВ КАК ВТ_Контакты
СГРУППИРОВАТЬ ПО
    ВТ_Контакты.Контрагент,
    ВТ_Контакты.КонтактноеЛицо
;

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

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

Решение 3. Индексация полей связи во временных таблицах

При соединении двух временных таблиц в СУБД (особенно в высоконагруженных системах под управлением PostgreSQL или MS SQL Server) критически важно помогать планировщику запросов строить оптимальные планы выполнения. Если поля, по которым происходит связь, не проиндексированы, СУБД может выбрать неэффективный алгоритм соединения (например, Nested Loops вместо Hash Match), что приведет к деградации производительности.

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


ВЫБРАТЬ
    ВТ_Контакты.Контрагент КАК Контрагент,
    ВТ_Контакты.КонтактноеЛицо КАК КонтактноеЛицо,
    МАКСИМУМ(ВТ_Контакты.Представление) КАК Представление
ПОМЕСТИТЬ ВТ_КонтактыСвернутая
ИЗ
    ТАБЛИЦА_КОНТАКТОВ КАК ВТ_Контакты
СГРУППИРОВАТЬ ПО
    ВТ_Контакты.Контрагент,
    ВТ_Контакты.КонтактноеЛицо
ИНДЕКСИРОВАТЬ ПО
    Контрагент,
    КонтактноеЛицо

Как корректно выводить пустые значения (NULL) при ЛЕВОМ СОЕДИНЕНИИ?

В процессе обсуждения у автора возник логичный вопрос: как вывести красивую пустую строку или пустую ссылку, если у контрагента из левой таблицы вообще нет контактного лица в правой таблице?

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

Для корректной обработки таких ситуаций в 1С предусмотрена функция ЕСТЬNULL. Однако применять её нужно с умом, учитывая типы данных обрабатываемых полей.

Вариант А. Для строковых полей (Представление, Адрес, Телефон)

Если нам нужно заменить отсутствующее текстовое описание на пустую строку, мы используем замену на "":

ЕСТЬNULL(ТАБЛИЦА_КОНТАКТОВ.Представление, "") КАК Представление

Вариант Б. Для ссылочных полей (СправочникСсылка)

Важное предупреждение: никогда не заменяйте отсутствующую ссылку на пустую строку "" или число 0. Это приведет к ошибке несовместимости типов при выгрузке результата запроса в Таблицу Значений или при дальнейшей обработке. Колонка должна оставаться строго типизированной.

Для сохранения ссылочного типа данных приводите NULL к пустой ссылке конкретного справочника с помощью конструкции ВЫБОР КОГДА ... ТОГДА:


ВЫБОР 
    КОГДА ТАБЛИЦА_КОНТАКТОВ.КонтактноеЛицо ЕСТЬ NULL 
        ТОГДА ЗНАЧЕНИЕ(Справочник.ФизическиеЛица.ПустаяСсылка) 
    ИНАЧЕ ТАБЛИЦА_КОНТАКТОВ.КонтактноеЛицо 
КОНЕЦ КАК КонтактноеЛицо

Применение такой конструкции гарантирует, что колонка КонтактноеЛицо в результирующем наборе данных будет иметь тип СправочникСсылка.ФизическиеЛица, даже если для некоторых строк значение не было найдено.

Давайте объединим все полученные знания в один итоговый, эталонный запрос:


ВЫБРАТЬ
    ТАБЛИЦА_АГЕНТОВ.НомерЗаказа КАК НомерЗаказа,
    ТАБЛИЦА_АГЕНТОВ.Контрагент КАК Контрагент,
    ТАБЛИЦА_АГЕНТОВ.КонтактноеЛицо КАК КонтактноеЛицо,
    ЕСТЬNULL(ВТ_КонтактыСвернутая.Представление, "") КАК ПредставлениеКонтакта
ИЗ
    ТАБЛИЦА_АГЕНТОВ КАК ТАБЛИЦА_АГЕНТОВ
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_КонтактыСвернутая КАК ВТ_КонтактыСвернутая
        ПО ТАБЛИЦА_АГЕНТОВ.Контрагент = ВТ_КонтактыСвернутая.Контрагент
        И ТАБЛИЦА_АГЕНТОВ.КонтактноеЛицо = ВТ_КонтактыСвернутая.КонтактноеЛицо

Используя предложенные методики предварительной группировки, индексации временных таблиц, составных ключей связи и функций обработки NULL, вы навсегда избавитесь от дублирования строк в запросах и обеспечите максимальную производительность вашей базы данных 1С.