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

← На главную

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

Анализ типичных ошибок при отборе пустых табличных частей

Рассмотрим распространенные ошибки, которые не позволяют получить корректный результат:

Передача значения NULL через параметр запроса. Попытка передать Неопределено или NULL через вызов метода УстановитьПараметр("Ссылка", Ссылка) с последующим условием ГДЕ Поле = &Ссылка приводит к тому, что запрос возвращает пустой результат. В реляционной алгебре и языке запросов 1С любое скалярное сравнение со значением NULL (через операторы =, <> и др.) всегда возвращает Ложь (неопределенность). Проверять отсутствие записей можно исключительно с помощью логической конструкции ЕСТЬ NULL (или IS NULL).
Обращение к табличной части через точку от ссылки. Запись вида ТрудовойДоговор.Отпуска.Ссылка обращается к табличной части как к вложенной таблице, а не как к самостоятельному источнику данных. В запросе 1С табличные части объектов конфигурации представляют собой отдельные таблицы СУБД, к которым необходимо обращаться напрямую (например, Документ.ТрудовойДоговор.Отпуска).
Неправильный порядок таблиц в левом соединении. Если расположить таблицу табличной части слева, а таблицу шапки документа справа при ЛЕВОМ СОЕДИНЕНИИ, то в выборку попадут только те документы, у которых строки в табличной части уже существуют. Чтобы найти незаполненные объекты, основной объект (шапка) обязательно должен выступать ведущей (левой) таблицей.
Проверка на NULL поля левой таблицы вместо правой. В секции ГДЕ условие ЕСТЬ NULL должно проверяться строго по полю присоединенной табличной части (правой таблицы), а не по полю шапки документа. Поле ссылки шапки в левой таблице никогда не будет равно NULL.

Способ 1. Классическое решение через ЛЕВОЕ СОЕДИНЕНИЕ и ЕСТЬ NULL

Выясним причину эффективности этого подхода: при левом соединении к каждой строке шапки документа пытаются подобраться связанные строки из табличной части. Если у документа нет строк в табличной части, на месте полей табличной части образуется значение NULL. Отфильтровав результат по условию отсутствия ссылки в присоединенной таблице, мы получаем ровно те объекты, у которых табличная часть пуста.

Посмотрим на практический пример запроса для документа ТрудовойДоговор и его табличной части Отпуска:


Запрос = Новый Запрос;
Запрос.Текст = 
    "ВЫБРАТЬ
    |	ТрудовойДоговор.Ссылка КАК Ссылка,
    |	ТрудовойДоговор.Номер КАК Номер,
    |	ТрудовойДоговор.Дата КАК Дата,
    |	ТрудовойДоговор.Сотрудник КАК Сотрудник
    |ИЗ
    |	Документ.ТрудовойДоговор КАК ТрудовойДоговор
    |	ЛЕВОЕ СОЕДИНЕНИЕ Документ.ТрудовойДоговор.Отпуска КАК ТрудовойДоговорОтпуска
    |	ПО ТрудовойДоговор.Ссылка = ТрудовойДоговорОтпуска.Ссылка
    |ГДЕ
    |	ТрудовойДоговорОтпуска.Ссылка ЕСТЬ NULL";

Результат = Запрос.Выполнить();
Выборка = Результат.Выбрать();
Пока Выборка.Следующий() Цикл
    // Обработка полученных данных
КонецЦикла;

Обратите внимание: в секции ПО связь выполняется строго по полю Ссылка (у табличной части это ссылка на документ-владелец: ТрудовойДоговор.Ссылка = ТрудовойДоговорОтпуска.Ссылка), а в секции ГДЕ проверяется ТрудовойДоговорОтпуска.Ссылка ЕСТЬ NULL.

Способ 2. Использование условия НЕ В с подзапросом

Разберем альтернативный вариант, когда мы отбираем все документы, ссылка на которые отсутствует в списке ссылок заполненных табличных частей. Для этого применим конструкцию НЕ В с подзапросом, выбирающим только уникальные ссылки из ТЧ:


ВЫБРАТЬ
	ТрудовойДоговор.Ссылка КАК Ссылка,
	ТрудовойДоговор.Номер КАК Номер,
	ТрудовойДоговор.Дата КАК Дата
ИЗ
	Документ.ТрудовойДоговор КАК ТрудовойДоговор
ГДЕ
	НЕ ТрудовойДоговор.Ссылка В
		(ВЫБРАТЬ РАЗЛИЧНЫЕ
			ТЧОтпуска.Ссылка
		ИЗ
			Документ.ТрудовойДоговор.Отпуска КАК ТЧОтпуска)

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

Способ 3. Использование предиката НЕ СУЩЕСТВУЕТ (NOT EXISTS)

Проанализируем конструкцию с коррелированным подзапросом и оператором НЕ СУЩЕСТВУЕТ. В современных СУБД (таких как MS SQL Server и PostgreSQL) данный запрос часто компилируется в наиболее эффективный план выполнения (Left Anti-Semi-Join). Оптимизатор прерывает сканирование табличной части сразу же, как только находит первую подходящую запись для документа, не перебирая остальные строки.


ВЫБРАТЬ
	ТрудовойДоговор.Ссылка КАК Ссылка,
	ТрудовойДоговор.Номер КАК Номер,
	ТрудовойДоговор.Дата КАК Дата
ИЗ
	Документ.ТрудовойДоговор КАК ТрудовойДоговор
ГДЕ
	НЕ СУЩЕСТВУЕТ
		(ВЫБРАТЬ
			1
		ИЗ
			Документ.ТрудовойДоговор.Отпуска КАК ТЧОтпуска
		ГДЕ
			ТЧОтпуска.Ссылка = ТрудовойДоговор.Ссылка)

Оптимизация при работе с большими объемами данных

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

В таких случаях рекомендуется использовать пакетный запрос с временной таблицей:

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

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


ВЫБРАТЬ РАЗЛИЧНЫЕ
	ТЧОтпуска.Ссылка КАК Ссылка
ПОМЕСТИТЬ ВТ_ЗаполненныеДокументы
ИЗ
	Документ.ТрудовойДоговор.Отпуска КАК ТЧОтпуска
ИНДЕКСИРОВАТЬ ПО
	Ссылка
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	ТрудовойДоговор.Ссылка КАК Ссылка,
	ТрудовойДоговор.Номер КАК Номер,
	ТрудовойДоговор.Дата КАК Дата
ИЗ
	Документ.ТрудовойДоговор КАК ТрудовойДоговор
	ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ЗаполненныеДокументы КАК ВТ
	ПО ТрудовойДоговор.Ссылка = ВТ.Ссылка
ГДЕ
	ВТ.Ссылка ЕСТЬ NULL

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

Особенность фильтрации с дополнительными условиями: если требуется отобрать документы, у которых нет строк с определенным условием (например, нет отпусков конкретного типа), фильтр по реквизиту табличной части следует помещать в условие соединения ПО, а не в блок ГДЕ. Если написать условие отбора по правой таблице в блоке ГДЕ, левое соединение неявно превратится во внутреннее, и все пустые объекты будут исключены из результата.