Часто перед разработчиком 1С встает задача: получить полный список данных из одной таблицы (например, номенклатуру из табличной части документа) и дополнить его связанной информацией из другой таблицы (например, кодами номенклатуры у конкретного контрагента). Проблема возникает, когда мы хотим видеть все строки из первой таблицы, даже если для них нет соответствия во второй, но при этом данные из второй таблицы нужны не все, а отфильтрованные по определенному условию (например, по конкретному контрагенту).
Попытка решить эту задачу "в лоб" с помощью фильтра в секции ГДЕ приводит к неожиданному результату: строки, для которых не нашлось соответствия, просто исчезают из выборки. Разберемся, почему так происходит и как правильно составить запрос.
Рассмотрим типичную ситуацию. У нас есть табличная часть документа УсловияПоставокПоДоговорамКонтрагентов и регистр сведений НоменклатураКонтрагентов. Нам нужно вывести всю номенклатуру из документа и для каждой позиции показать код номенклатуры контрагента, который указан в шапке этого документа.
Интуитивно можно написать такой запрос:
ВЫБРАТЬ
ТЧДокумента.Номенклатура,
Регистр.КодНоменклатурыКонтрагента
ИЗ
Документ.УсловияПоставокПоДоговорамКонтрагентов.НоменклатураДоговора КАК ТЧДокумента
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.НоменклатураКонтрагентов КАК Регистр
ПО ТЧДокумента.Номенклатура = Регистр.Номенклатура
ГДЕ
ТЧДокумента.Ссылка = &СсылкаНаДокумент
И Регистр.Контрагент = &КонтрагентИзДокумента
И здесь кроется главная ошибка. Давайте по шагам разберем, как платформа выполняет этот запрос:
ЛЕВОЕ СОЕДИНЕНИЕ. Система берет все строки из левой таблицы (ТЧДокумента) и для каждой ищет соответствие в правой (Регистр) по полю Номенклатура.Регистр.КодНоменклатурыКонтрагента, Регистр.Контрагент) для этой строки заполняются специальным значением NULL.ГДЕ. Система проходит по получившемуся набору данных и проверяет условие Регистр.Контрагент = &КонтрагентИзДокумента. Для тех строк, где соответствия не нашлось, это условие превратится в NULL = &КонтрагентИзДокумента, что всегда дает ложь.В итоге, все строки, для которых мы хотели сохранить NULL, отбрасываются фильтром. Наше ЛЕВОЕ СОЕДИНЕНИЕ по своему результату превратилось во ВНУТРЕННЕЕ СОЕДИНЕНИЕ.
Ключ к решению — понять фундаментальную разницу между условиями в секции ПО (ON в SQL) и ГДЕ (WHERE).
ГДЕ применяется ко всему результату ПОСЛЕ выполнения всех соединений.ПО применяется В МОМЕНТ соединения. Для левого соединения это означает, что сначала будет отфильтрована правая (присоединяемая) таблица, и только потом этот отфильтрованный набор данных будет присоединяться к левой.Таким образом, чтобы сохранить все строки из левой таблицы, нам нужно фильтровать правую таблицу до того, как она "испортит" нам результат. Для этого перенесем условие по контрагенту из секции ГДЕ в секцию ПО.
Посмотрим на исправленный и полный вариант запроса:
ВЫБРАТЬ
УсловияПоставокПоДоговорамКонтрагентовНоменклатураДоговора.Номенклатура.КодОракл КАК НоменклатураКодОракл,
УсловияПоставокПоДоговорамКонтрагентовНоменклатураДоговора.Номенклатура КАК Номенклатура,
УсловияПоставокПоДоговорамКонтрагентовНоменклатураДоговора.Номенклатура.ВестиУчетПоХарактеристикам КАК НоменклатураВестиУчетПоХарактеристикам,
УсловияПоставокПоДоговорамКонтрагентовНоменклатураДоговора.ЕдиницаИзмерения КАК ЕдиницаИзмерения,
УсловияПоставокПоДоговорамКонтрагентовНоменклатураДоговора.Номенклатура.ВидТовара КАК НоменклатураВидТовара,
НоменклатураКонтрагентов.КодНоменклатурыКонтрагента КАК КодНоменклатурыКонтрагента,
НоменклатураКонтрагентов.Контрагент КАК Контрагент
ИЗ
Документ.УсловияПоставокПоДоговорамКонтрагентов.НоменклатураДоговора КАК УсловияПоставокПоДоговорамКонтрагентовНоменклатураДоговора
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.НоменклатураКонтрагентов КАК НоменклатураКонтрагентов
ПО УсловияПоставокПоДоговорамКонтрагентовНоменклатураДоговора.Номенклатура = НоменклатураКонтрагентов.Номенклатура
И (НоменклатураКонтрагентов.Контрагент = &Контрагент) // Фильтр для правой таблицы здесь!
ГДЕ
УсловияПоставокПоДоговорамКонтрагентовНоменклатураДоговора.Ссылка = &Ссылка // Фильтр для левой таблицы остается здесь
Проанализируем, что теперь происходит:
...НоменклатураДоговора как левую (основную) таблицу.ГДЕ мы отбираем строки, относящиеся только к нашему документу (Ссылка = &Ссылка). Это правильно, так как это фильтр для основной таблицы.ЛЕВОЕ СОЕДИНЕНИЕ. Платформа берет каждую строку из отфильтрованной табличной части документа и пытается найти для нее пару в регистре НоменклатураКонтрагентов, но не во всем, а только в том наборе, который удовлетворяет двум условиям сразу:
Номенклатура совпадает.Контрагент равен значению параметра &Контрагент.В результате, если для номенклатуры из документа найдется запись в регистре для нужного контрагента, она присоединится. Если же записи для этого контрагента нет (хотя могут быть для других), или ее нет вообще, то поля из регистра останутся NULL, но сама строка из документа никуда не исчезнет. Это именно то поведение, которое нам было нужно.
В рассмотренном выше запросе мы передаем контрагента отдельным параметром &Контрагент. Однако, если контрагент указан в шапке документа, мы можем получить его прямо в запросе и сделать соединение еще более лаконичным. Для этого нужно обратиться к реквизиту документа через ссылку из табличной части.
Условие соединения будет выглядеть так:
ПО УсловияПоставокПоДоговорамКонтрагентовНоменклатураДоговора.Номенклатура = НоменклатураКонтрагентов.Номенклатура
И НоменклатураКонтрагентов.Контрагент = УсловияПоставокПоДоговорамКонтрагентовНоменклатураДоговора.Ссылка.Контрагент
В этом случае параметр &Контрагент становится не нужен, запрос становится более самодостаточным.
Запомните простое правило: при использовании ЛЕВОГО СОЕДИНЕНИЯ:
ГДЕ.ПО через оператор И.Соблюдение этого правила поможет избежать распространенных ошибок и получать корректные данные в сложных запросах.