Null values in SQL

The null value does not satisfy any lookup criteria other than is [not] null.
– If NULL participates in arithmetic operations, the value of the arithmetic expression is null.
– If NULL participates in the comparison operation, the result can be treated as false. can be regarded as unknown in SQL-92.
– If NULL participates in the aggregation operation, all other aggregation functions except count (*) ignore null.
Example: select SUM (SAL)
From PROF
Example: SELECT COUNT (*)
From PROF
–

Detailed analysis

A null value in SQL that indicates that the data is not known (UNKNOWN), used as a specific value that does not know the data, or does not know whether the data exists, or if the data does not exist.

Because of the introduction of NULL values, the logical system of SQL is a logical system of three-valued logical system--false, TRUE, and null three values.
1, or, and, not truth table
A B a OR b A and B not a
False false to False TRUE
False true True false-
False NULL NULL false-
True false true False false
True True True-
True null true null-
NULL false null FALSE NULL
Null true True null-
NULL NULL NULL NULL-


Because null is understood when unknown, therefore, when the value is null, it can be both false and possibly true. If there is only one possible result of the calculation, it can be

If the result of the calculation is either false or true, the result will be null. For example, not NULL, the result also has two possibilities, so not NULL

The result is null. For example, null and true, there are two possible results, so null and true result is null. However, NULL or TRUE, or

NULL and False, the former result is true, it is sufficient to know that the result is true if it is true, and the latter result is false, which is sufficient to

Know that the result is false. This is determined by the properties of OR and and.
2, comparison operators and NULL in arithmetic operators
Comparison operators and NULL in arithmetic operators cause the result of the operation to be null as well. As the result of 3+null, the result of Null,null>=5 is NULL, and so on.
3. Null in between and, in
A between B and C means a>=b and a<=c, so the case of a null value can be judged by reference to the latter. A not between B and C are equivalent to

Not (A>=b and a<=c).
A in (b,c,d) means a=b or a=c or a=d, so that it produces a null value, which can be judged by reference to the latter. A not-in (B,C,D) corresponds to the equivalent of not (A=b OR

A=c OR A=d). When the collection is empty, the result of in is false,not in, which is true.
4, EXISTS, is NULL
EXISTS (and not EXISTS), is null (and is not null), produce only false or true, without generating null.
5, any, all
When a value is compared to a collection (a set of values), the comparison operator can add any (or some, the same effect) or all.
A>any (b,c,d) means a>b or a>c or a>d, so it can be judged by the latter when it produces a null value. When the collection is empty, the result of any is false.
A>all (b,c,d) means a>b and a>c and a>d, so it is possible to refer to the latter for the case where a null value is generated. When the collection is empty, the result of all is true.
6. WHERE, have, on condition filtering, and integrity constraint checking
In the Where, have, on condition filter, only the result is true, not false and null. But in the integrity constraint check, only the result is false

To violate the constraint check, NULL does not violate.



Two functions related to null values

Nullif: two parameters required

Example: Nullif (A, B)
Description: If A and b are equal, then return NULL if not equal to return a
Select Nullif (' eqeqweqwe ', ' 1 ') turns out to be eqeqweqwe
Select Nullif () result is null
The type of a and B should be consistent


ISNULL: two parameters required

Example: ISNULL (A, B)
Description: If A and b are both NULL, return NULL if A is null,b not NULL, return B if a is not null,b null returns a If both A and B are not null

Returns a
Select ISNULL (null,null) result is null
Select ISNULL (null,33) results are 33
Select ISNULL (' DDD ', null) result is DDD
Select ISNULL (44,33) results are 44

Reprinted from: http://blog.sina.com.cn/s/blog_6a6eb42d0100ka56.html

Null values in SQL