Что проверяет оператор is null

Работа с NULL-значениями

NULL-значение

Достаточно часто встречаются такие случаи, когда в таблице имеются записи с не заданными значениями какого-либо из полей, потому что значение поля неизвестно или его просто нет. В таких случаях SQL позволяет указать в поле NULL-значение. Строго говоря, NULL-значение вовсе не представлено в поле. Когда значение поля есть NULL — это значит, что программа базы данных специальным образом помечает поле, как не содержащее какого-либо значения для данной строки (записи). Дело обстоит не так в случае простого приписывания полю значения «нуль» или «пробел», которые база данных трактует как любое другое значение. Поскольку NULL не является значением как таковым, он не имеет типа данных. NULL может размещаться в поле любого типа. Тем не менее, NULL, как NULL-значение, часто используется в SQL.
Предположим, появился покупатель, которому еще не назначен продавец. Чтобы констатировать этот факт, нужно ввести значение NULL в поле snum, а реальное значение включить туда позже, когда данному покупателю будет назначен продавец.

IS NULL

Поскольку NULL фиксирует пропущенные значения, результат любого сравнения при наличии NULL-значений неизвестен. Когда NULL-значение сравнивается с любым значением, даже с NULL-значением, результат просто неизвестен. Булево значение «неизвестно» ведет себя также, как «ложь» — строка, на которой предикат принимает значение «неизвестно», не включается в результат запроса – при одном важном исключении: NOT от лжи есть истина (NOT (false)=true), тогда как NOT от неизвестного значения есть также неизвестное значение. Следовательно, такое выражение как «city = NULL» или «city IN (NULL)» является неизвестным независимо от значения city.
Часто необходимо различать false и unknown – строки, содержащие значения столбца, не удовлетворяющие предикату, и строки, которые содержат NULL. Для этой цели SQL располагает специальным оператором IS, который используется с ключевым словом NULL для локализации NULL-значения.

SQL IS NULL. Пример.
Вывести все поля из талицы Customers, значения поля city которых равны NULL:

SELECT * FROM Customers WHERE city IS NULL

В данном случае выходных данных не будет, поскольку в поле city нет NULL-значений.

IS NOT NULL

Условие IS NOT NULL используется в запросах для выборки записей со значениями не равных значению NULL
SQL IS NOT NULL. Пример.
Вывести все поля из талицы Customers, значения поля city которых НЕ равны NULL:

SELECT * FROM Customers WHERE city IS NOT NULL

Источник

Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

Базы данных

SQL условие IS NULL

В этом учебном материале вы узнаете, как использовать SQL условие IS NULL с синтаксисом и примерами.

Описание

Условие IS NULL используется в SQL для проверки значения NULL. Оно возвращает TRUE, если найдено значение NULL, в противном случае оно возвращает FALSE. Его можно использовать в операторе SELECT, INSERT, UPDATE или DELETE.

Синтаксис

Синтаксис для условия IS NULL в SQL:

Параметры или аргументы

При тестировании на NULL значение, IS NULL является рекомендуемым оператором сравнения для использования в SQL. Давайте начнем с примера, который показывает, как использовать условие IS NULL в запросе SELECT.
В этом примере у нас есть таблица customers со следующими данными:

Введите следующий SQL оператор:

Будет выбрана 1 запись. Вот результаты, которые вы получите:

Далее давайте рассмотрим пример использования условия IS NULL в запросе UPDATE.

В этом примере у нас есть таблица products содержащая следующие данные:

Введите следующий запрос UPDATE:

Будет обновлена 1 запись. Снова выберите данные из таблицы products :

Вот результаты, которые вы должны получить:

В этом примере будут обновлены все значения category_id в таблице products до 110, где category_id содержит значение NULL. Как видите, category_id в последней строке обновлен до 110.

Далее давайте рассмотрим пример использования условия IS NULL в операторе DELETE.
В этом примере у нас есть таблица orders со следующими данными:

Источник

Обработка значений NULL

Значения NULL и логика трех значений

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

Так как значение NULL считается неизвестным, два значения NULL, сравниваемые друг с другом, не считаются равными. В выражениях, использующих арифметические операторы, если какой-либо из операндов имеет значение NULL, результат также равен NULL.

Значения NULL и SqlBoolean

При сравнении между любыми типами System.Data.SqlTypes будет возвращаться значение SqlBoolean. Функция IsNull для каждого типа SqlType возвращает SqlBoolean и может использоваться для проверки на наличие значений NULL. В следующих таблицах истинности показано, как работают операторы AND, OR и NOT при наличии значения NULL. (T = true, F = false и U = неизвестно или NULL.)

Основные сведения о параметре ANSI_NULLS

Стандарт ANSI SQL-92 не поддерживает columnName = NULL в предложении WHERE. В SQL Server параметр ANSI_NULLS управляет допустимостью значений NULL по умолчанию в базе данных и вычислением сравнений со значениями NULL. Если параметр ANSI_NULLS включен (по умолчанию), то при проверке на наличие значений NULL в выражениях должен использоваться оператор IS NULL. Например, результатом следующего сравнения всегда является неизвестность при включенном параметре ANSI_NULLS:

Сравнение с переменной, содержащей значение NULL, также приводит к неизвестному результату:

Для тестирования на значение NULL используются предикаты IS NULL и IS NOT NULL. Это может усложнить предложение WHERE. Например, столбец TerritoryID в таблице AdventureWorks Customer допускает значения NULL. Если инструкция SELECT используется для тестирования на значения NULL в дополнение к другим, она должна включать предикат IS NULL:

Если в SQL Server параметр ANSI_NULLS отключен, можно создать выражения, которые используют оператор равенства для сравнения со значением NULL. Однако нельзя запретить другим подключениям задавать параметры NULL для этого подключения. Использование параметра IS NULL для проверки на наличие значений NULL всегда работает, независимо от установленного значения ANSI_NULLS для подключения.

Присвоение значений NULL

Значения NULL являются специальными, и их семантика хранения и назначения различается в разных системах типов и системах хранения. Dataset предназначен для использования с различными системами типов и хранения.

В этом разделе описывается семантика значений NULL для присвоения значений NULL для DataColumn в DataRow в различных системах типов.

Назначение нескольких столбцов (строк)

Кроме того, следующие правила применяются к экземпляру назначений NULL DataRow.[«columnName»] :

Используемое по умолчанию значение default является DbNull.Value для всех столбцов, за исключением строго типизированных нулевых столбцов с допустимыми строго типизированными значениями NULL.

Значения NULL никогда не записываются во время сериализации в XML-файлы (как в xsi:nil).

Все значения, в том числе по умолчанию, отличные от NULL, всегда записываются при сериализации в XML. Это отличается от семантики XSD/XML, где значение NULL (xsi: nil) является явным, а значение по умолчанию — неявным (если отсутствует в XML, то проверяющее средство синтаксического анализа может получить его из связанной схемы XSD). Обратное верно для DataTable : значение NULL является неявным, а значение по умолчанию — явным.

Всем отсутствующим значениям столбцов для строк, считываемых из входных данных XML, присваивается значение NULL. Строкам, созданным с помощью NewRow или аналогичных методов, присваивается значение по умолчанию DataColumn.

Присвоение значений NULL для SqlTypes

Значение по умолчанию для любого экземпляра System.Data.SqlTypes— NULL.

Значения NULL могут быть назначены DataColumn, как показано в следующем примере кода. Вы можете напрямую назначить значения NULL для переменных SqlTypes без запуска исключения.

Пример

В следующем примере кода показано создание DataTable с двумя столбцами, определенными как SqlInt32 и SqlString. Код добавляет одну строку известных значений, одну строку значений NULL, а затем выполняет итерацию по DataTable, присваивая значения переменным и отображая выходные данные в окне консоли.

В этом примере отображаются следующие результаты:

Источник

Функция ISNULL (Transact-SQL)

Заменяет значение NULL указанным замещающим значением.

Синтаксические обозначения в Transact-SQL

Синтаксис

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Аргументы

check_expression
Выражение, которое необходимо проверить на равенство значению NULL. Аргумент check_expression может быть любого типа.

replacement_value
Выражение, возвращаемое, если check_expression имеет значение NULL. Аргумент replacement_value должен иметь тип, который может быть неявно преобразован в тип check_expression.

Типы возвращаемых данных

Возвращает тип, совпадающий с типом выражения check_expression. Если в аргументе check_expression предоставлено литеральное значение NULL, возвращает тип данных replacement_value. Если в аргументе check_expression предоставлено литеральное значение NULL, а аргумент replacement_value не задан, возвращает int.

Remarks

Возвращается значение check_expression, если это выражение не равно NULL. В противном случае возвращается значение replacement_value. Если типы являются разными, то тип replacement_value неявно преобразуется в тип check_expression. Значение replacement_value может усекаться, если значение replacement_value длиннее, чем check_expression.

Для возврата первого значения, отличного от NULL, используйте функцию COALESCE (Transact-SQL).

Примеры

A. Использование функции ISNULL с функцией AVG

Б. Использование функции ISNULL

В. Проверка значений NULL в предложении WHERE

Не используйте для поиска значений NULL выражение ISNULL, вместо него следует использовать выражение IS NULL. В следующем примере выполняется поиск всех продуктов, имеющих значение NULL в столбце веса. Заметьте, что между словами IS и NULL стоит пробел.

Примеры: Azure Synapse Analytics и Система платформы аналитики (PDW)

Г. Использование функции ISNULL с функцией AVG

Д. Использование функции ISNULL

В приведенном ниже примере функция ISNULL используется для поиска значений NULL в столбце MinPaymentAmount и отображения значения 0.00 для соответствующих строк.

Здесь приводится частичный результирующий набор.

Е. Использование функции IS NULL для проверки на значение NULL в предложении WHERE

Источник

Заметка про NULL

Основные положения

Для удобства сделаем процедуру, печатающую состояние булевого параметра:

и включим опцию печати сообщений на консоль:

Привычные операторы сравнения пасуют перед NULLом:

Сравнение с NULLом

Соответственно, IS NOT NULL действует наоборот: вернёт истину, если значение операнда отлично от NULLа и ложь, если он является NULLом:

DECODE идёт против системы:

Пример с составными индексами находится в параграфе про индексы.

Логические операции и NULL

В большинстве случаев неизвестный результат обрабатывается как ЛОЖЬ :

Отрицание неизвестности даёт неизвестность:

Операторы IN и NOT IN

Для начала сделаем несколько предварительных действий. Для тестов создадим таблицу T с одним числовым столбцом A и четырьмя строками: 1, 2, 3 и NULL

Включим трассировку запроса (для этого надо обладать ролью PLUSTRACE ).
В листингах от трассировки оставлена только часть filter, чтобы показать, во что разворачиваются указанные в запросе условия.

Предварительные действия закончены, давайте теперь поработаем с операторами. Попробуем выбрать все записи, которые входят в набор (1, 2, NULL) :

Попробуем теперь с NOT IN :

Вообще ни одной записи! Давайте разберёмся, почему тройка не попала в результаты запроса. Посчитаем вручную фильтр, который применила СУБД, для случая A=3 :

Из-за особенностей трёхзначной логики NOT IN вообще не дружит с NULLами: как только NULL попал в условия отбора, данных не ждите.

NULL и пустая строка

Здесь Oracle отходит от стандарта ANSI SQL и провозглашает эквивалентность NULLа и пустой строки. Это, пожалуй, одна из наиболее спорных фич, которая время от времени рождает многостраничные обсуждения с переходом на личности, поливанием друг друга фекалиями и прочими непременными атрибутами жёстких споров. Судя по документации, Oracle и сам бы не прочь изменить эту ситуацию (там сказано, что хоть сейчас пустая строка и обрабатывается как NULL, в будущих релизах это может измениться), но на сегодняшний день под эту СУБД написано такое колоссальное количество кода, что взять и поменять поведение системы вряд ли реально. Тем более, говорить об этом они начали как минимум с седьмой версии СУБД (1992-1996 годы), а сейчас уже двенадцатая на подходе.

NULL и пустая строка эквивалентны:

непременный атрибут жёсткого спора:

Длина пустой строки не определена:

Сравнение с пустой строкой невозможно:

Критики подхода, предлагаемого Ораклом, говорят о том, что пустая строка не обязательно обозначает неизвестность. Например, менеджер по продажам заполняет карточку клиента. Он может указать его контактный телефон (555-123456), может указать, что он неизвестен (NULL), а может и указать, что контактный телефон отсутствует (пустая строка). С оракловым способом хранения пустых строк реализовать последний вариант будет проблемно. С точки зрения семантики довод правильный, но у меня на него всегда возникает вопрос, полного ответа на который я так и не получил: как менеджер введёт в поле «телефон» пустую строку и как он в дальнейшем отличит его от NULLа? Варианты, конечно, есть, но всё-таки…

Вообще-то, если говорить про PL/SQL, то где-то глубоко внутри его движка пустая строка и NULL различаются. Один из способов увидеть это связан с тем, что ассоциативные коллекции позволяют сохранить элемент с индексом » (пустая строка), но не позволяют сохранить элемент с индексом NULL:

Использовать такие финты ушами на практике не стоит. Во избежание проблем лучше усвоить правило из доки: пустая строка и NULL в оракле неразличимы.

Математика NULLа

Этот маленький абзац писался пятничным вечером под пиво, на фоне пятничного РЕН-ТВшного фильма. Переписывать его лень, уж извините.

Очевидно, что мы ничем не сможем помочь Коле: неизвестное количество любовников Маши до замужества сводит все расчёты к одному значению — неизвестно. Oracle, хоть и назвался оракулом, в этом вопросе уходит не дальше, чем участники битвы экстрасенсов: он даёт очевидные ответы только на очевидные вопросы. Хотя, надо признать, что Oracle гораздо честнее: в случае с Колей он не будет заниматься психоанализом и сразу скажет: «я не знаю»:

С конкатенацией дела обстоят по другому: вы можете добавить NULL к строке и это её не изменит. Такая вот политика двойных стандартов.

NULL и агрегатные функции

Таблица с данными. Используется ниже много раз:

Пустые значения игнорируются агрегатами:

Набор данных только из NULLов:

Пустой набор данных:

NULL в OLAP

Удобная фишка sqlplus: при выводе данных заменяет NULL на указанную строку:

Проверяем дуализм NULLа в многомерном кубе:

Источник