Null missing value:
If there is null in the expression, the result is also null. The null value is never equal to other values, and it is not equal to itself. Most functions that contain null return values are also null. One exception is that the function is designed to process null first. Including isnull and coalesce.
A null value in a relational database can cause great consumption. Assume the following tables and data:
Create Table # values (K1 int identity, C1 int null)
Insert # values (C1) values (1)
Insert # values (C1) values (1)
Insert # values (C1) values (null)
Insert # values (C1) values (9)
This query:
Select * From @ values where c1 = 1
And
Select * From @ values where c1 <> 1
It may be considered that all rows in the # values table will be returned, but not actually. To obtain all rows, the null value must be taken into account. Therefore, the SQL statement should be written as follows:
Select * from # values where is C1 = 1 or C1 is null
An acceptable method to avoid null is to use a dummy element value to represent the missing value. For example, you can use "N/A" or "NV" to replace null in a string column; you can use-1 to represent the missing values in multiple numeric columns. You can use "1900-01-01" to replace dates.
The above is the Reading Notes of the book "Transact-SQL authoritative guide", thanks to the author Ken Henderson and the translator jian lian Technology China Power Press for bringing me such a classic T-SQL book.
