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