Difference between "is not null" and "<> Null" in SQL Server? [duplicate]

Question

Could you help me, I need to understand about the difference between

select * from table where field <> NULL;

and

select * from table where field is not NULL;

and see

SELECT COUNT(*) where (1 = null) -- return 0
SELECT COUNT(*) where (1 <> null) -- return 0
SELECT COUNT(*) where (1 is not  null) -- return 1
SELECT COUNT(*) where (null = null) -- return 0
SELECT COUNT(*) where (null <> null) -- return 0
SELECT COUNT(*) where (null is null) -- return 1
SELECT COUNT(*) where (null is not  null) -- return 0

Why is null = null false?

Thanks in advance

Answer 1

1) First question about difference IS NULL vs = NULL:

Comparison operators like (=, <>, <, >, ...) with NULL always produce NULL. Use IS NULL/IS NOT NULL instead.

2) Second question "why (null = null) is false":

From SQL and the Snare of Three-Valued Logic:

One kind of NULL marks values which are:

missing because the value is unknown

and the other kind marks values that are missing because the attribute is missing.

When you try to compare NULL you actualy do something like

UNKNOWN = UNKNOWN

This is of course unknown.