Соединения в запросах 1С: левое, внутреннее, полное и ОБЪЕДИНИТЬ ВСЕ

Задача: собрать данные из нескольких таблиц одним запросом

Редкий запрос в 1С обходится одной таблицей: номенклатуру нужно дополнить ценой, контрагента — суммой продаж, документ — данными из регистра. Для этого язык запросов предлагает соединения — ЛЕВОЕ, ПРАВОЕ, ВНУТРЕННЕЕ и ПОЛНОЕ — и объединения ОБЪЕДИНИТЬ / ОБЪЕДИНИТЬ ВСЕ. Они решают разные задачи: соединение добавляет колонки, объединение добавляет строки. Ниже — как устроены все виды соединений, как соединять виртуальные таблицы и временные таблицы и как не получить размножение строк. Общие основы запросов — в статье про запросы в 1С.

Синтаксис соединения и условие ПО

Соединение записывается после основной таблицы в разделе ИЗ. Условие, по которому строки сопоставляются, указывается после слова ПО. Слово «ВНЕШНЕЕ» в левом, правом и полном соединении необязательно.

ВЫБРАТЬ
    Номенклатура.Ссылка КАК Номенклатура,
    ЕСТЬNULL(Цены.Цена, 0) КАК Цена
ИЗ
    Справочник.Номенклатура КАК Номенклатура
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&Период, ТипЦен = &ТипЦен) КАК Цены
    ПО Номенклатура.Ссылка = Цены.Номенклатура
ГДЕ
    НЕ Номенклатура.ЭтоГруппа

Запрос выводит все товары, а цену — если она есть в регистре на заданную дату. У товаров без цены поле Цена будет NULL, и функция ЕСТЬNULL заменяет его нулём. Это стандартное лечение пустых значений после любого соединения, кроме внутреннего.

Виды соединений и схема результата

Разницу проще всего увидеть на маленьком примере. Пусть левая таблица Товары содержит Яблоко, Грушу и Сливу, а правая Цены — Яблоко (100), Сливу (80) и Дыню (150). Соединяем по наименованию.

Соединение	Строки результата	Что получаем
ВНУТРЕННЕЕ	Яблоко — 100; Слива — 80	Только пары, найденные в обеих таблицах
ЛЕВОЕ	Яблоко — 100; Груша — NULL; Слива — 80	Все строки левой таблицы, из правой — что нашлось
ПРАВОЕ	Яблоко — 100; Слива — 80; (нет товара) — 150 для Дыни	Все строки правой таблицы, из левой — что нашлось
ПОЛНОЕ	Яблоко — 100; Груша — NULL; Слива — 80; Дыня — 150 без товара	Все строки обеих таблиц, ненайденные поля — NULL

В повседневной работе чаще всего встречаются внутреннее и левое соединения. Правое соединение — это левое с переставленными таблицами, поэтому его проще не использовать: читать запрос с левыми соединениями привычнее. Полное нужно для сверок двух источников, где с любой стороны могут быть «лишние» строки.

Где ставить условие: ПО или ГДЕ

Самая частая ошибка с левым соединением — условие по правой таблице в разделе ГДЕ. Для строк без пары поля правой таблицы равны NULL, условие к ним не выполняется, и такие строки отбрасываются. Левое соединение превращается во внутреннее, хотя в запросе написано иначе.

// Неверно: контрагенты без проведённых документов пропадут
ИЗ
    Справочник.Контрагенты КАК Контрагенты
    ЛЕВОЕ СОЕДИНЕНИЕ Документ.РеализацияТоваровУслуг КАК Реализации
    ПО Реализации.Контрагент = Контрагенты.Ссылка
ГДЕ
    Реализации.Проведен

// Верно: условие вошло в соединение
ИЗ
    Справочник.Контрагенты КАК Контрагенты
    ЛЕВОЕ СОЕДИНЕНИЕ Документ.РеализацияТоваровУслуг КАК Реализации
    ПО Реализации.Контрагент = Контрагенты.Ссылка
        И Реализации.Проведен

Если нужно именно отфильтровать по правой таблице (и пустые строки действительно не нужны) — напишите внутреннее соединение. Условия по левой таблице, напротив, безопасно держать в ГДЕ.

Соединение с виртуальными таблицами регистров

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

// Хорошо: отбор внутри виртуальной таблицы
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки(
        &Дата,
        Склад = &Склад И Номенклатура В (&Товары)) КАК Остатки
ПО Товары.Номенклатура = Остатки.Номенклатура

// Хуже: остатки считаются по всему регистру, потом отбрасываются
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки(&Дата, ) КАК Остатки
ПО Товары.Номенклатура = Остатки.Номенклатура
ГДЕ
    Остатки.Склад = &Склад

Если список товаров уже лежит во временной таблице, его можно подставить подзапросом прямо в параметры виртуальной таблицы:

ВЫБРАТЬ
    Товары.Номенклатура КАК Номенклатура,
    ЕСТЬNULL(Остатки.КоличествоОстаток, 0) КАК Остаток
ИЗ
    ВТ_Товары КАК Товары
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки(
            &Дата,
            Номенклатура В (ВЫБРАТЬ Номенклатура ИЗ ВТ_Товары)) КАК Остатки
    ПО Товары.Номенклатура = Остатки.Номенклатура

Соединение с временной таблицей и индексирование

Промежуточные результаты удобно складывать во временные таблицы и соединять их между собой — запрос становится читаемым, а сложные подзапросы не повторяются. Если временная таблица большая и по ней идут соединения, добавьте в конце описания ИНДЕКСИРОВАТЬ ПО с полями, по которым соединяете. Подробнее о пакетных запросах и временных таблицах — в общей статье про запросы.

ВЫБРАТЬ
    Реализация.Контрагент КАК Контрагент,
    СУММА(Реализация.СуммаДокумента) КАК Сумма
ПОМЕСТИТЬ ВТ_Продажи
ИЗ
    Документ.РеализацияТоваровУслуг КАК Реализация
ГДЕ
    Реализация.Проведен
    И Реализация.Дата МЕЖДУ &Начало И &Конец
СГРУППИРОВАТЬ ПО
    Реализация.Контрагент
ИНДЕКСИРОВАТЬ ПО
    Контрагент
;

ВЫБРАТЬ
    Поступление.Контрагент КАК Контрагент,
    СУММА(Поступление.СуммаДокумента) КАК Сумма
ПОМЕСТИТЬ ВТ_Оплаты
ИЗ
    Документ.ПоступлениеНаРасчетныйСчет КАК Поступление
ГДЕ
    Поступление.Проведен
    И Поступление.Дата МЕЖДУ &Начало И &Конец
СГРУППИРОВАТЬ ПО
    Поступление.Контрагент
ИНДЕКСИРОВАТЬ ПО
    Контрагент
;

ВЫБРАТЬ
    ЕСТЬNULL(Продажи.Контрагент, Оплаты.Контрагент) КАК Контрагент,
    ЕСТЬNULL(Продажи.Сумма, 0) КАК Продано,
    ЕСТЬNULL(Оплаты.Сумма, 0) КАК Оплачено
ИЗ
    ВТ_Продажи КАК Продажи
    ПОЛНОЕ СОЕДИНЕНИЕ ВТ_Оплаты КАК Оплаты
    ПО Продажи.Контрагент = Оплаты.Контрагент

Это сверка продаж и оплат по контрагентам. Полное соединение показывает и тех, кому отгрузили, но оплаты не было, и тех, кто заплатил без продаж в периоде. Ключ в итоговой выборке берётся через ЕСТЬNULL от обеих сторон: у строки без пары одна из них пустая. (Названия документов типичны для бухгалтерской конфигурации; у вас могут быть другие.)

Размножение строк и как его избежать

Соединение «один ко многим» дублирует строки левой таблицы: если у контрагента три оплаты, его реализация встретится в результате три раза. Суммы по левой таблице после этого окажутся завышены.

// Неверно: сумма реализаций умножается на число оплат контрагента
ВЫБРАТЬ
    Реализация.Контрагент КАК Контрагент,
    СУММА(Реализация.СуммаДокумента) КАК Продано,
    СУММА(Оплата.СуммаДокумента) КАК Оплачено
ИЗ
    Документ.РеализацияТоваровУслуг КАК Реализация
    ЛЕВОЕ СОЕДИНЕНИЕ Документ.ПоступлениеНаРасчетныйСчет КАК Оплата
    ПО Оплата.Контрагент = Реализация.Контрагент
СГРУППИРОВАТЬ ПО
    Реализация.Контрагент

Правильный подход — сначала свернуть каждую сторону до уровня ключа соединения (как выше, во временные таблицы ВТ_Продажи и ВТ_Оплаты), и только потом соединять. Тогда на каждый ключ приходится по одной строке с каждой стороны. Тот же принцип работает для табличных частей и регистров: агрегируйте до соединения, а не после.

ОБЪЕДИНИТЬ и ОБЪЕДИНИТЬ ВСЕ

Объединение склеивает результаты нескольких запросов по вертикали. Количество и порядок полей в запросах должны совпадать, типы — быть совместимыми, а имена колонок берутся из первого запроса. Разница между вариантами:

• ОБЪЕДИНИТЬ ВСЕ просто дописывает строки одного запроса за другим — быстро, дубликаты сохраняются;
• ОБЪЕДИНИТЬ дополнительно убирает полностью совпадающие строки, как РАЗЛИЧНЫЕ по всему результату, и требует от СУБД лишней работы.

// Уникальный список контрагентов, у которых есть продажи или оплаты
ВЫБРАТЬ
    Реализация.Контрагент КАК Контрагент
ИЗ
    Документ.РеализацияТоваровУслуг КАК Реализация

ОБЪЕДИНИТЬ

ВЫБРАТЬ
    Поступление.Контрагент
ИЗ
    Документ.ПоступлениеНаРасчетныйСчет КАК Поступление

Если нужен общий список движений с указанием источника, дубликаты не убирают, и подойдёт ОБЪЕДИНИТЬ ВСЕ:

ВЫБРАТЬ
    Реализация.Контрагент КАК Контрагент,
    "Продажа" КАК Источник,
    Реализация.СуммаДокумента КАК Сумма
ИЗ
    Документ.РеализацияТоваровУслуг КАК Реализация

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    Поступление.Контрагент,
    "Оплата",
    Поступление.СуммаДокумента
ИЗ
    Документ.ПоступлениеНаРасчетныйСчет КАК Поступление

УПОРЯДОЧИТЬ ПО
    Контрагент

Раздел УПОРЯДОЧИТЬ ПО пишется один раз — в самом конце всей цепочки и ссылается на имена колонок первого запроса. Если нужно только склеить строки, используйте ОБЪЕДИНИТЬ ВСЕ; обычный ОБЪЕДИНИТЬ оправдан, когда уникальность нужна по условию задачи.

Производительность соединений

• Соединяйте по индексируемым полям — ссылкам и измерениям регистров. Соединение по выражению, например ПОДСТРОКА(...), не позволяет использовать индекс.
• Избегайте ИЛИ в условии ПО: такие соединения очень медленны. Разделите запрос на два и склейте их через ОБЪЕДИНИТЬ ВСЕ.
• Не соединяйте по полям составного типа без нужды — платформа вынуждена сравнивать и тип, и значение.
• Уменьшайте обе стороны до соединения: условия в параметрах виртуальных таблиц и в самих запросах, отбор во временные таблицы.
• Вместо вложенных запросов в соединении используйте временные таблицы с индексом.
• Следите за числом строк в результате: резкий рост по сравнению с левой таблицей — признак размножения.

Соединения и объединения: шпаргалка

Задача	Конструкция
Только совпавшие строки	ВНУТРЕННЕЕ СОЕДИНЕНИЕ
Все строки основной таблицы плюс данные, если есть	ЛЕВОЕ СОЕДИНЕНИЕ + ЕСТЬNULL
Сверка двух источников	ПОЛНОЕ СОЕДИНЕНИЕ
Добавить строки второго запроса	ОБЪЕДИНИТЬ ВСЕ
Склеить и убрать дубликаты	ОБЪЕДИНИТЬ
Ускорить соединение больших промежуточных данных	ПОМЕСТИТЬ + ИНДЕКСИРОВАТЬ ПО
Отбор по виртуальной таблице	Условие в параметрах таблицы, не в ГДЕ