I learned this fact the hard way the other day! A MERGE statement would not update null values in the destination table with the appropriate non-null data from the source table. My AND clause looked something like “Source.Name != Destination.Name”. I thought that setting ANSI_NULLS to OFF would cause this clause to catch differences when one of the values was null.
NULL & Equals
By default, Microsoft SQL Server’s equals (=) and not equals (!=, <>) operators always evaluate to unknown (in effect, false) when one or both operands are null. This behavior is in accordance with the relevant ISO standard.
Setting the option ANSI_NULLS to OFF changes this behavior so that = and !=/<> behave identically to IS NULL and IS NOT NULL so long as at least one of the operands is either a literal null value or a variable set to null.
However, SET ANSI_NULLS OFF has no effect on comparisons between columns. If one or both of the column values are null, the following condition evaluates to false even if ANSI_NULLS is OFF.
WHERE Source.Name != Destination.Name
To match differences between the two columns, including cases when one or the other is null, a clause similar to the following must be used:
WHERE ( (Source.Name != Destination.Name) OR (Source.Name IS NULL AND Destination.Name IS NOT NULL) OR (Source.Name IS NOT NULL AND Destination.Name IS NULL) )
These rules apply to all uses of = and !=/<>, regardless of whether they occur in WHERE statements, JOIN conditions, AND clauses, or otherwise.
For more information on Microsoft SQL Server’s ANSI_NULLS option, visit MSDN.