При работе с языком запросов 1С часто возникает задача объединить данные из двух разных источников, например, фактические остатки товаров на складе и ожидаемые поступления. Идеальным инструментом для этого кажется ПОЛНОЕ СОЕДИНЕНИЕ, ведь оно должно показывать все записи из обеих таблиц, даже если для них нет пары. Однако на практике можно столкнуться с ситуацией, когда записи из одной из таблиц таинственным образом исчезают из результата. Давайте разберемся, почему так происходит и как этого избежать.
Проанализируем типичную ситуацию. Есть два запроса: один получает ожидаемые поступления, другой — свободные остатки. Мы хотим объединить их в одну таблицу.
Запрос 1: Ожидаемые поступления
ВЫБРАТЬ
ДоступныеОстаткиПланируемыхПоступлений.Характеристика,
ДоступныеОстаткиПланируемыхПоступлений.Склад,
ДоступныеОстаткиПланируемыхПоступлений.Количество
ПОМЕСТИТЬ ОжидаемыеПоступления
ИЗ
РегистрСведений.ДоступныеОстаткиПланируемыхПоступлений КАК ДоступныеОстаткиПланируемыхПоступлений
ГДЕ
ДоступныеОстаткиПланируемыхПоступлений.Номенклатура = &Ссылка
И ДоступныеОстаткиПланируемыхПоступлений.Количество > 0
Запрос 2: Объединение с остатками (неверная версия)
ВЫБРАТЬ
СвободныеОстаткиОстатки.Характеристика,
СвободныеОстаткиОстатки.Склад,
СвободныеОстаткиОстатки.ВНаличииОстаток,
ОжидаемыеПоступления.Количество
ИЗ
РегистрНакопления.СвободныеОстатки.Остатки(&ТекДата, ) КАК СвободныеОстаткиОстатки
ПОЛНОЕ СОЕДИНЕНИЕ ОжидаемыеПоступления КАК ОжидаемыеПоступления
ПО (ОжидаемыеПоступления.Характеристика = СвободныеОстаткиОстатки.Характеристика)
И (ОжидаемыеПоступления.Склад = СвободныеОстаткиОстатки.Склад)
ГДЕ
СвободныеОстаткиОстатки.Номенклатура = &Ссылка
Проблема в том, что если по номенклатуре &Ссылка есть ожидаемое поступление, но нет остатков, эта строка не попадет в итоговый результат. Выясним причину и рассмотрим правильные подходы к решению этой задачи.
Корень проблемы кроется в порядке выполнения операторов в SQL-запросе. Секция ГДЕ (WHERE) применяется после того, как выполнено соединение таблиц (JOIN). Давайте пошагово разберем, что происходит:
ПОЛНОЕ СОЕДИНЕНИЕ. Система берет все строки из таблицы остатков и все строки из таблицы поступлений и пытается их сопоставить по характеристике и складу.СвободныеОстаткиОстатки.Характеристика, СвободныеОстаткиОстатки.Склад, СвободныеОстаткиОстатки.Номенклатура и т.д.), для этой строки будут иметь значение NULL.ГДЕ. Теперь система фильтрует полученный набор данных по условию ГДЕ СвободныеОстаткиОстатки.Номенклатура = &Ссылка.Именно на последнем шаге и происходит "магия". Для той самой строки, которая была только в поступлениях, поле СвободныеОстаткиОстатки.Номенклатура равно NULL. Сравнение NULL = &Ссылка всегда дает результат ЛОЖЬ (не ИСТИНА и не NULL). В итоге эта строка отбрасывается фильтром.
Таким образом, условие в секции ГДЕ, наложенное на поле одной из таблиц, фактически превращает ПОЛНОЕ СОЕДИНЕНИЕ в ЛЕВОЕ или ПРАВОЕ (в нашем случае — в ПРАВОЕ, так как остаются только строки, где есть данные из правой таблицы `СвободныеОстаткиОстатки`).
Чтобы отбор не "ломал" логику внешнего соединения, его нужно применять не ко всему результату, а к каждой таблице до момента их объединения. Для этого есть два правильных места.
Способ А: Параметры виртуальной таблицы (предпочтительный)
Самый правильный и производительный способ — передать условия отбора прямо в параметры виртуальной таблицы. Это позволяет СУБД сначала отобрать нужные записи и только потом выполнять соединение.
ИЗ
РегистрНакопления.СвободныеОстатки.Остатки(&ТекДата, Номенклатура = &Ссылка) КАК СвободныеОстаткиОстатки
ПОЛНОЕ СОЕДИНЕНИЕ ОжидаемыеПоступления КАК ОжидаемыеПоступления
...
Способ Б: Условия в секции ПО
Условия отбора также можно перенести в секцию ПО (ON). Это тоже сработает, но использование параметров виртуальных таблиц считается более идиоматичным для платформы 1С.
Важный момент! Отбор нужно применить к обеим таблицам, если это необходимо. В нашем исходном примере отбор по номенклатуре уже был в первом запросе для временной таблицы, а для второго его нужно было добавить в параметры виртуальной таблицы.
Даже после исправления отбора мы столкнемся со второй проблемой. Если мы перенесем условие и уберем ГДЕ, результат может выглядеть так:
Характеристика|Склад|ВНаличииОстаток|Количество S, белый|Магазин|8| L, белый|Магазин2|7| | | |50
Почему поля Характеристика и Склад пустые для строки с поступлением? Потому что в секции ВЫБРАТЬ мы указали СвободныеОстаткиОстатки.Характеристика и СвободныеОстаткиОстатки.Склад. А для строки, которая есть только в поступлениях, эти поля равны NULL.
Чтобы это исправить, нужно использовать функцию ЕСТЬNULL() (аналог COALESCE в стандартном SQL). Она принимает два параметра и возвращает первый из них, если он не NULL, в противном случае — второй.
Итоговый правильный запрос:
Давайте соберем все исправления в один корректный запрос, объединив обе таблицы без использования временной.
ВЫБРАТЬ
ЕСТЬNULL(СвободныеОстатки.Характеристика, ОжидаемыеПоступления.Характеристика) КАК Характеристика,
ЕСТЬNULL(СвободныеОстатки.Склад, ОжидаемыеПоступления.Склад) КАК Склад,
ЕСТЬNULL(СвободныеОстатки.ВНаличииОстаток, 0) КАК ВНаличииОстаток,
ЕСТЬNULL(ОжидаемыеПоступления.Количество, 0) КАК КоличествоПоступление
ИЗ
РегистрНакопления.СвободныеОстатки.Остатки(
&ТекДата,
Номенклатура = &Ссылка
) КАК СвободныеОстатки
ПОЛНОЕ СОЕДИНЕНИЕ РегистрСведений.ДоступныеОстаткиПланируемыхПоступлений КАК ОжидаемыеПоступления
ПО СвободныеОстатки.Характеристика = ОжидаемыеПоступления.Характеристика
И СвободныеОстатки.Склад = ОжидаемыеПоступления.Склад
И СвободныеОстатки.Номенклатура = ОжидаемыеПоступления.Номенклатура
ГДЕ
ОжидаемыеПоступления.Номенклатура = &Ссылка
Проанализируем этот код:
Характеристика, Склад) с помощью ЕСТЬNULL. Если запись есть в остатках, возьмется значение оттуда. Если нет — из поступлений.ЕСТЬNULL, заменяя возможное значение NULL на 0 для корректного отображения и дальнейших вычислений.ГДЕ, так как она не является виртуальной. Однако для единообразия и ясности его можно было бы также перенести в условия соединения ПО.Такой запрос даст именно тот результат, который требовался изначально, корректно отображая все данные.
В некоторых случаях конструкция с ПОЛНОЕ СОЕДИНЕНИЕ может быть громоздкой. Существует элегантная альтернатива — оператор ОБЪЕДИНИТЬ ВСЕ (UNION ALL). Этот подход часто бывает более читаемым и гибким.
Суть метода в следующем:
ОБЪЕДИНИТЬ ВСЕ.Характеристика, Склад) и суммируем числовые поля.Посмотрим на пример:
ВЫБРАТЬ
РезультатОбъединения.Характеристика,
РезультатОбъединения.Склад,
СУММА(РезультатОбъединения.ВНаличииОстаток) КАК ВНаличииОстаток,
СУММА(РезультатОбъединения.КоличествоПоступление) КАК КоличествоПоступление
ИЗ
(ВЫБРАТЬ
СвободныеОстатки.Характеристика КАК Характеристика,
СвободныеОстатки.Склад КАК Склад,
СвободныеОстатки.ВНаличииОстаток КАК ВНаличииОстаток,
0 КАК КоличествоПоступление
ИЗ
РегистрНакопления.СвободныеОстатки.Остатки(&ТекДата, Номенклатура = &Ссылка) КАК СвободныеОстатки
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
Поступления.Характеристика,
Поступления.Склад,
0,
Поступления.Количество
ИЗ
РегистрСведений.ДоступныеОстаткиПланируемыхПоступлений КАК Поступления
ГДЕ
Поступления.Номенклатура = &Ссылка
И Поступления.Количество > 0) КАК РезультатОбъединения
СГРУППИРОВАТЬ ПО
РезультатОбъединения.Характеристика,
РезультатОбъединения.Склад
Этот метод полностью избавляет от сложностей с NULL значениями и поведением ГДЕ при внешних соединениях, а в некоторых сценариях может оказаться даже производительнее.