Как в запросе 1С проверить вхождение всех строк одной таблицы в другую таблицу?

При разработке алгоритмов проверки остатков, сопоставления спецификаций или анализа загруженных данных часто возникает задача: убедиться, что каждая строка из исходной таблицы (назовем её Таблица1) имеет соответствующую строку во второй таблице (Таблица2) по заданному набору условий. В кодовой части такую проверку можно выполнить простым перебором в цикле, однако при больших объемах данных перебор работает медленно. Разберем, как реализовать подобную проверку средствами языка запросов 1С максимально быстро и эффективно.

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

Таблица1.н = Таблица2.н И Таблица1.х = Таблица2.х И Таблица1.к >= Таблица2.к

Основной способ: ЛЕВОЕ СОЕДИНЕНИЕ и поиск ненайденных строк

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

Для этого мы соединяем первую таблицу со второй с помощью конструкции ЛЕВОЕ СОЕДИНЕНИЕ по всем требуемым условиям, а в секции ГДЕ фильтруем результат по отсутствию данных из правой таблицы (проверка на ЕСТЬ NULL).

Посмотрим на пример текста запроса:

ВЫБРАТЬ ПЕРВЫЕ 1
    1 КАК Поле1
ИЗ
    Таблица1 КАК Т1
    ЛЕВОЕ СОЕДИНЕНИЕ Таблица2 КАК Т2
        ПО Т1.н = Т2.н
        И Т1.х = Т2.х
        И Т1.к >= Т2.к
ГДЕ
    Т2.н ЕСТЬ NULL

Разберем по шагам, как работает этот алгоритм:

1. Базовой таблицей выступает именно Таблица1, вхождение которой мы проверяем. Если поменять таблицы местами, логика проверки нарушится.
2. В секции ПО указываются все правила соответствия: точные равенства ключевых полей и условия сравнения числовых характеристик.
3. Если для какой-то строки из Т1 во второй таблице подходящей записи не нашлось, все поля таблицы Т2 для этой строки заполняются значением NULL.
4. Условие ГДЕ Т2.н ЕСТЬ NULL отсекает все успешно сопоставленные записи и оставляет только те строки из первой таблицы, для которых не найдено пары.

Оптимизация проверки в коде 1С: использование метода Пустой()

Обратим внимание на директиву ВЫБРАТЬ ПЕРВЫЕ 1. Для ответа на вопрос «все ли строки вошли» нам не требуется подсчитывать полное количество расхождений или получать детальный список отсутствующих позиций. Достаточно обнаружить хотя бы один факт несовпадения.

Когда СУБД встречает директиву ПЕРВЫЕ 1, выполнение запроса прерывается сразу после обнаружения первой «одинокой» строки, что экономит процессорное время и оперативную память сервера.

В модуле на встроенном языке проверка результата выполняется через метод РезультатЗапроса.Пустой():

Запрос = Новый Запрос;
Запрос.Текст = 
    "ВЫБРАТЬ ПЕРВЫЕ 1
    |	1 КАК Маркер
    |ИЗ
    |	ВТ_Таблица1 КАК Т1
    |	ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Таблица2 КАК Т2
    |		ПО Т1.н = Т2.н
    |		И Т1.х = Т2.х
    |		И Т1.к >= Т2.к
    |ГДЕ
    |	Т2.н ЕСТЬ NULL";

// Заполняем параметры или менеджер временных таблиц
// ...

Результат = Запрос.Выполнить();

Если Результат.Пустой() Тогда
    // Ни одной записи с NULL не найдено: первая таблица полностью входит во вторую
    ВсеСтрокиВходят = Истина;
Иначе
    // Найдена хотя бы одна строка без соответствия
    ВсеСтрокиВходят = Ложь;
КонецЕсли;

Нюансы работы с временными таблицами и индексами

Если сопоставляемые данные формируются динамически (например, выгружаются из объектов ТаблицаЗначений), перед выполнением соединения их помещают во временные таблицы с помощью инструкции ПОМЕСТИТЬ.

При сложном соединении с предикатом неравенства (Т1.к >= Т2.к) оптимизатор СУБД может выбрать неэффективный план выполнения, если по таблицам отсутствуют индексы. Рассмотрим правила создания индексов для временных таблиц в таком сценарии:

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

Опасность значений NULL в ключевых полях

Выясним причину возможной ошибки, если в исходных данных поля н или х содержат значения NULL. В стандарте SQL выражение NULL = NULL возвращает Ложь (трехзначная логика). В результате строки с пустыми значениями не свяжутся между собой, даже если они визуально совпадают в обеих таблицах, и попадут в выборку как ненайденные.

Если в ключевых полях допускаются пустые значения, их необходимо обрабатывать функцией ЕСТЬNULL непосредственно в секции соединения:

ПО ЕСТЬNULL(Т1.н, """") = ЕСТЬNULL(Т2.н, """")
И ЕСТЬNULL(Т1.х, 0) = ЕСТЬNULL(Т2.х, 0)
И Т1.к >= Т2.к

Почему не подходит альтернатива со счетчиками (+1 и -1)?

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

Важно понимать: данный способ работает исключительно при условии полного равенства всех анализируемых колонок. При наличии нестрогого неравенства (Т1.к >= Т2.к) объединение и группировка неприменимы, поскольку разные числовые значения поля к образуют отдельные группы и не смогут свернуться в одну строку. Поэтому для задач с диапазонами или неравенствами единственным корректным решением остается ЛЕВОЕ СОЕДИНЕНИЕ.