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

← На главную

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

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

Анализ проблемы: почему ЛЕВОЕ СОЕДИНЕНИЕ превращается во ВНУТРЕННЕЕ

Рассмотрим исходный запрос, в котором возникла проблема. В данном примере соединяются две выборки из регистра сведений АдресныеОбъекты: одна выступает в роли субъектов РФ (уровень 1), а вторая — в роли районов (уровень 3).


ВЫБРАТЬ
    АдресныеОбъектыСубъектРФ.Наименование КАК СубъектРФ,
    АдресныеОбъектыРайон.Наименование КАК Район
ИЗ
    РегистрСведений.АдресныеОбъекты КАК АдресныеОбъектыСубъектРФ
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.АдресныеОбъекты КАК АдресныеОбъектыРайон
    ПО АдресныеОбъектыСубъектРФ.КодСубъектаРФ = АдресныеОбъектыРайон.КодСубъектаРФ
ГДЕ
    АдресныеОбъектыРайон.Актуален
    И АдресныеОбъектыСубъектРФ.Актуален
    И АдресныеОбъектыРайон.Уровень = 3
    И АдресныеОбъектыСубъектРФ.Уровень = 1

На первый взгляд запрос написан логично: используется ЛЕВОЕ СОЕДИНЕНИЕ, которое должно сохранить все строки из левой таблицы (АдресныеОбъектыСубъектРФ). Однако Санкт-Петербург в результат не попадает. Выясним причину такого поведения.

В реляционных базах данных применяется трехзначная логика SQL: любое выражение сравнения со значением NULL (например, NULL = ИСТИНА или NULL = 3) возвращает не ЛОЖЬ, а значение НЕИЗВЕСТНО (UNKNOWN).

Порядок выполнения запроса СУБД следующий:

Выполняется соединение таблиц по условию в секции ПО. Для субъекта РФ без районов (Санкт-Петербург) формируется строка, где поля правой таблицы (АдресныеОбъектыРайон) заполнены значениями NULL.
После этого СУБД переходит к фильтрации всего результата в секции ГДЕ.
СУБД проверяет условие АдресныеОбъектыРайон.Актуален = ИСТИНА. Так как значение поля равно NULL, выражение вычисляется как UNKNOWN.
Секция ГДЕ пропускает в итоговую выборку только те строки, для которых совокупное условие возвращает ИСТИНА. Значения UNKNOWN отбрасываются.

Таким образом, наложение отбора на поля правой таблицы в секции ГДЕ фактически превращает ЛЕВОЕ СОЕДИНЕНИЕ во ВНУТРЕННЕЕ СОЕДИНЕНИЕ (INNER JOIN).

Способ 1. Перенос условий правой таблицы в секцию ПО (условия связи)

Разберем классическое и наиболее правильное решение этой проблемы. Все условия, которые относятся к правой (присоединяемой) таблице, необходимо перенести из секции ГДЕ в условие связи ПО.

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


ВЫБРАТЬ
    АдресныеОбъектыСубъектРФ.Наименование КАК СубъектРФ,
    АдресныеОбъектыРайон.Наименование КАК Район
ИЗ
    РегистрСведений.АдресныеОбъекты КАК АдресныеОбъектыСубъектРФ
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.АдресныеОбъекты КАК АдресныеОбъектыРайон
        ПО АдресныеОбъектыСубъектРФ.КодСубъектаРФ = АдресныеОбъектыРайон.КодСубъектаРФ
        И АдресныеОбъектыРайон.Актуален = ИСТИНА
        И АдресныеОбъектыРайон.Уровень = 3
ГДЕ
    АдресныеОбъектыСубъектРФ.Актуален = ИСТИНА
    И АдресныеОбъектыСубъектРФ.Уровень = 1

Как это работает:

Фильтры АдресныеОбъектыСубъектРФ.Уровень = 1 и Актуален находятся в секции ГДЕ, так как они отсекают лишние записи из основной (левой) таблицы.
Условия на Уровень = 3 и Актуален для районов находятся в секции ПО. СУБД сначала отфильтрует правые записи, подходящие под эти условия, и только потом попытается привязать их к левой таблице.
Если у левой записи (Санкт-Петербург) нет соответствующей правой записи с уровнем 3, строка все равно попадет в выборку, а в поле Район будет выведено значение NULL.

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

Рассмотрим альтернативный вариант, который позволяет оставить условия в секции ГДЕ, обработав возможные NULL с помощью функции ЕСТЬNULL.


ВЫБРАТЬ
    АдресныеОбъектыСубъектРФ.Наименование КАК СубъектРФ,
    АдресныеОбъектыРайон.Наименование КАК Район
ИЗ
    РегистрСведений.АдресныеОбъекты КАК АдресныеОбъектыСубъектРФ
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.АдресныеОбъекты КАК АдресныеОбъектыРайон
        ПО АдресныеОбъектыСубъектРФ.КодСубъектаРФ = АдресныеОбъектыРайон.КодСубъектаРФ
ГДЕ
    АдресныеОбъектыСубъектРФ.Актуален
    И АдресныеОбъектыСубъектРФ.Уровень = 1
    И ЕСТЬNULL(АдресныеОбъектыРайон.Актуален, ИСТИНА)
    И ЕСТЬNULL(АдресныеОбъектыРайон.Уровень, 3) = 3

В данном случае выражение ЕСТЬNULL(АдресныеОбъектыРайон.Уровень, 3) при отсутствии пары подставит значение 3, благодаря чему условие сравнения вернет ИСТИНА.

Важное предупреждение о производительности: Использование функций вроде ЕСТЬNULL в секции ГДЕ не рекомендуется на больших объемах данных. Это приводит к так называемому «заглаживанию» индексов на стороне СУБД (Index Scan вместо Index Seek), что значительно замедляет выполнение запроса на миллионах записей классификатора адресных объектов (ФИАС / ГАР).

Особенности ПОЛНОГО СОЕДИНЕНИЯ (FULL OUTER JOIN)

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

Рассмотрим пример запроса с полным соединением:


ВЫБРАТЬ
    АдресныеОбъектыУлицы.Наименование КАК Улица,
    АдресныеОбъектыГорода.Наименование КАК Город
ИЗ
    РегистрСведений.АдресныеОбъекты КАК АдресныеОбъектыГорода
    ПОЛНОЕ СОЕДИНЕНИЕ РегистрСведений.АдресныеОбъекты КАК АдресныеОбъектыУлицы
        ПО (АдресныеОбъектыУлицы.КодСубъектаРФ = АдресныеОбъектыГорода.КодСубъектаРФ)
        И (АдресныеОбъектыУлицы.Уровень = 7)
        И (АдресныеОбъектыГорода.Уровень = 4)
        И (АдресныеОбъектыУлицы.Актуален)
        И (АдресныеОбъектыГорода.Актуален)

В результатом такого запроса могут стать строки, где у города выводятся записи с Уровень = 1 (Регионы), хотя в секции ПО четко написано АдресныеОбъектыГорода.Уровень = 4.

Почему так происходит:
Механика ПОЛНОГО СОЕДИНЕНИЯ обязывает СУБД вернуть все строки из левой таблицы и все строки из правой таблицы. Условия в секции ПО при полном соединении выступают исключительно в роли правила сопоставления записей друг с другом, но не отсекают сами записи. По этой причине строка левой таблицы с Уровень = 1 не найдет себе пару (так как условие Уровень = 4 не выполнено), но выведется в результат со значениями NULL в полях правой таблицы.

Оптимальное решение: Использование временных таблиц

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

Разберем по шагам создание правильного пакета запросов:

В первой временной таблице выбираем и фильтруем только субъекты РФ (уровень 1).
Во второй временной таблице выбираем и фильтруем только районы (уровень 3).
В финальном запросе соединяем подготовленные и уменьшенные выборки.

Посмотрим на реализацию данного подхода:


// Шаг 1: Подготовка субъектов РФ
ВЫБРАТЬ
    АдресныеОбъекты.КодСубъектаРФ КАК КодСубъектаРФ,
    АдресныеОбъекты.Наименование КАК СубъектРФ
ПОМЕСТИТЬ ВТ_СубъектыРФ
ИЗ
    РегистрСведений.АдресныеОбъекты КАК АдресныеОбъекты
ГДЕ
    АдресныеОбъекты.Уровень = 1
    И АдресныеОбъекты.Актуален
ИНДЕКСИРОВАТЬ ПО
    КодСубъектаРФ
;

////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Подготовка районов
ВЫБРАТЬ
    АдресныеОбъекты.КодСубъектаРФ КАК КодСубъектаРФ,
    АдресныеОбъекты.Наименование КАК Район
ПОМЕСТИТЬ ВТ_Районы
ИЗ
    РегистрСведений.АдресныеОбъекты КАК АдресныеОбъекты
ГДЕ
    АдресныеОбъекты.Уровень = 3
    И АдресныеОбъекты.Актуален
ИНДЕКСИРОВАТЬ ПО
    КодСубъектаРФ
;

////////////////////////////////////////////////////////////////////////////////
// Шаг 3: Итоговое соединение очищенных данных
ВЫБРАТЬ
    ВТ_СубъектыРФ.СубъектРФ КАК СубъектРФ,
    ЕСТЬNULL(ВТ_Районы.Район, "--- Нет района ---") КАК Район
ИЗ
    ВТ_СубъектыРФ КАК ВТ_СубъектыРФ
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Районы КАК ВТ_Районы
        ПО ВТ_СубъектыРФ.КодСубъектаРФ = ВТ_Районы.КодСубъектаРФ

Преимущества использования пакета запросов с временными таблицами:

Читаемость и простота отладки: Каждый шаг изолирован, логику отбора условий легко проверить отдельно.
Гарантия корректности: Ошибки типа «превращение левого соединения во внутреннее» становятся физически невозможными, так как фильтрация по ГДЕ происходит на этапах формирования временных таблиц.
Высокая производительность: Создание индексов (конструкция ИНДЕКСИРОВАТЬ ПО) позволяет СУБД соединять таблицы в памяти за минимальное время.