При разработке отчетов и запросов в системе 1С программисты часто сталкиваются с ситуацией, когда использование ЛЕВОЕ СОЕДИНЕНИЕ (LEFT JOIN) не дает ожидаемого результата. Казалось бы, левое соединение должно возвращать все строки из основной (левой) таблицы, даже если для них нет соответствий в присоединяемой (правой) таблице. Однако на практике данные из левой таблицы внезапно исчезают. Разберем детально, почему это происходит и как правильно составить запрос, чтобы данные не терялись.
Рассмотрим ситуацию, описанную в теме. Имеется запрос к бухгалтерским оборотам, к которым присоединяются данные из документа СчетНаОплатуПокупателю. Основная цель — вывести все оплаты, а если к оплате найден счет, то показать и данные счета. Проблема заключается в секции ГДЕ исходного запроса:
ГДЕ
СчетНаОплатуПокупателю.Дата МЕЖДУ &НачалоПериодаСчетов И &КонецПериода
И НЕ СчетНаОплатуПокупателю.ПометкаУдаления
Выясним причину, почему это "убивает" левое соединение. Механика работы СУБД (SQL) подразумевает строго определенную последовательность выполнения операций:
ПО. Если соответствия нет, поля правой таблицы заполняются значением NULL.NULL) применяется фильтр.Когда мы пишем СчетНаОплатуПокупателю.Дата МЕЖДУ ..., система проверяет дату для каждой строки. Если для оборота оплаты не найден счет, в поле Дата будет стоять NULL. Любое сравнение NULL с конкретным значением (датой) возвращает результат "Ложь". В итоге все строки, где счет не найден, отсеиваются фильтром, и запрос возвращает только те записи, где соответствие есть. Таким образом, наше левое соединение фактически превратилось во внутреннее.
Самый быстрый и очевидный способ исправить ситуацию — перенести условия фильтрации правой таблицы непосредственно в условие связи. В этом случае фильтр будет применен в момент соединения, а не после него. Те записи, которые не соответствуют условию (например, помечены на удаление или не попадают в период), просто не будут присоединены, но строки левой таблицы сохранятся.
Проанализируем правильный вариант написания секции ИЗ:
ИЗ
РегистрБухгалтерии.Хозрасчетный.ОборотыДтКт(&НачалоПериода, &КонецПериода, , СчетДт.Код В ("50.01", "51", "55.03"), , СчетКт.Код В ("62.01", "62.02"), , ) КАК ХозрасчетныйОборотыДтКт
ЛЕВОЕ СОЕДИНЕНИЕ Документ.СчетНаОплатуПокупателю КАК СчетНаОплатуПокупателю
ПО ХозрасчетныйОборотыДтКт.СубконтоКт1 = СчетНаОплатуПокупателю.Контрагент
И ХозрасчетныйОборотыДтКт.СубконтоКт2 = СчетНаОплатуПокупателю.ДоговорКонтрагента
И (СчетНаОплатуПокупателю.Дата МЕЖДУ &НачалоПериодаСчетов И &КонецПериода)
И (НЕ СчетНаОплатуПокупателю.ПометкаУдаления)
При таком подходе секция ГДЕ в конце запроса должна быть либо пустой, либо содержать условия только на поля левой таблицы (в нашем случае ХозрасчетныйОборотыДтКт).
Хотя первый метод рабочий, профессиональным стандартом разработки в 1С считается предварительная подготовка данных во временных таблицах (ВТ). Рассмотрим подробнее, почему это лучше:
Контрагент, Договор), что значительно ускорит поиск соответствий.Разберем по шагам реализацию через ВТ:
ВТ_СчетаНаОплату.ВТ_СчетаНаОплату по полям Контрагент и ДоговорКонтрагента.Посмотрим на пример кода:
// Шаг 1: Подготовка правой таблицы
ВЫБРАТЬ
Счет.Контрагент КАК Контрагент,
Счет.ДоговорКонтрагента КАК Договор,
Счет.КурсВзаиморасчетов КАК КурсВзаиморасчетов,
Счет.СтавкаНДС КАК СтавкаНДС,
СУММА(Счет.СуммаДокумента) КАК СуммаДокумента
ПОМЕСТИТЬ ВТ_Счета
ИЗ
Документ.СчетНаОплатуПокупателю КАК Счет
ГДЕ
Счет.Дата МЕЖДУ &НачалоПериодаСчетов И &КонецПериода
И НЕ Счет.ПометкаУдаления
СГРУППИРОВАТЬ ПО
Счет.Контрагент,
Счет.ДоговорКонтрагента,
Счет.КурсВзаиморасчетов,
Счет.СтавкаНДС
ИНДЕКСИРОВАТЬ ПО
Контрагент,
Договор
;
// Шаг 2: Финальное соединение
ВЫБРАТЬ
Обороты.СубконтоКт1 КАК Контрагент,
Обороты.СубконтоКт2 КАК Договор,
СУММА(Обороты.СуммаОборот) КАК СуммаОплаты,
ЕСТЬNULL(ВТ_Счета.СуммаДокумента, 0) КАК СуммаПоСчету
ИЗ
РегистрБухгалтерии.Хозрасчетный.ОборотыДтКт(&НачалоПериода, &КонецПериода, , , , , , ) КАК Обороты
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Счета КАК ВТ_Счета
ПО Обороты.СубконтоКт1 = ВТ_Счета.Контрагент
И Обороты.СубконтоКт2 = ВТ_Счета.Договор
СГРУППИРОВАТЬ ПО
Обороты.СубконтоКт1,
Обороты.СубконтоКт2
В процессе работы с левым соединением мы можем столкнуться с еще одной коварной проблемой — дублированием сумм. Проанализируем ситуацию: если на одного контрагента и один договор в периоде выписано три разных счета, то строка с оплатой из бухгалтерии при соединении превратится в три строки. В результате сумма оплаты при простом суммировании (СУММА) увеличится в три раза.
Для решения этой проблемы мы применили группировку в первом запросе пакета (при создании ВТ). Важно сгруппировать данные правой таблицы таким образом, чтобы на одну комбинацию "Контрагент + Договор" приходилась только одна уникальная строка с уже просуммированными показателями счетов. Только в этом случае левое соединение не приведет к искажению финансовых результатов отчета.
В исходном запросе использовалась таблица РегистрБухгалтерии.Хозрасчетный.ОборотыДтКт. При работе с любыми виртуальными таблицами (Обороты, Остатки) крайне важно соблюдать следующие правила:
ГДЕ. Это позволяет системе 1С передать фильтры на уровень SQL еще до формирования временных итогов, что ускоряет запрос в разы.ЕСТЬNULL(Поле, 0). Это предотвратит ошибки при дальнейших математических операциях в коде или в компоновке данных (СКД).Подведем итог: чтобы данные не исчезали при левом соединении, никогда не ставьте жесткие условия на правую таблицу в секции ГДЕ. Используйте перенос условий в ПО или, что еще лучше, подготавливайте данные во временных таблицах с предварительной группировкой и индексацией. Это гарантирует точность данных и высокую скорость работы вашего отчета.