При проектировании запросов в системе «1С:Предприятие» разработчики часто используют конструкцию ЛЕВОЕ СОЕДИНЕНИЕ (LEFT JOIN). По задумке, это соединение должно возвращать абсолютно все строки из левой (основной) таблицы, дополняя их данными из правой таблицы при наличии совпадений по условию связи. Если совпадений нет, поля правой таблицы должны заполниться значениями NULL.
Однако на практике начинающие (и даже опытные) разработчики часто сталкиваются с неприятными сюрпризами: строки из левой таблицы неожиданно пропадают из итоговой выборки, превращая соединение во внутреннее, либо строки начинают неконтролируемо дублироваться («размножаться»). В этой статье мы подробно разберем, почему это происходит, как ведет себя СУБД при выполнении таких запросов, и как сделать ваше левое соединение действительно «левым».
Рассмотрим ситуацию, с которой столкнулся автор темы. В его запросе во второй временной таблице ВТ_Оклады выполняется левое соединение таблицы документа с регистром сведений:
ВЫБРАТЬ
МАКСИМУМ(СведенияОСотрудниках.Период) КАК Период,
СведенияОСотрудниках.Сотрудник,
ВТ_ДатнныеДокумента.НомерСтроки
ПОМЕСТИТЬ ВТ_Оклады
ИЗ
ВТ_ДатнныеДокумента КАК ВТ_ДатнныеДокумента
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.СведенияОСотрудниках КАК СведенияОСотрудниках
ПО ВТ_ДатнныеДокумента.Сотрудник = СведенияОСотрудниках.Сотрудник
И ВТ_ДатнныеДокумента.ДатаНачала >= СведенияОСотрудниках.Период
ГДЕ
НЕ СведенияОСотрудниках.Сотрудник ЕСТЬ NULL
СГРУППИРОВАТЬ ПО
СведенияОСотрудниках.Сотрудник,
ВТ_ДатнныеДокумента.НомерСтроки ;
Как справедливо заметил участник обсуждения в Сообщении 6, виновником «исчезновения» строк является условие в секции фильтрации: ГДЕ НЕ СведенияОСотрудниках.Сотрудник ЕСТЬ NULL. Чтобы понять, почему это происходит, проанализируем логический порядок выполнения операций СУБД при обработке запроса:
ВТ_ДатнныеДокумента с правой таблицей РегистрСведений.СведенияОСотрудниках по условиям, указанным после ключевого слова ПО. Для тех сотрудников, по которым нет записей в регистре, формируется строка, где все поля из регистра (включая СведенияОСотрудниках.Сотрудник) принимают значение NULL. На данном этапе левое соединение отработало корректно.ГДЕ. СУБД анализирует условие НЕ СведенияОСотрудниках.Сотрудник ЕСТЬ NULL. Для тех строк, где связь не состоялась и поле сотрудника из регистра заполнилось значением NULL, это условие возвращает ЛОЖЬ. В результате все такие строки безжалостно отсекаются из итогового результата.Таким образом, наложение любого жесткого условия (кроме проверки на равенство NULL) на поля правой таблицы в секции ГДЕ мгновенно превращает ваше левое соединение в обычное ВНУТРЕННЕЕ СОЕДИНЕНИЕ (INNER JOIN). Вся суть левого соединения при этом полностью теряется.
Давайте выясним принципиальное различие в работе условий в зависимости от того, где они написаны. Проанализируем отличный пример, приведенный в Сообщении 10. Нам нужно получить список всех контрагентов и сумму их заказов за определенный период.
Вариант 1 (Правильный): Условие внутри секции связи ПО
ВЫБРАТЬ
Контрагенты.Ссылка КАК Контрагент,
ЕСТЬNULL(СУММА(ЗаказПоставщику.СуммаДокумента), 0) КАК СуммаДокумента
ИЗ
Справочник.Контрагенты КАК Контрагенты
ЛЕВОЕ СОЕДИНЕНИЕ Документ.ЗаказПоставщику КАК ЗаказПоставщику
ПО Контрагенты.Ссылка = ЗаказПоставщику.Контрагент
И (ЗаказПоставщику.Дата МЕЖДУ &НачалоПериода И &КонецПериода)
СГРУППИРОВАТЬ ПО
Контрагенты.Ссылка
В этом случае СУБД накладывает фильтр по датам непосредственно на правую таблицу Документ.ЗаказПоставщику до или в процессе выполнения соединения. Если у контрагента не было заказов в указанном периоде, СУБД все равно вернет этого контрагента, а в поле суммы благодаря функции ЕСТЬNULL мы получим аккуратный ноль. Левое соединение осталось левым.
Вариант 2 (Неправильный): Условие перенесено в секцию ГДЕ
ВЫБРАТЬ
Контрагенты.Ссылка КАК Контрагент,
СУММА(ЗаказПоставщику.СуммаДокумента) КАК СуммаДокумента
ИЗ
Справочник.Контрагенты КАК Контрагенты
ЛЕВОЕ СОЕДИНЕНИЕ Документ.ЗаказПоставщику КАК ЗаказПоставщику
ПО Контрагенты.Ссылка = ЗаказПоставщику.Контрагент
ГДЕ
ЗаказПоставщику.Дата МЕЖДУ &НачалоПериода И &КонецПериода
СГРУППИРОВАТЬ ПО
Контрагенты.Ссылка
Здесь СУБД сначала свяжет всех контрагентов со всеми их заказами за всю историю. У тех контрагентов, у которых вообще никогда не было заказов, поле даты примет значение NULL. Затем сработает секция ГДЕ, которая попытается вычислить, попадает ли значение NULL в период между датами. Результатом этого сравнения будет ложь, и контрагенты без заказов (а также контрагенты, у которых заказы были, но не в этом периоде) будут полностью исключены из результата. Запрос вернул только тех контрагентов, у которых есть заказы в этом периоде, то есть отработал как внутреннее соединение.
Золотое правило разработки в 1С: любые условия и ограничения, накладываемые на правую таблицу в левом соединении, должны находиться исключительно в секции ПО (условие связи). В секции ГДЕ допускается только проверка на отсутствие записи вида ПраваяТаблица.ПолеСвязи ЕСТЬ NULL (так называемое антисоединение для поиска «сиротских» записей).
Второй важный аспект левого соединения затронут в Сообщении 7: количество строк в итоговой таблице может значительно превышать количество строк в исходной левой таблице. Левое соединение гарантирует, что ни одна строка слева не пропадет, но оно не гарантирует, что они не размножатся.
Если одному значению ключа связи в левой таблице соответствует несколько записей в правой таблице, СУБД выполнит декартово умножение для этой группы. Строка левой таблицы продублируется ровно столько раз, сколько соответствий будет найдено справа. Например, если у нас в левой таблице есть строка с сотрудником «Иванов», а в правой таблице регистра сведений по нему есть три разные записи об окладе, удовлетворяющие условию по дате, в итоговой таблице мы получим три строки с «Ивановым».
Чтобы левое соединение не размножало строки, отношение между левой и правой таблицей по ключу связи должно быть строго «один ко многим» (где на одну запись слева приходится максимум одна запись справа), а еще лучше — «один к одному» или «один к нулю». Достичь этого можно следующими способами:
МАКСИМУМ или МИНИМУМ) во временную таблицу.Разберем по шагам, как исправить логику в запросе автора, чтобы левое соединение отработало корректно, оклады сопоставились правильно и не произошло нежелательного умножения строк. Мы перенесем условия фильтрации из ГДЕ в параметры связи и обеспечим корректную работу с временными таблицами.
Шаг 1. Подготовка исходных данных документа
Сначала мы выбираем данные документа во временную таблицу. Здесь все написано верно, добавим только правильное индексирование:
ВЫБРАТЬ
НачислениеЗарплаты.НомерСтроки КАК НомерСтроки,
НачислениеЗарплаты.Сотрудник КАК Сотрудник,
НачислениеЗарплаты.ВидРасчета КАК ВидРасчета,
НачислениеЗарплаты.ДатаНачала КАК ДатаНачала,
КОНЕЦПЕРИОДА(НачислениеЗарплаты.ДатаОкончания, ДЕНЬ) КАК ДатаОкончания,
НачислениеЗарплаты.ГрафикРаботы КАК ГрафикРаботы
ПОМЕСТИТЬ ВТ_ДанныеДокумента
ИЗ
Документ.НачислениеЗарплаты.ОсновныеНачисления КАК НачислениеЗарплаты
ГДЕ
НачислениеЗарплаты.Ссылка = &Ссылка
И НачислениеЗарплаты.ВидРасчета = ЗНАЧЕНИЕ(ПланВидовРасчета.ОсновныеНачисления.Оклад)
ИНДЕКСИРОВАТЬ ПО
Сотрудник,
НомерСтроки;
Шаг 2. Получение актуальных дат среза окладов
Теперь нам нужно определить наиболее актуальную дату изменения оклада для каждого сотрудника на момент начала начисления (учитывая, что в регистре сведения хранятся в разрезе периодов). Мы делаем левое соединение, но избавляемся от ошибочного фильтра ГДЕ НЕ ... ЕСТЬ NULL. Если у сотрудника вообще нет оклада в регистре, мы все равно должны сохранить его строку!
ВЫБРАТЬ
ВТ_ДанныеДокумента.НомерСтроки КАК НомерСтроки,
ВТ_ДанныеДокумента.Сотрудник КАК Сотрудник,
МАКСИМУМ(СведенияОСотрудниках.Период) КАК Период
ПОМЕСТИТЬ ВТ_МаксимальныеДатыОкладов
ИЗ
ВТ_ДанныеДокумента КАК ВТ_ДанныеДокумента
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.СведенияОСотрудниках КАК СведенияОСотрудниках
ПО ВТ_ДанныеДокумента.Сотрудник = СведенияОСотрудниках.Сотрудник
И ВТ_ДанныеДокумента.ДатаНачала >= СведенияОСотрудниках.Период
СГРУППИРОВАТЬ ПО
ВТ_ДанныеДокумента.НомерСтроки,
ВТ_ДанныеДокумента.Сотрудник
ИНДЕКСИРОВАТЬ ПО
Сотрудник,
Период;
Шаг 3. Получение размеров окладов
Теперь соединяем полученные максимальные даты с физической таблицей регистра сведений, чтобы вытащить сам размер оклада. Обратите внимание: мы используем ЛЕВОЕ СОЕДИНЕНИЕ, чтобы сотрудники без окладов не пропали из итоговой выборки:
ВЫБРАТЬ
ВТ_МаксимальныеДатыОкладов.НомерСтроки КАК НомерСтроки,
ВТ_МаксимальныеДатыОкладов.Сотрудник КАК Сотрудник,
ЕСТЬNULL(СведенияОСотрудниках.Оклад, 0) КАК Размер
ПОМЕСТИТЬ ВТ_РассчитанныйОклад
ИЗ
ВТ_МаксимальныеДатыОкладов КАК ВТ_МаксимальныеДатыОкладов
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.СведенияОСотрудниках КАК СведенияОСотрудниках
ПО ВТ_МаксимальныеДатыОкладов.Сотрудник = СведенияОСотрудниках.Сотрудник
И ВТ_МаксимальныеДатыОкладов.Период = СведенияОСотрудниках.Период
ИНДЕКСИРОВАТЬ ПО
НомерСтроки;
Шаг 4. Финальное объединение данных
На последнем шаге мы соединяем исходную таблицу документа с полученными окладами по уникальному ключу — номеру строки. Использование ЕСТЬNULL гарантирует, что если оклад не был найден, в итоговом результате отобразится ноль, но сама строка сотрудника никуда не исчезнет:
ВЫБРАТЬ
ВТ_ДанныеДокумента.НомерСтроки,
ВТ_ДанныеДокумента.Сотрудник,
ВТ_ДанныеДокумента.ВидРасчета,
ВТ_ДанныеДокумента.ДатаНачала,
ВТ_ДанныеДокумента.ДатаОкончания,
ВТ_ДанныеДокумента.ГрафикРаботы,
ЕСТЬNULL(ВТ_РассчитанныйОклад.Размер, 0) КАК Размер
ИЗ
ВТ_ДанныеДокумента КАК ВТ_ДанныеДокумента
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_РассчитанныйОклад КАК ВТ_РассчитанныйОклад
ПО ВТ_ДанныеДокумента.НомерСтроки = ВТ_РассчитанныйОклад.НомерСтроки
Левое соединение — одна из самых «тяжелых» операций для СУБД. Неоптимально написанные запросы с левыми соединениями являются главной причиной зависания транзакций и низкой скорости работы отчетов. Разберем ключевые правила оптимизации, которые необходимо соблюдать при разработке в 1С:
ЛЕВОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ ... ИЗ ...) ПО ... категорически запрещены стандартами разработки 1С. Оптимизатор СУБД не может адекватно оценить статистику по вложенному запросу и строит неэффективный план выполнения. Всегда выносите вложенные выборки в предварительные Временные таблицы.ОстаткиИОбороты, СрезПоследних и т.д.). Сначала получите нужный срез или остатки во временную таблицу, наложив все возможные фильтры в параметрах виртуальной таблицы, а затем производите соединение.ПО (например, Сотрудник, Номенклатура, Договор, НомерСтроки), должны быть проиндексированы. Для этого используйте конструкцию ИНДЕКСИРОВАТЬ ПО ... при помещении данных во временную таблицу. Это позволяет СУБД использовать быстрый поиск по индексу (Index Seek) вместо медленного полного сканирования таблицы (Table Scan).Понимание логики работы СУБД на стыке секций ПО и ГДЕ, а также соблюдение стандартов по работе с временными таблицами и индексами позволит вам писать не только логически корректные, но и максимально быстродействующие запросы в системе 1С.