1C-Code → Основы 1С → Соединения и NULL
Соединения и NULL
Самая частая тема на собеседовании и самый частый источник неверных отчётов. Четыре вида соединений, NULL и почему «ноль» и «ничего» — не одно и то же.
Глава 26 из 36 · чтение примерно 28 минут
Данные лежат в разных таблицах: товары отдельно, остатки отдельно, цены отдельно. Соединение — способ увидеть их вместе.
И это же место, где отчёт молча начинает врать: строки пропадают или размножаются, а сумма выглядит правдоподобно.
Четыре вида соединения
| Вид | Что оставляет |
|---|---|
| ВНУТРЕННЕЕ | только строки, у которых есть пара в обеих таблицах |
| ЛЕВОЕ | все строки левой; где пары нет — NULL справа |
| ПРАВОЕ | то же зеркально; в 1С почти не пишут — меняют таблицы местами |
| ПОЛНОЕ | все строки обеих; NULL с той стороны, где пары нет |
Как выбрать вид
Вопрос один: нужны ли строки, у которых пары нет.
«Все товары и их остаток» — нужны: товар без остатка всё равно товар. Значит ЛЕВОЕ.
«Только товары, по которым были продажи» — не нужны. Значит ВНУТРЕННЕЕ.
Ошибка почти всегда в одну сторону: пишут внутреннее там, где нужно левое, и из отчёта тихо исчезают строки. Никто не замечает, потому что оставшиеся выглядят правильно.
NULL — это не ноль и не пустая строка
NULL означает «значения нет вовсе». Он появляется там, где левое соединение не нашло пары.
И ведёт себя не как значение:
NULL + 100 даёт NULL, а не 100. Любая арифметика с NULL даёт NULL — одна строка без остатка обнуляет весь итог.
NULL = NULL не истина. Сравнивать с ним бесполезно: есть ЕСТЬ NULL.
Лечится одинаково: ЕСТЬNULL(Поле, 0) — подставить ноль там, где ничего не нашлось.
Левое соединение и ЕСТЬNULL
ВЫБРАТЬ
Т.Наименование,
ЕСТЬNULL(О.Количество, 0) КАК Остаток
ИЗ
Справочник.Номенклатура КАК Т
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ОстаткиТоваров КАК О
ПО О.Номенклатура = Т.Ссылка| Часть | Что означает |
|---|---|
ЛЕВОЕ СОЕДИНЕНИЕ | товары без остатка обязаны остаться в отчёте |
ЕСТЬNULL(О.Количество, 0) | без него у таких товаров будет NULL, а не ноль |
ПО О.Номенклатура = Т.Ссылка | условие связи — в ПО, а не в ГДЕ |
КАК Т, КАК О | псевдонимы: без них длинные имена делают запрос нечитаемым |
ГДЕ убивает левое соединение
Самая коварная ошибка темы. Написали левое соединение, а условие на присоединённую таблицу поставили в ГДЕ:
ЛЕВОЕ СОЕДИНЕНИЕ … ГДЕ О.Склад = &Склад
У товаров без остатка О.Склад равен NULL, сравнение с NULL не истина — и ГДЕ выбрасывает эти строки. Левое соединение превращается во внутреннее, и никакой ошибки при этом не возникает.
Правило: условие на присоединяемую таблицу — в ПО, условие на основную — в ГДЕ.
На чём спотыкаются ещё
Строки размножаются. Если в правой таблице на одну строку левой приходится несколько — результат вырастет, и сумма удвоится. Лечится группировкой или соединением с уже свёрнутой таблицей.
Считают СУММА по полю с NULL. Агрегат NULL пропускает, но СУММА(А + Б), где Б бывает NULL, даст меньше ожидаемого. Оборачивать надо внутри: СУММА(ЕСТЬNULL(А,0) + ЕСТЬNULL(Б,0)).
Соединяют подряд пять таблиц и удивляются скорости. Каждое соединение — работа. Часто дешевле собрать во временную таблицу и соединить уже свёрнутое.
Соединение РАЗМНОЖАЕТ строки
Это главный сюрприз соединений, и он дороже всех остальных вместе взятых. Соединение не «добавляет колонки» — оно составляет все подходящие пары.
Одной строке слева соответствуют три справа — в результате будет три строки, и в каждой левая часть повторится. Сумма, посчитанная по такому результату, окажется втрое больше.
На демо-базе это видно в числах: в строках всех документов 31 запись. Соедините их с регистром остатков по номенклатуре — станет 94. Сумма количества при этом вырастет с 14 615 до 44 278, и в отчёте это будет выглядеть как «продали втрое больше».
Отчёт, который завышает в три раза
Разбирательства с «отчёт показывает не то» почти всегда кончаются здесь. Признак узнаётся мгновенно: числа кратны настоящим — ровно вдвое, ровно втрое.
Как проверить за минуту: уберите из запроса все СУММА и посмотрите строки глазами. Если одна и та же строка документа встречается несколько раз — виновато соединение.
Как чинить: либо соединять с уже свёрнутым результатом (агрегировать во временной таблице ДО соединения), либо добавить в условие соединения недостающее измерение, чтобы пара стала единственной.
Упражнение 2. Сумма, которую не раздуло
Напишите ВсегоПродано(): общее количество по строкам всех документов РеализацияТоваров. Никаких соединений — только табличная часть. Ожидается 14615.
Решается в тренажёре: код запускается и проверяется сразу.
NULL заражает всё, к чему прикоснётся
NULL — не значение, а его отсутствие. Отсюда правила, которые кажутся странными, пока не поймёшь эту фразу.
Любая арифметика с NULL даёт NULL. 100 + NULL — это NULL, а не 100. Одна пустая строка в сумме обнуляет весь итог колонки.
Сравнение с NULL не истинно и не ложно. Поле = NULL не сработает никогда — даже когда там действительно NULL. Проверяют только через ЕСТЬ NULL.
Строка с NULL не попадает в ГДЕ. Условие ГДЕ Сумма > 100 выбросит строки, где сумма NULL, — вместе с теми, где она меньше ста. Это разные вещи, и в отчёте они выглядят одинаково: строки просто нет.
Три способа не встретиться с NULL
| Способ | Что делает | Когда |
|---|---|---|
| ЕСТЬNULL(Поле, 0) | подставляет значение вместо NULL | почти всегда — оборачивают каждое поле из присоединённой таблицы |
| ЕСТЬ NULL | проверка «значения нет» | когда сам факт отсутствия и есть ответ |
| ВЫБОР КОГДА … ЕСТЬ NULL ТОГДА | развилка по отсутствию | когда вместо числа нужен текст: «нет данных» |
ЕСТЬNULL пишут сразу, а не когда сломается
Практическое правило: каждое поле присоединённой таблицы, которое участвует в расчёте, оборачивают в ЕСТЬNULL — сразу, не дожидаясь пустого итога.
Стоит это ничего, а ловит тот самый случай, когда у половины товаров движений нет: без обёртки их суммы превратятся в NULL и утянут за собой общий итог.
Не оборачивают только то, по чему собираются проверять ЕСТЬ NULL: там отсутствие и есть смысл.
Соединение с виртуальной таблицей
Отдельная ловушка, из-за которой отчёт по остаткам показывает не то.
Виртуальная таблица остатков возвращает строки только по тем товарам, у которых остаток есть. Товара без движений в ней нет вовсе — не «ноль», а строки нет.
Поэтому отчёт «все товары и их остаток» строят от справочника ЛЕВЫМ соединением к остаткам, а не наоборот. И оборачивают количество в ЕСТЬNULL: у товаров без движений там будет NULL, а показать надо ноль.
Все товары, включая нулевые
ВЫБРАТЬ
Н.Наименование,
ЕСТЬNULL(О.КоличествоОстаток, 0) КАК Остаток
ИЗ
Справочник.Номенклатура КАК Н
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ОстаткиТоваров.Остатки КАК О
ПО О.Номенклатура = Н.Ссылка
ГДЕ
НЕ Н.ПометкаУдаления| Часть | Что означает |
|---|---|
ИЗ Справочник | слева то, что должно остаться ЦЕЛИКОМ, — иначе товары без остатка исчезнут |
ЛЕВОЕ СОЕДИНЕНИЕ | правая часть может не найтись, и это нормально |
ЕСТЬNULL(…, 0) | у товара без движений остаток NULL — показываем ноль |
ГДЕ НЕ ПометкаУдаления | условие на ЛЕВУЮ таблицу — её оно не портит; условие на правую убило бы соединение |
Упражнение 3. Товары и остаток, включая нулевые
Напишите СтрокОтчёта(): соедините справочник Номенклатура ЛЕВЫМ соединением с виртуальной таблицей остатков и верните, сколько строк получилось. В справочнике 25 позиций — а строк выйдет 26. Почему, сказано в разборе после проверки.
Решается в тренажёре: код запускается и проверяется сразу.
Упражнение 1. Все товары с остатком
Функция ТоваровБезОстатка() должна вернуть, сколько позиций номенклатуры не имеют ни одного движения в регистре ОстаткиТоваров. Соедините справочник с регистром ЛЕВЫМ соединением и посчитайте строки, где движения нет.
Решается в тренажёре: код запускается и проверяется сразу.
Короткий свод
Вид соединения выбирают по вопросу «нужны ли строки без пары». Ошибаются почти всегда в одну сторону: внутреннее вместо левого, и строки тихо исчезают. NULL — это «значения нет»: арифметика с ним даёт NULL, сравнение не работает, лечит ЕСТЬNULL. Условие на присоединяемую таблицу ставят в ПО: в ГДЕ оно превращает левое соединение во внутреннее, и молча.
Как закрепить
Читать про код и писать код — разные умения. В тренажёре к этой главе идут упражнения: вы пишете решение, оно запускается на настоящем интерпретаторе 1С и проверяется сразу, без установки платформы.
← Регистры бухгалтерии и расчёта
→ Виртуальные таблицы регистров