Null values in SQL Server null value problem

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:

1. < Span style= "FONT-FAMILY:SIMSUN; font-size:10.5pt; Font-weight:normal; Font-style:normal, "lang=" ZH-CN "> nullnull

1. < 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