The sql-92 standard requires that an equals (=) or not equal to (<>) comparison against a null value evaluates to FALSE.

When SET ANSI_NULLS is ON,a SELECT statement using WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement using WHERE column_name <> NULL returns zero rows even if there are non-null values in column_name.

When SET ANSI_NULLS is OFF,the Equals (=) and Not Equal To (<>) comparison operators do not follow the sql-92 standard. A SELECT statement using WHERE column_name = NULL returns the rows with null values in column_name. A SELECT statement using WHERE column_name <> NULL returns the rows with non-null values in the column. In addition,a SELECT statement using WHERE column_name <> XYZ_value returns all rows that are not XYZ_value and that are not NULL.