NULL comparison conditions are different from others. Because NULL means UNKNOWN, there are three conditions when using NULL:
This is because a value that is unknown cannot be compared logically against any other value.
T-SQL supports an extension that allows for the comparison operators to return TRUE or FALSE when comparing against null values. This option is activated by setting ANSI_NULLS OFF.
In some cases we need it very much for example in complex stored procedures or triggers. These tow query results are the same:
SELECT * FROM tbluser WHERE notes IS NULL ;
SET ANSI_NULLS OFF go SELECT * FROM tbluser WHERE notes = NULL ; go