Original: null value problem in SQL Server
SQL uses three-valued predicate logic, so the result returned by a logical expression can be true, false, or unknown, and returning true in the three-value logic is not exactly the same as not returning false.
SQL handling of query filtering conditions: Accept Ture deny false and unknown
SQL handling of CHECK constraints: Accept FALSE deny TRUE and UNKnown
the subtlety of unknown is when it is reversed results are still unknown ,
The expression (Null=null) that compares two Null values is still evaluated as unknown because the null value represents an unknown value, so it is not really possible to determine whether an unknown value equals another, so SQL provides two predicates is Null and is Not NULL to replace =null and <>null, when filtering data with the not-in predicate, the value is never returned if the in value contains null (SELECT ordernum from [dbo].[ SalesOrder] WHERE ordernum not in (' 100128054 ', NULL))
When grouping and sorting , two null values are considered equal, meaning that the group by clause will reorganize all null values in each group
orderbynullt-sqlnull worth qualifying before the valid values
All of the aggregation functions will ignoreNullValue, with only one exceptionCount (*),Suppose a group has two rows and itsqty 3,null, expression count (*) return 2,count (qty) return 1 because a row is a known value
There are two UNIque constraints of ANSI SQL:
- < Span style= "FONT-FAMILY:SIMSUN; font-size:10.5pt; Font-weight:normal; Font-style:normal, "lang=" ZH-CN "> nullnull
- < Span style= "FONT-FAMILY:SIMSUN; font-size:10.5pt; Font-weight:normal; Font-style:normal, "lang=" ZH-CN "> nullnull
SQL Server only implements the former
Null values in SQL Server null value problem