Соединения таблиц

Учимся собирать данные из нескольких таблиц в один результат — и понимать, почему после соединения строк вдруг стало больше, чем было.

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

В реальной базе данные разложены по разным таблицам. Документ «Продажа» лежит отдельно, а название и артикул номенклатуры — в справочнике «Номенклатура». В документе хранится не сам текст «Кофе молотый 250 г», а ссылка на элемент справочника. Когда вам нужен отчёт, где рядом стоят и количество из документа, и артикул из справочника, эти две таблицы приходится соединять.

Зачем вообще соединять таблицы

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

Условие соединения почти всегда — равенство ссылки и элемента. В левой таблице лежит поле-ссылка (например, Номенклатура), а справа — сам справочник, чьё поле Ссылка с этой ссылкой сравнивается.

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

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

Три вида соединения

ЛЕВОЕ СОЕДИНЕНИЕ ПО

Берём все строки левой (первой) таблицы, а из правой подставляем совпавшие. Если совпадения нет — поля правой таблицы будут NULL. Левая таблица тут главная: сколько в ней строк, столько (как минимум) будет и в результате.

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

Здесь мы хотим увидеть каждый заказ — даже те, по которым отгружать уже нечего. По таким заказам в регистре остатков строки нет, поэтому КоличествоОстаток вернётся как NULL, а ЕСТЬNULL(..., 0) превратит его в аккуратный ноль. Функцию ЕСТЬNULL после левого соединения используют почти всегда — про неё подробно ниже, в «частых ошибках».

ВНУТРЕННЕЕ СОЕДИНЕНИЕ ПО

Оставляем только те строки, для которых нашлась пара в обеих таблицах. Нет совпадения — строка вылетает из результата целиком. Это как фильтр: «покажи только заказы, по которым есть что отгружать».

ВЫБРАТЬ
    Заказы.Ссылка КАК Заказ,
    ОтгрузкаОстатки.КоличествоОстаток КАК ОсталосьОтгрузить
ИЗ
    Документ.ЗаказПокупателя КАК Заказы
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыКОтгрузке.Остатки КАК ОтгрузкаОстатки
    ПО Заказы.Ссылка = ОтгрузкаОстатки.Заказ

Тот же запрос с внутренним соединением вернёт только заказы с ненулевым остатком к отгрузке. Полностью отгруженные и пустые заказы отсеются сами собой.

ПОЛНОЕ СОЕДИНЕНИЕ ПО

Берём все строки и слева, и справа. Где пары нет — с недостающей стороны будут NULL. Применяют редко: например, чтобы сверить две ведомости и увидеть строки, которые есть только в одной из них.

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

В результат попадёт и то, что было в плане, но не купили, и то, что купили сверх плана. Первое поле собрано из обеих сторон через ЕСТЬNULL, потому что в «плановых» строках ссылка придёт из План, а в «сверхплановых» — из Факт.

Почему левое соединение — самое частое

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

Соединений в одном запросе может быть несколько — они выполняются слева направо, наращивая набор полей:

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

Кстати, вместо соединения со справочником только ради наименования часто пишут короче — через точку: Продажи.Номенклатура.Наименование. Платформа сама подставит нужное соединение. Но как только справочных полей становится много или нужна фильтрация по ним, явное ЛЕВОЕ СОЕДИНЕНИЕ читается яснее и работает предсказуемее.

Как это работает

Запрос 1С не выполняется в самой платформе — она переводит его в SQL и отдаёт настоящей СУБД (PostgreSQL, MS SQL). Соединение превращается в JOIN, условие после ПО — в условие соединения. СУБД сопоставляет строки: для каждой строки левой таблицы ищет подходящие строки правой по условию равенства.

Ключевой момент — сопоставление не обязано быть один-к-одному. Если одной строке слева соответствует три строки справа, в результат попадут три строки — левая продублируется по три раза с разными правыми значениями. Это не ошибка платформы, а математика соединения. И именно здесь новички получают неожиданно «раздутый» результат.

Частые ошибки

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

Забыли про NULL после левого соединения. По несопоставленным строкам поля правой таблицы равны NULL. А NULL в арифметике «заражает» результат: 100 + NULL даёт NULL, а не 100. Поэтому числовые поля из правой таблицы почти всегда оборачивают в ЕСТЬNULL(Поле, 0), а ссылочные — в ЕСТЬNULL(Поле, ЗНАЧЕНИЕ(Справочник.Номенклатура.ПустаяСсылка)).

Условие соединения превратили в фильтр. Иногда в ПО добавляют условие вроде И Цены.Цена > 0. При левом соединении это ведёт себя не так, как ожидаешь: строки слева сохранятся, но правые значения по не прошедшим условие обнулятся в NULL. Если вам нужен именно фильтр результата — выносите условие в ГДЕ. В ПО держите только условие сопоставления строк.

Перепутали, какая таблица главная. Поставили справочник слева, а документ справа — и потеряли смысл: теперь в результат лезут все элементы справочника, даже те, что ни разу не продавались. Всегда держите в голове: слева — то, что показываем целиком; справа — то, чем дополняем.

Итоги

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