While comparing a field to NULL, a SQL query should use the IS operator in place of the “=” operator. The reason is that if the ANSI_NULLS option is On for a connection, expressions being compared to a NULL value will not return a true or false value but rather they will return an unknown value. To make it simpler, if a table xxx has a column by name col1 that contains two NULL values then this query would result in fetching zero records:
Select * from xxx where col1 = NULL
Meanwhile, this query would result in fetching two records:
Select * from xxx where col1 IS NULL
However, if you set ANSI_NULLS off then both of these queries would return two records. Similarly, it is better to use IS NOT in place of operator “<>” while comparing against NULL.