sql server 2008 - Issues with SQL comparison and null values

Question

I have an update query that updates a field in one table when the value does not match a field in another table.

UPDATE  table1
SET     a.field1 = b.field3
FROM    table1 a ,
        table2 b
WHERE   a.field2 = b.field2
        AND a.field1 <> b.field3

The problem I am having is that it is not picking up when a.field1 is null and b.field3 is a value OR if a.field1 is a value and b.field3 is null.

I have gotten around this by adding the following...

UPDATE  table1
SET     a.field1 = b.field3
FROM    table1 a ,
        table2 b
WHERE   a.field2 = b.field2
        AND ( a.field1 <> b.field3
              OR (a.field1 IS NOT NULL
              AND b.field3 IS NULL)
              OR (a.field1 IS NULL
              AND b.field3 IS NOT NULL)
            )

My question is more centered around why this is happening and how to best structure the query in order to prevent this?

Answer 1

The problem is with NULL comparison. If a.field1 or b.field3 is NULL you need to use a IS NULL or IS NOT NULL statement. You could use a default value for a.field1 and b.field3 with the ISNULL function.

ISNULL(a.field1,0) <> ISNULL(b.field3,0)

in this case there is a comparison with the value 0.

SELECT IIF(NULL=NULL,'true','false') -- The result is false. Amazing!