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!

Answer 2

The result of comparing anything to NULL, even itself, is always NULL(not TRUE or FALSE). Use option with EXISTS and EXCEPT operators.

UPDATE table1
SET a.field1 = b.field3
FROM table1 a JOIN table2 b ON a.field2 = b.field2
WHERE EXISTS (
              SELECT a.field1
              EXCEPT
              SELECT b.field3
              )

Answer 3

In addition to handling the NULL logic correctly, you need to enclose multiple conditions that are to be applied together in parentheses.

Something like this (not sure I understood your conditions exactly).

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)
            )

Answer 4

Tim Shmelter is right in his comment, NULL is not equal to anything- even including NULL. NULL literally means that the value is unknown.

This means, even if a.field1 and b.field3 both are NULL, the conditions a.field1 <> b.field3 as well as a.field1 = b.field3 both will always return false. Try it and you will see!

I think the solution here does not lie in the IFNULL function of SQL Server. It lies more in your joining logic. You already have your solution, i.e., the second query in your question. What I will recommend is you playing a bit more with NULL values so you can understand what really are they.

Answer 5

You can use coalesce in SQL Server to default the value of a column to a non-null value. Coalesce returns the first non-null value in the list.

UPDATE  table1
SET     a.field1 = b.field3
FROM    table1 a ,
        table2 b
WHERE   a.field2 = b.field2
        AND (
           coalesce(a.field1,-1) <> coalesce(b.field3, -1)
        )

I've assumed that your type is number, though you can use other data types. I've also assumed that if both values are NULL then the two rows are equivalent.

Answer 6

When you write in your query a.field1 = b.field3 you actually make two assumptions: field1 in table a must contain a value and field3 in your b table must also contain a value. It is not possible to compare a 'missing information and inapplicable information' to a value. The result of this comparison is unknown. You can have a look for further information on Wikipedia.

Answer 7

This will check if the Column1 and Column2 is equal, Additionally used Convertion to VARBINARY to compare in case sensitive and you can remove it if not necessary.

--c1 = Length of Column1
--c2 = Length of Column2

ISNULL(NULLIF(CONVERT(VARBINARY(cl), LTRIM(RTRIM(Column1))), CONVERT(VARBINARY(c2),LTRIM(RTRIM(Column2)))), NULLIF(CONVERT(VARBINARY(c2),LTRIM(RTRIM(Column2))), CONVERT(VARBINARY(c1),LTRIM(RTRIM(Column1))))) IS NULL

You can change the end of expression to IS NOT NULL for checking unequal condition.

Hope this help.

Answer 8

Another way would be to use CHECKSUM function

create table #temp
  (
    val1 varchar(255),
    val2 varchar(255)
  )

  insert into #temp values(NULL, NULL) 
  insert into #temp values(NULL, 'B') 
  insert into #temp values('A', NULL) 
  insert into #temp values('A', 'B') 
  insert into #temp values('A', 'A') 

  select *, 
  'Are Not Equal' = case 
   when val1 <> val2 or checksum(val1) <> checksum(val2) then 'true' 
   else 'false' end 
  from #temp