Как выбрать null в sql запросе



Использование значения NULL в условиях поиска

Так, если требуется найти записи в таблице PC, для которых в столбце price отсутствует значение (например, при поиске ошибок ввода), можно воспользоваться следующим оператором:

Характерной ошибкой является написание предиката в виде:

Этому предикату не соответствует ни одной строки, поэтому результирующий набор записей будет пуст, даже если имеются изделия с неизвестной ценой. Это происходит потому, что сравнение с NULL -значением согласно предикату сравнения оценивается как UNKNOWN . А строка попадает в результирующий набор только в том случае, если предикат в предложении WHERE есть TRUE . Это же справедливо и для предиката в предложении HAVING .

Аналогичной, но не такой очевидной ошибкой является сравнение с NULL в предложении CASE (см. пункт 5.10). Чтобы продемонстрировать эту ошибку, рассмотрим такую задачу: «Определить год спуска на воду кораблей из таблицы Outcomes. Если последний неизвестен, указать 1900».

Поскольку год спуска на воду (launched) находится в таблице Ships, нужно выполнить левое соединение (см. пункт 5.6):

Для кораблей, отсутствующих в Ships, столбец launched будет содержать NULL -значение. Теперь попробуем заменить это значение значением 1900 с помощью оператора CASE (см. пункт 5.10):

Однако ничего не изменилось. Почему? Потому что использованный оператор CASE эквивалентен следующему:

А здесь мы получаем сравнение с NULL -значением, и в результате — UNKNOWN , что приводит к использованию ветви ELSE, и все остается, как и было. Правильным будет следующее написание:

Источник статьи: http://www.sql-tutorial.ru/ru/book_using_null_in_search_conditions.html

Работа с 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

Источник статьи: http://sql-language.ru/rabota-s-null-znacheniyami.html

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

Скачать ADO.NET

Значение NULL в реляционной базе данных используется, если значение в столбце неизвестно или отсутствует. A null value in a relational database is used when the value in a column is unknown or missing. NULL не является ни пустой строкой (для типов данных character или datetime), ни нулевым значением (для числовых типов данных). A null is neither an empty string (for character or datetime data types) nor a zero value (for numeric data types). В спецификации ANSI SQL-92 указано, что значение NULL должно быть одинаковым для всех типов данных, чтобы все значения NULL обрабатывались согласованно. The ANSI SQL-92 specification states that a null must be the same for all data types, so that all nulls are handled consistently. Пространство имен System.Data.SqlTypes обеспечивает семантику со значением NULL, реализуя интерфейс INullable. The System.Data.SqlTypes namespace provides null semantics by implementing the INullable interface. Каждый из типов данных в System.Data.SqlTypes имеет собственное свойство IsNull и значение Null , которое может быть назначено экземпляру этого типа данных. Each of the data types in System.Data.SqlTypes has its own IsNull property and a Null value that can be assigned to an instance of that data type.

В версиях .NET Framework 2.0 и .NET Core 1.0 появилась поддержка типов, допускающих значение NULL, что позволяет программистам расширять тип значения для представления всех значений базового типа. The .NET Framework version 2.0 and .NET Core version 1.0 introduced support for nullable types, which allow programmers to extend a value type to represent all values of the underlying type. Эти типы CLR, допускающие значение NULL, представляют экземпляр структуры Nullable. These CLR nullable types represent an instance of the Nullable structure. Эта возможность особенно полезна, если типы значений упакованы и распакованы, что обеспечивает улучшенную совместимость с типами объектов. This capability is especially useful when value types are boxed and unboxed, providing enhanced compatibility with object types. Типы CLR, допускающие значение NULL, не предназначены для хранения значений NULL базы данных, так как значение NULL ANSI SQL не работает так же, как ссылка на null (или Nothing в Visual Basic). CLR nullable types are not intended for storage of database nulls because an ANSI SQL null does not behave the same way as a null reference (or Nothing in Visual Basic). Для работы со значениями NULL в базе данных ANSI SQL используйте значения NULL System.Data.SqlTypes вместо Nullable. For working with database ANSI SQL null values, use System.Data.SqlTypes nulls rather than Nullable. Дополнительные сведения о работе на C# с типами CLR, допускающими значения NULL, см. в этой статье. For more information on working with CLR nullable types in C# see Nullable Types, and for C# see Using Nullable Types.

Значения NULL и логика трех значений Nulls and three-valued logic

Разрешение значений NULL в определениях столбцов вводит в приложение логику трех значений. Allowing null values in column definitions introduces three-valued logic into your application. Результатом сравнения может быть одно из трех условий: A comparison can evaluate to one of three conditions:

Так как значение NULL считается неизвестным, два значения NULL, сравниваемые друг с другом, не считаются равными. Because null is considered to be unknown, two null values compared to each other are not considered to be equal. В выражениях, использующих арифметические операторы, если какой-либо из операндов имеет значение NULL, результат также равен NULL. In expressions using arithmetic operators, if any of the operands is null, the result is null as well.

Значения NULL и SqlBoolean Nulls and SqlBoolean

При сравнении между любыми типами System.Data.SqlTypes будет возвращаться значение SqlBoolean. Comparison between any System.Data.SqlTypes will return a SqlBoolean. Функция IsNull для каждого типа SqlType возвращает SqlBoolean и может использоваться для проверки на наличие значений NULL. The IsNull function for each SqlType returns a SqlBoolean and can be used to check for null values. В следующих таблицах истинности показано, как работают операторы AND, OR и NOT при наличии значения NULL. The following truth tables show how the AND, OR, and NOT operators function in the presence of a null value. (T = true, F = false и U = неизвестно или NULL.) (T=true, F=false, and U=unknown, or null.)

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

System.Data.SqlTypes предоставляет ту же семантику, что и при установке параметра ANSI_NULLS в SQL Server. System.Data.SqlTypes provides the same semantics as when the ANSI_NULLS option is set on in SQL Server. Все арифметические операторы (+, -, *, /, %), битовые операции (

, &, |) и большинство функций возвращают NULL, если какие-либо из операндов или аргументов равны NULL, за исключением операндов или аргументов для свойства IsNull . All arithmetic operators (+, -, *, /, %), bitwise operators (

, &, |), and most functions return null if any of the operands or arguments is null, except for the property IsNull .

Стандарт ANSI SQL-92 не поддерживает columnName = NULL в предложении WHERE. The ANSI SQL-92 standard does not support columnName = NULL in a WHERE clause. В SQL Server параметр ANSI_NULLS управляет допустимостью значений NULL по умолчанию в базе данных и вычислением сравнений со значениями NULL. In SQL Server, the ANSI_NULLS option controls both default nullability in the database and evaluation of comparisons against null values. Если параметр ANSI_NULLS включен (по умолчанию), то при проверке на наличие значений NULL в выражениях должен использоваться оператор IS NULL. If ANSI_NULLS is turned on (the default), the IS NULL operator must be used in expressions when testing for null values. Например, результатом следующего сравнения всегда является неизвестность при включенном параметре ANSI_NULLS: For example, the following comparison always yields unknown when ANSI_NULLS is on:

Сравнение с переменной, содержащей значение NULL, также приводит к неизвестному результату: Comparison to a variable containing a null value also yields unknown:

Для тестирования на значение NULL используются предикаты IS NULL и IS NOT NULL. Use the IS NULL or IS NOT NULL predicate to test for a null value. Это может усложнить предложение WHERE. This can add complexity to the WHERE clause. Например, столбец TerritoryID в таблице AdventureWorks Customer допускает значения NULL. For example, the TerritoryID column in the AdventureWorks Customer table allows null values. Если инструкция SELECT используется для тестирования на значения NULL в дополнение к другим, она должна включать предикат IS NULL: If a SELECT statement is to test for null values in addition to others, it must include an IS NULL predicate:

Если в SQL Server параметр ANSI_NULLS отключен, можно создать выражения, которые используют оператор равенства для сравнения со значением NULL. If you set ANSI_NULLS off in SQL Server, you can create expressions that use the equality operator to compare to null. Однако нельзя запретить другим подключениям задавать параметры NULL для этого подключения. However, you can’t prevent different connections from setting null options for that connection. Использование параметра IS NULL для проверки на наличие значений NULL всегда работает, независимо от установленного значения ANSI_NULLS для подключения. Using IS NULL to test for null values always works, regardless of the ANSI_NULLS settings for a connection.

Установка ANSI_NULLS OFF не поддерживается в DataSet , который всегда соответствует стандарту ANSI SQL-92 для обработки значений NULL в System.Data.SqlTypes. Setting ANSI_NULLS off is not supported in a DataSet , which always follows the ANSI SQL-92 standard for handling null values in System.Data.SqlTypes.

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

Значения NULL являются специальными, и их семантика хранения и назначения различается в разных системах типов и системах хранения. Null values are special, and their storage and assignment semantics differ across different type systems and storage systems. Dataset предназначен для использования с различными системами типов и хранения. A Dataset is designed to be used with different type and storage systems.

В этом разделе описывается семантика значений NULL для присвоения значений NULL для DataColumn в DataRow в различных системах типов. This section describes the null semantics for assigning null values to a DataColumn in a DataRow across the different type systems.

DBNull.Value
Это назначение допустимо для любого типа DataColumn . This assignment is valid for a DataColumn of any type. Если тип реализует INullable , DBNull.Value приводится к соответствующему строго типизированному значению NULL. If the type implements INullable , DBNull.Value is coerced into the appropriate strongly typed Null value.

SqlType.Null
Все типы данных System.Data.SqlTypes реализуют INullable . All System.Data.SqlTypes data types implement INullable . Если строго типизированное значение NULL может быть преобразовано в тип данных столбца с помощью операторов неявного приведения, то назначение должно быть принятым. If the strongly typed null value can be converted into the column’s data type using implicit cast operators, the assignment should go through. Иначе будет вызвано исключение недопустимого приведения. Otherwise an invalid cast exception is thrown.

null
Если значение NULL является допустимым для указанного типа данных DataColumn , оно приводится к соответствующему значению DbNull.Value или Null , связанному с типом INullable ( SqlType.Null ). If ‘null’ is a legal value for the given DataColumn data type, it is coerced into the appropriate DbNull.Value or Null associated with the INullable type ( SqlType.Null )

derivedUdt.Null
Для столбцов пользовательского типа значения NULL всегда хранятся в зависимости от типа, связанного с DataColumn . For UDT columns, nulls are always stored based on the type associated with the DataColumn . Рассмотрим случай пользовательского типа, связанного с DataColumn , который не реализует INullable в отличие от своего подкласса. Consider the case of a UDT associated with a DataColumn that does not implement INullable while its sub-class does. В этом случае, если назначено строго типизированное значение NULL, связанное с производным классом, оно сохраняется как нетипизированное значение DbNull.Value , так как хранилище значений NULL всегда согласуется с типом данных DataColumn. In this case, if a strongly typed null value associated with the derived class is assigned, it is stored as an untyped DbNull.Value , because null storage is always consistent with the DataColumn’s data type.

В настоящее время структура Nullable или Nullable не поддерживается в DataSet . The Nullable or Nullable structure is not currently supported in the DataSet .

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

DataTable.Add , DataTable.LoadDataRow или другие API-интерфейсы, принимающие массив ItemArray, который сопоставляется со строкой, сопоставляют значение NULL со значением по умолчанию DataColumn. DataTable.Add , DataTable.LoadDataRow , or other APIs that accept an ItemArray that gets mapped to a row, map ‘null’ to the DataColumn’s default value. Если объект в массиве содержит DbNull.Value или строго типизированный аналог, применяются те же правила, которые описаны выше. If an object in the array contains DbNull.Value or its strongly typed counterpart, the same rules as described above are applied.

Кроме того, следующие правила применяются к экземпляру назначений NULL DataRow.[«columnName»] : In addition, the following rules apply for an instance of DataRow.[«columnName»] null assignments:

Используемое по умолчанию значение default является DbNull.Value для всех столбцов, за исключением строго типизированных нулевых столбцов с допустимыми строго типизированными значениями NULL. The default default value is DbNull.Value for all except the strongly typed null columns where it is the appropriate strongly typed null value.

Значения NULL никогда не записываются во время сериализации в XML-файлы (как в xsi:nil). Null values are never written out during serialization to XML files (as in «xsi:nil»).

Все значения, в том числе по умолчанию, отличные от NULL, всегда записываются при сериализации в XML. All non-null values, including defaults, are always written out while serializing to XML. Это отличается от семантики XSD/XML, где значение NULL (xsi: nil) является явным, а значение по умолчанию — неявным (если отсутствует в XML, то проверяющее средство синтаксического анализа может получить его из связанной схемы XSD). This is unlike XSD/XML semantics where a null value (xsi:nil) is explicit and the default value is implicit (if not present in XML, a validating parser can get it from an associated XSD schema). Обратное верно для DataTable : значение NULL является неявным, а значение по умолчанию — явным. The opposite is true for a DataTable : a null value is implicit and the default value is explicit.

Всем отсутствующим значениям столбцов для строк, считываемых из входных данных XML, присваивается значение NULL. All missing column values for rows read from XML input are assigned NULL. Строкам, созданным с помощью NewRow или аналогичных методов, присваивается значение по умолчанию DataColumn. Rows created using NewRow or similar methods are assigned the DataColumn’s default value.

Метод IsNull возвращает true как для DbNull.Value , так и для INullable.Null . The IsNull method returns true for both DbNull.Value and INullable.Null .

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

Значение по умолчанию для любого экземпляра System.Data.SqlTypes— NULL. The default value for any System.Data.SqlTypes instance is null.

Значения NULL в System.Data.SqlTypes относятся к определенному типу и не могут быть представлены одним значением, таким как DbNull . Nulls in System.Data.SqlTypes are type-specific and cannot be represented by a single value, such as DbNull . Чтобы проверить на наличие значений NULL, используйте свойство IsNull . Use the IsNull property to check for nulls.

Значения NULL могут быть назначены DataColumn, как показано в следующем примере кода. Null values can be assigned to a DataColumn as shown in the following code example. Вы можете напрямую назначить значения NULL для переменных SqlTypes без запуска исключения. You can directly assign null values to SqlTypes variables without triggering an exception.

Пример Example

В следующем примере кода показано создание DataTable с двумя столбцами, определенными как SqlInt32 и SqlString. The following code example creates a DataTable with two columns defined as SqlInt32 and SqlString. Код добавляет одну строку известных значений, одну строку значений NULL, а затем выполняет итерацию по DataTable, присваивая значения переменным и отображая выходные данные в окне консоли. The code adds one row of known values, one row of null values and then iterates through the DataTable, assigning the values to variables and displaying the output in the console window.

В этом примере отображаются следующие результаты: This example displays the following results:

Сравнение значений NULL с SqlTypes и типами CLR Comparing null values with SqlTypes and CLR types

При сравнении значений NULL важно понимать разницу между тем, как метод Equals вычисляет значения NULL в System.Data.SqlTypes по сравнению с тем, как он работает с типами CLR. When comparing null values, it is important to understand the difference between the way the Equals method evaluates null values in System.Data.SqlTypes as compared with the way it works with CLR types. Все методы System.Data.SqlTypes Equals используют семантику базы данных для вычисления значений NULL. Если одно или оба значения являются NULL, результатом сравнения будет NULL. All of the System.Data.SqlTypes Equals methods use database semantics for evaluating null values: if either or both of the values is null, the comparison yields null. С другой стороны, при использовании метода Equals CLR для двух System.Data.SqlTypes вернется значение true, если оба значения соответствуют NULL. On the other hand, using the CLR Equals method on two System.Data.SqlTypes will yield true if both are null. Это отражает разницу между использованием метода экземпляра, такого как метод String.Equals CLR, и использованием статического или общего метода SqlString.Equals . This reflects the difference between using an instance method such as the CLR String.Equals method, and using the static/shared method, SqlString.Equals .

В следующем примере показана разница результатов между методами SqlString.Equals и String.Equals , если каждому из них передается пара значений NULL, а затем пара пустых строк. The following example demonstrates the difference in results between the SqlString.Equals method and the String.Equals method when each is passed a pair of null values and then a pair of empty strings.

Получается следующий вывод: The code produces the following output:

Источник статьи: http://docs.microsoft.com/ru-ru/sql/connect/ado-net/sql/handle-null-values?view=sql-server-ver15


Adblock
detector