Разрешить значение null sql что это
SQL Значение NULL
Что такое значение NULL?
Если поле в таблице является необязательным, то можно вставить новую запись или обновить запись без добавления значения в это поле. Затем поле будет сохранено с значением NULL.
Как проверить наличие NULL значений?
Вместо этого нам придется использовать оператор IS NULL и IS NOT NULL.
Синтаксис IS NULL
Синтаксис IS NOT NULL
Демо база данных
Ниже приведен выбор из таблицы «Customers» в образце базы данных Northwind:
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 1 |
Alfreds FutterkisteMaria AndersObere Str. 57Berlin12209Germany2Ana Trujillo Emparedados y heladosAna TrujilloAvda. de la Constitución 2222México D.F.05021Mexico3Antonio Moreno TaqueríaAntonio MorenoMataderos 2312México D.F.05023Mexico4
Around the HornThomas Hardy120 Hanover Sq.LondonWA1 1DPUK5Berglunds snabbköpChristina BerglundBerguvsvägen 8LuleåS-958 22Sweden
Оператор IS NULL
Оператор IS NULL используется для проверки пустых значений (NULL).
В следующем SQL файле перечислены все клиенты с значением NULL в поле «Address»:
Пример
Совет: Всегда используйте значение NULL для поиска значений NULL.
Оператор IS NOT NULL
Оператор IS NOT NULL используется для проверки непустых значений (NOT NULL).
В следующем SQL файле перечислены все клиенты со значением в поле «Address»:
Обработка значений 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 в различных системах типов.
Значение по умолчанию для любого экземпляра System.Data.SqlTypes— NULL.
Значения NULL могут быть назначены DataColumn, как показано в следующем примере кода. Вы можете напрямую назначить значения NULL для переменных SqlTypes без запуска исключения.
Пример
В следующем примере кода показано создание DataTable с двумя столбцами, определенными как SqlInt32 и SqlString. Код добавляет одну строку известных значений, одну строку значений NULL, а затем выполняет итерацию по DataTable, присваивая значения переменным и отображая выходные данные в окне консоли.
В этом примере отображаются следующие результаты:
Присвоение для многих столбцов или строк
Кроме того, следующие правила применяются к экземпляру назначений NULL DataRow.[«columnName»] :
Значение по умолчанию — DbNull.Value для всех, за исключением строго типизированных столбцов со значением NULL, где он является соответствующим строго типизированным значением NULL.
Значения NULL никогда не записываются во время сериализации в XML-файлы (как в xsi:nil).
Все значения, в том числе по умолчанию, отличные от NULL, всегда записываются при сериализации в XML. Это отличается от семантики XSD/XML, где значение NULL (xsi: nil) является явным, а значение по умолчанию — неявным (если отсутствует в XML, то проверяющее средство синтаксического анализа может получить его из связанной схемы XSD). Обратное верно для DataTable : значение NULL является неявным, а значение по умолчанию — явным.
Всем отсутствующим значениям столбцов для строк, считываемых из входных данных XML, присваивается значение NULL. Строкам, созданным с помощью NewRow или аналогичных методов, присваивается значение по умолчанию DataColumn.
Обработка значений 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, присваивая значения переменным и отображая выходные данные в окне консоли.
В этом примере отображаются следующие результаты:
Значение NULL: руководство для начинающих
Если вы далеки от работы с базами данных, для вас может быть открытием, что ноль – это не значение NULL, хотя, признаем, они созвучны. Кроме того, NULL не является значением пустой строки, хотя можно найти поле, содержащее данные любого типа.
NULL можно представить как значение для представления неизвестного фрагмента данных (обратите внимание: не нулевого, хотя поле при этом выглядит пустым). А еще он не равен ничему, даже другому NULL. И сегодня мы поговорим об этом загадочном (на первый взгляд) значении NULL более подробно.
Пример значения NULL
Итак, что вы должны знать о значении NULL? Давайте разбираться.
Представьте себе письменный стол, на котором лежат канцелярские принадлежности: 6 шариковых ручек и 2 простых карандаша. Также известно, что в ящике стола должны быть фломастеры. Но вот сколько их и есть ли они вообще — данных нет. Если нам нужно составить таблицу инвентаризации с вводом значения NULL, то выглядеть она будет так:
| InventoryID | Item | Количество |
| 1 | ручки | 6 |
| 2 | карандаши | 2 |
| 3 | фломастеры | NULL |
Как вы понимаете, принимать за «0» количество фломастеров в данном случае было бы неверным, так как подобная запись показывала бы, что фломастеров нет вообще. Но точные данные об их количестве отсутствуют, поэтому может оказаться, что несколько штук все же есть.
Значение NULL и НЕ NULL
IS NULL и IS NOT NULL – специально созданные операторы, которые осуществляют сравнение имеющихся NULLов. IS NULL возвращает истину, если операнда является NULLом. Соответственно, если операнд не является NULLом, то значение будет ложным.
select case when null is null then ‘YES’ else ‘NO’ end from dual; — YES
select case when ‘a’ is null then ‘YES’ else ‘NO’ end from dual; — NO
IS NOT NULL имеет обратный принцип: значение будет истинным, если операнд не является NULLом, и ложным, если он таковым является.
select case when ‘a’ is NOT null then ‘YES’ else ‘NO’ end from dual; — YES
select case when null is NOT null then ‘YES’ else ‘NO’ end from dual; — NO
Учтите, что когда речь идет об отсутствующих значениях, есть особые случаи их сравнения:
Вот так проявляет себя DECODE:
, null, ‘EMPTY’ — это условие будет истинным
Значение NULL в MySQL
Результат при сравнении NULLов, в зависимости от операции SQL, часто будет иметь значение NULL. Предположим, что А НЕДЕЙСТВИТЕЛЕН:
Ваш Путь в IT начинается здесь
Логические операции и NULL
Для логических операторов AND и OR есть свои особенности при работе со значением NULL. Краткое руководство рассмотрим на примере.
Чаще всего с неизвестным результатом работают как с ЛОЖЬЮ:
select 1 from dual where dummy = null; — запрос не вернёт записей
При отрицании неизвестности результатом будет НЕИЗВЕСТНО:
exec test_bool( not(null = null)); — UNKNOWN
exec test_bool( not(null = ‘a’) ); — UNKNOWN
exec test_bool(null or true); — TRUE Главный карьерный консультант
Мы в GeekBrains каждый день обучаем людей новым профессиям и точно знаем, с какими трудностями они сталкиваются. Вместе с экспертами по построению карьеры поможем определиться с новой профессией, узнать, с чего начать, и преодолеть страх изменений.
Карьерная мастерская это:
Уже 50 000 человек прошли мастерскую и сделали шаг к новой профессии!
Запишитесь на бесплатный курс и станьте ближе к новой карьере:
Зарегистрироваться и получить подарки
Операторы IN и NOT IN для значения NULL
Чтобы понять взаимодействие этих операторов с NULLом, рассмотрим пример.
Создадим таблицу Т, состоящую из одного числового столбца А и строками: 1, 2, 3 и NULL.
create table t as select column_value a from table(sys.odcinumberlist(1,2,3,null));
Затем выполним трассировку запроса (учтите, что для этого нужно обладать ролью PLUSTRACE).
От трассировки в листингах оставлена часть filter, чтобы показать преобразование указанных в запросе условий.
Теперь, после подготовительных действий, попробуем выбрать те записи, которые будут соответствовать набору (1, 2, NULL).
select * from t where a in(1,2,null); — вернёт [1,2]
— filter(«A»=1 OR «A»=2 OR «A»=TO_NUMBER(NULL))
По какой-то причине строка с NULLом не выбрана. Возможно, это случилось потому, что вычисление предиката «А»=TO_NUMBER(NULL) вернуло состояние НЕИЗВЕСТНО. Попробуем явно указать условие включения NULLов в результаты запросов:
select * from t where a in(1,2) or a is null; — вернёт [1,2,NULL]
— filter(«A» IS NULL OR «A»=1 OR «A»=2)
Попробуем с NOT IN:
select * from t where a not in(1,2,null); — no rows selected
— filter(«A»<>1 AND «A»<>2 AND «A»<>TO_NUMBER(NULL))
Ни одной записи так и не появилось.
Это объясняется тем, что трехзначная логика NOT IN не взаимодействует с NULLами: при попадании NULL в условия отбора данных можно не ждать.
Значение NULL и пустая строка в СУБД
Oracle отличается от стандартов ANSI SQL в определении NULLов: он проводит знак равенства между NULL и пустой строкой. Эта особенность программы рождает много споров, хотя Oracle и заявляет, что, возможно, в будущих релизах будет изменен подход в обработке пустой строки, как NULL. Но в реальности проведение таких изменений сомнительно, так как под эту СУБД написано неимоверное количество кода.
exec test_bool( » is null ); — TRUE
Если попытаться найти причину, почему вообще пустую строку стали считать эквивалентной NULL, то ответ можно найти в формате хранения varchar`ов и NULLов внутри блоков данных. Табличные строки Oracle хранит в структуре, представляющей собой заголовок и следующими за ним столбцы с данными.
Каждый столбец, в свою очередь, состоит из 2-х полей: длина данных в столбце (1 или 3 байта) и сами данные. При нулевой длине varchar2 в поле с данными нечего вносить, так как оно не занимает ни байта. В поле же, где указывается длина, вносится специальное значение 0xFF, что и означает отсутствие данных.
NULL Oracle представляет аналогично, то есть отсутствует поле с данными, а в поле длины данных вносится 0xFF. Так как изначально разработчики Oracle не разделяли эти два состояния, то и сейчас принцип внесения данных не изменился.
Значение NULL
Понятие «пустая строка» допустимо толковать как абсолютное отсутствие значения, так как ее длина равна нулю. NULL же, в свою очередь, имеет длину неопределенного значения. Поэтому выражение length (») возвращает NULL, а не ожидаемый ноль.
Еще одна причина, по которой нельзя сравнивать NULL с пустой строкой: выражение val = » вернёт состояние НЕИЗВЕСТНО, так как, по сути, идентично val=NULL.
Неопределенная длина пустой строки:
select length(») from dual; — NULL
Сравнение с пустой строкой невозможно:
Критика такого подхода Oracle к значениям NULL и пустой строки, основывается на том, что не всегда пустая строка может означать неизвестность. Например, когда менеджер-продавец вносит данные в карточку клиента, то в поле «Контактный номер» он может указать конкретный номер; также он может указать, что номер неизвестен (NULL); но еще он может указать, что номера как такового нет (пустая строка).
Отличия между null и undefined
Можно сказать, что NULL – это такое значение, которое является определенным для отсутствующего объекта. UNDEFINED же означает именно неопределенность. Например:
// значение переменной element до её инициализации не определённо: undefined
// здесь при попытке получения несуществующего элемента, метод getElementById возвращает null
// переменная element теперь инициализирована значением null, её значение определено
Осуществляя проверку на NULL или UNDEFINED, нужно помнить о разнице в операторах равенства (==) и идентичности (===): с первым оператором производится преобразование типов.
typeof null // object (не «null» из соображений обратной совместимости)
typeof undefined // undefined
null === undefined // false
null == undefined // true
Это все то, что вы должны знать о значении NULL. Обрастая опытом и применяя некоторые уловки для избежания NullPointerException, вы научитесь делать безопасный код. Главным образом неразбериха возникает из-за того, что NULL может трактоваться как пустое значение или как неидентифицированное.
Поэтому важно документально фиксировать поведение метода, когда есть входящее значение NULL. Держите в памяти, что NULL – это значение по умолчанию ссылочных переменных. И вызывать методы экземпляра или получать доступ к переменным экземпляра, применяя 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