При разработке и доработке конфигураций на платформе 1С:Предприятие программисты регулярно сталкиваются с необходимостью сопоставления данных. Типичная задача — сравнить две табличные части разных документов (например, документ «Дополнительные затраты» и «Расходная накладная») и выявить позиции номенклатуры, которые присутствуют в одном документе, но отсутствуют в другом. На первый взгляд задача кажется простой, но попытки решить её «в лоб» часто приводят к неверным результатам или пустой таблице значений.
В этой статье мы подробно разберем, почему стандартные операторы сравнения не работают при простом сопоставлении строк, проанализируем поведение СУБД при работе со специальным значением NULL, а также рассмотрим профессиональные подходы к оптимизации таких запросов с использованием временных таблиц.
Рассмотрим типичную ошибку, которую часто совершают начинающие разработчики. Попытка связать две таблицы документов через запятую в секции ИЗ с наложением условия неравенства в секции ГДЕ выглядит следующим образом:
ВЫБРАТЬ
ДополнительныеЗатратыСписокНоменклатуры.Номенклатура КАК Номенклатура
ИЗ
Документ.ДополнительныеЗатраты.СписокНоменклатуры КАК ДополнительныеЗатратыСписокНоменклатуры,
Документ.РасходнаяНакладная КАК РасходнаяНакладная
ГДЕ
ДополнительныеЗатратыСписокНоменклатуры.Ссылка = &СсылкаДок
И ДополнительныеЗатратыСписокНоменклатуры.Номенклатура <> РасходнаяНакладная.СписокНоменклатуры.Номенклатура
И РасходнаяНакладная.СписокНоменклатуры.Ссылка = &Ссылка
При выполнении этого запроса система возвращает практически всю номенклатуру из первого документа, включая те позиции, которые на самом деле есть во втором. Выясним причину такого поведения.
Когда мы указываем таблицы через запятую в секции ИЗ, СУБД выполняет так называемое полное декартово произведение (Cross Join). Каждая строка из первой таблицы сопоставляется с каждой строкой из второй таблицы. Если в первом документе у нас есть позиции «Товар 1» и «Товар 2», а во втором — «Товар 2» и «Товар 3», система сформирует следующие пары сравнения:
В результате проверки условий строка с «Товар 2» все равно попадет в итоговую выборку, так как она успешно прошла проверку по паре №4. Таким образом, использовать оператор неравенства <> для поиска уникальных строк в связанных таблицах категорически нельзя.
Для корректного решения задачи необходимо использовать механизм соединений таблиц. Однако здесь разработчиков подстерегает еще одна важная особенность работы баз данных — логика значения NULL.
Значение NULL в SQL — это не пустая ссылка, не число 0 и не пустая строка. Это специальный маркер, обозначающий отсутствие данных (неизвестное значение). С точки зрения трехзначной логики SQL, любой результат сравнения с NULL (будь то равенство, неравенство, больше или меньше) всегда возвращает значение «Ложь» (или «Неизвестно», что при фильтрации приравнивается к «Ложь»).
Рассмотрим следующие правила:
Номенклатура = NULL возвращает Ложь.Номенклатура <> NULL возвращает Ложь.NULL = NULL возвращает Ложь.Единственным правильным способом проверки поля на отсутствие связи при соединении таблиц является использование специализированных операторов ЕСТЬ NULL или его отрицания НЕ ЕСТЬ NULL.
Попытаемся применить левое соединение (Left Join) табличных частей документов с проверкой на ЕСТЬ NULL. Рассмотрим запрос, который изначально возвращает пустую таблицу значений:
ВЫБРАТЬ
ДополнительныеЗатратыСписокНоменклатуры.Номенклатура КАК Номенклатура
ИЗ
Документ.ДополнительныеЗатраты.СписокНоменклатуры КАК ДополнительныеЗатратыСписокНоменклатуры
ЛЕВОЕ СОЕДИНЕНИЕ Документ.РасходнаяНакладная.СписокНоменклатуры КАК РасходнаяНакладнаяСписокНоменклатуры
ПО ДополнительныеЗатратыСписокНоменклатуры.Номенклатура = РасходнаяНакладнаяСписокНоменклатуры.Номенклатура
ГДЕ
ДополнительныеЗатратыСписокНоменклатуры.Ссылка = &СсылкаДок
И РасходнаяНакладнаяСписокНоменклатуры.Ссылка = &Ссылка
И РасходнаяНакладнаяСписокНоменклатуры.Номенклатура ЕСТЬ NULL
Проанализируем ситуацию и выясним, почему этот запрос не возвращает данные. Проблема кроется в порядке выполнения операций СУБД:
ЛЕВОЕ СОЕДИНЕНИЕ по равенству номенклатуры. Для тех позиций первого документа, которых нет в расходной накладной, все поля правой таблицы (включая поле Ссылка) заполняются значениями NULL.ГДЕ.РасходнаяНакладнаяСписокНоменклатуры.Ссылка = &Ссылка. Для всех несовпавших строк поле Ссылка равно NULL. Из-за этого выражение NULL = &Ссылка возвращает Ложь.Для того чтобы запрос отработал корректно, нам необходимо перенести условие фильтрации правой таблицы по конкретному документу из секции ГДЕ непосредственно в параметры связи ПО. В таком случае СУБД сначала отфильтрует строки расходной накладной по нужной ссылке, и только затем выполнит левое соединение.
Посмотрим на рабочий вариант запроса:
ВЫБРАТЬ
ДополнительныеЗатратыСписокНоменклатуры.Номенклатура КАК Номенклатура
ИЗ
Документ.ДополнительныеЗатраты.СписокНоменклатуры КАК ДополнительныеЗатратыСписокНоменклатуры
ЛЕВОЕ СОЕДИНЕНИЕ Документ.РасходнаяНакладная.СписокНоменклатуры КАК РасходнаяНакладнаяСписокНоменклатуры
ПО ДополнительныеЗатратыСписокНоменклатуры.Номенклатура = РасходнаяНакладнаяСписокНоменклатуры.Номенклатура
И РасходнаяНакладнаяСписокНоменклатуры.Ссылка = &Ссылка
ГДЕ
ДополнительныеЗатратыСписокНоменклатуры.Ссылка = &СсылкаДок
И РасходнаяНакладнаяСписокНоменклатуры.Номенклатура ЕСТЬ NULL
Этот запрос отрабатывает быстро и возвращает точный список номенклатуры первого документа, которой нет во втором.
Существует еще один способ решения этой задачи, который часто интуитивно понятен разработчикам — это использование конструкции НЕ В с вложенным запросом. Рассмотрим этот вариант:
ВЫБРАТЬ
ДЗ.Номенклатура КАК Номенклатура
ИЗ
Документ.ДополнительныеЗатраты.СписокНоменклатуры КАК ДЗ
ГДЕ
ДЗ.Ссылка = &СсылкаДок
И ДЗ.Номенклатура НЕ В (
ВЫБРАТЬ
РН.Номенклатура
ИЗ
Документ.РасходнаяНакладная.СписокНоменклатуры КАК РН
ГДЕ
РН.Ссылка = &Ссылка
)
Важное предупреждение: При написании таких запросов нужно быть предельно осторожными. Если во вложенном запросе по какой-то причине окажется значение NULL (например, если в табличной части накладной будет пустая строка номенклатуры), оператор НЕ В вернет пустой результат для всего запроса. Это стандартное поведение SQL-систем. В реальной практике 1С для справочников это редкая ситуация, но ее обязательно нужно учитывать.
В реальных проектах с высокой нагрузкой прямое соединение табличных частей физических таблиц документов считается неоптимальным подходом. СУБД тратит много ресурсов на обработку лишних индексов и сканирование физических таблиц документов целиком.
Согласно стандартам разработки фирмы «1С», для обеспечения максимальной производительности и исключения избыточных блокировок данных необходимо использовать пакетные запросы и временные таблицы. Разберем этот профессиональный подход по шагам:
Реализуем этот алгоритм в виде пакетного запроса:
// Шаг 1: Выборка номенклатуры первого документа
ВЫБРАТЬ РАЗЛИЧНЫЕ
ДополнительныеЗатратыСписокНоменклатуры.Номенклатура КАК Номенклатура
ПОМЕСТИТЬ ВТ_НоменклатураДЗ
ИЗ
Документ.ДополнительныеЗатраты.СписокНоменклатуры КАК ДополнительныеЗатратыСписокНоменклатуры
ГДЕ
ДополнительныеЗатратыСписокНоменклатуры.Ссылка = &СсылкаДок
ИНДЕКСИРОВАТЬ ПО
Номенклатура
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Выборка номенклатуры второго документа
ВЫБРАТЬ РАЗЛИЧНЫЕ
РасходнаяНакладнаяСписокНоменклатуры.Номенклатура КАК Номенклатура
ПОМЕСТИТЬ ВТ_НоменклатураРН
ИЗ
Документ.РасходнаяНакладная.СписокНоменклатуры КАК РасходнаяНакладнаяСписокНоменклатуры
ГДЕ
РасходнаяНакладнаяСписокНоменклатуры.Ссылка = &Ссылка
ИНДЕКСИРОВАТЬ ПО
Номенклатура
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 3: Соединение легких временных таблиц
ВЫБРАТЬ
ВТ_НоменклатураДЗ.Номенклатура КАК Номенклатура
ИЗ
ВТ_НоменклатураДЗ КАК ВТ_НоменклатураДЗ
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_НоменклатураРН КАК ВТ_НоменклатураРН
ПО ВТ_НоменклатураДЗ.Номенклатура = ВТ_НоменклатураРН.Номенклатура
ГДЕ
ВТ_НоменклатураРН.Номенклатура ЕСТЬ NULL
Использование ключевого слова РАЗЛИЧНЫЕ позволяет убрать дубли уже на этапе заполнения временных таблиц, что снижает нагрузку на процессор при слиянии данных. Индексирование полей связи гарантирует, что СУБД моментально найдет соответствия без полного сканирования таблиц.
Нередко перед программистом встает более сложная задача: нужно не просто найти отсутствующие товары, но и сравнить количественные показатели в двух документах, выявив расхождения. Для этих целей отлично подходит метод объединения таблиц с математическим знаком «минус» для сопоставляемых показателей.
Проанализируем пример такого запроса:
ВЫБРАТЬ
ТаблицаСравнения.Номенклатура КАК Номенклатура,
СУММА(ТаблицаСравнения.Количество) КАК КоличествоРазница
ИЗ
(ВЫБРАТЬ
ДЗ.Номенклатура КАК Номенклатура,
ДЗ.Количество КАК Количество
ИЗ
Документ.ДополнительныеЗатраты.СписокНоменклатуры КАК ДЗ
ГДЕ
ДЗ.Ссылка = &СсылкаДок
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
РН.Номенклатура,
-РН.Количество
ИЗ
Документ.РасходнаяНакладная.СписокНоменклатуры КАК РН
ГДЕ
РН.Ссылка = &Ссылка) КАК ТаблицаСравнения
СГРУППИРОВАТЬ ПО
ТаблицаСравнения.Номенклатура
ИМЕЮЩИЕ
СУММА(ТаблицаСравнения.Количество) <> 0
Разберем логику работы этого алгоритма:
СГРУППИРОВАТЬ ПО объединяет одинаковые строки номенклатуры, складывая их количества. Если объемы совпадают, сумма становится равной нулю.ИМЕЮЩИЕ отсекает нулевые результаты, оставляя только расхождения.Если полученная разница положительна — значит, товара в первом документе больше, либо его вообще нет во втором. Если разница отрицательна — позиция преобладает во втором документе.