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.