ABSTRACT

The result of null comparisons using the = and != operators is inconsistent.

EXPLANATION

In the ANSI SQL standard, the value of NULL is defined as unknown. It is not equal to anything, not even another NULL value. Also, a null value is never not equal to another value. By default, T-SQL adopts the same behavior, but it can be turned off using the SET ANSI_NULLS OFF command or setting the database ANSI_NULLS option. In addition, certain database operations cannot or should not be performed if ANSI_NULLS is turned off. Therefore, it is safest to make all T-SQL code ANSI compliant.

Example 1: The code in the following example will only print the message if the ANSI_NULLS option has been turned off:

SET @x = NULLIF @x = NULL PRINT "ANSI NULLS OFF"


Example 2: The code in the following example will only print the message if the ANSI_NULLS option is turned on:
SET @x = 1
IF @x != NULL PRINT "ANSI NULLS ON"

REFERENCES

[1] Standards Mapping - Common Weakness Enumeration - (CWE) CWE ID 480

[2] Null Values: SQL Server 2008