T-sql: Is null is NOT NULL (vii)

First, SQL SERVER is a three-valued logic that predicate evaluates to True,false,unknown

The standard predicates are those that follow this rule.

If slary>0 returns a result that evaluates to true the row rejects false and the unknown result

But not all predicate processing is like this if it appears in a check constraint that evaluates to False to deny false means to accept true and unknown

TRUE and False we all know what that means. How to avoid UNKNOWN?

1.UNKNOWN

is UNKNOWN to get the result null this is quite normal not is to take the inverse result also null this is confusing not UNKNOWN still equals UNKNOWN

Null=null evaluates to unknown NULL means missing, simple point is that an unknown value cannot be equal to another unknown value

So SQL provides two predicates to determine if NULL is null and is not NULL instead of =null <>null

Remember <> calculation results do not include NULL for example

SELECT CustID, country, region, City  from sales.customers WHERE <> N'WA';

You can see the first picture with no null line

Then we query for NULL.

SELECT CustID, country, region, City  from sales.customers WHERE = NULL;

So we use is NULL instead of =

SELECT CustID, country, region, City  from sales.customers WHERE  is NULL;

If you want to reverse back not equal to WA including null

SELECT CustID, country, region, City  from sales.customers WHERE <> N'WA'   ORisNULL;

Oddly, the Null=null is established in the grouping and sorting predicates.

NULL is also considered equal in a unique constraint

T-sql: Is null is NOT NULL (vii)