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?

Null equals nothing, it is an undefined value which you cannot compare to anything(even if you use `<>`). That's why null value records are omitted. Therefore you have to use `IS NULL` or `IS NOT NULL` explicitely. — Tim Schmelter, Apr 10 '13 at 14:58
possible duplicate of [Not equal <> != operator in T-SQL on NULL](http://stackoverflow.com/questions/5658457/not-equal-operator-in-t-sql-on-null) — Cyril Gandon, Apr 10 '13 at 15:01
I ran into the "opposite" problem recently. Someone supplied a SQL statement to me involing a comparison like `WHERE val=null ...` and it *did actually work* on our sql-server equally to `WHERE val IS NULL`! It turned out, there is a setting `SET ANSI_NULLS OFF` that was activated on our server. This (deprecated!!) setting enables null comparisons, see [here](https://msdn.microsoft.com/en-gb/en-en/library/ms188048.aspx). — Carsten Massmann, Jul 12 '16 at 08:50

score 10 · Answer 1 · edited Feb 10 '22 at 01:02

10

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!

edited Feb 10 '22 at 01:02

Dale K

25,246
15
42
71

answered Apr 10 '13 at 15:00

Norberto108

167
1
9

1

I've never used this isnull...I will try it..thank you for the new tip – Heather Apr 10 '13 at 15:03
1

@Heather: [ISNULL function is designed for presentation](http://blogs.msdn.com/b/conor_cunningham_msft/archive/2010/12/18/conor-vs-more-sargable-predicates.aspx) (SELECT) and not for filtering (WHERE). – Bogdan Sahlean Apr 10 '13 at 15:06
@Norberto108: Please explain. Why " ISNULL function is designed for presentation" ? – Bogdan Sahlean Apr 10 '13 at 15:22
@BogdanSahlean I just wanted to understand your point of view. The link you posted has a concern on performance and execution plan to not suggest the ISNULL use in a where condition. I agree in terms, because the execution plan is a matter of further investigation like other joins, effective use of index and more. Maybe we could not simplify things with a "not for filtering" sentence. Best regards – Norberto108 Apr 10 '13 at 15:29
2

@Norberto108: If "it [seems] to work" then it doesn't means is a "[good] solution". And what if `a.field1 = 0 AND b.field3 IS NULL` ? – Bogdan Sahlean Apr 10 '13 at 15:34
2

@BogdanSahlean 0 MUST be an arbitrary and out of the scope value. The point is to keep using the <> operator. It could be -1, etc ... – Norberto108 Apr 10 '13 at 15:37
@Norberto108: Don't Take It Personal. The point is to give a [good] solution, not any solution that "works". – Bogdan Sahlean Apr 10 '13 at 15:43
1

@Norberto108: If for a row, the value of field1 is NULL and the value of field3 is 0 then the result of `ISNULL(a.field1,0) <> ISNULL(b.field3,0)` is 'false'. But the result of `a.field1 IS NULL AND b.field3 IS NOT NULL` will be 'true'. – Bogdan Sahlean Apr 10 '13 at 15:57
@BogdanSahlean I didn´t take :) But i answered you on previous comment. – Norberto108 Apr 10 '13 at 17:30
1

@BogdanSahlean I agree with Norberto here. ISNULL is just a function, you can use it anywhere you want provided you understand the risks and caveats of using functions. I often use this pattern for quick on-the-fly queries in order to avoid spaghetti-like query like the one the op came up with as solution. – Vland Sep 03 '14 at 12:32

Aleksandr Fedorenko · Answer 2 · 2013-04-10T15:22:15.967

9

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
              )

edited Apr 10 '13 at 15:22

answered Apr 10 '13 at 15:07

Aleksandr Fedorenko

16,594
6
37
44

2

Far and away the best answer, as documented [here](http://sqlblog.com/blogs/paul_white/archive/2011/06/21/undocumented-query-plans-equality-comparisons.aspx). The `ISNULL()` option works great, until it doesn't. – AHiggins Sep 15 '16 at 13:22

score 2 · Answer 3 · answered Apr 10 '13 at 14:58

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

Thank you...I left out the parens in the example, but they are in my code...I have edited to be more clear — Heather, Apr 10 '13 at 15:04

score 1 · Answer 4 · answered Apr 10 '13 at 15:00

1

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.

answered Apr 10 '13 at 15:00

Rachcha

8,486
8
48
70

So from what I'm reading, ifnull almost needs to be in any comparison query? – Heather Apr 10 '13 at 15:05
2

@Rachcha - Small correction: if either (or both) a.field1 or b.field3 are NULL, the conditions a.field1 <> b.field3 and a.field1 = b.field3 will not return FALSE, but rather NULL. It will behave like FALSE in most cases (like this one) because it is not TRUE, but it's still not FALSE and i.e. can't be negated back to TRUE. If you put NOT in front (a.field1 = b.field3) it will still be NULL and behave like FALSE. – Nenad Zivkovic Apr 10 '13 at 15:19
This was a very helpful comment. Looks like you have played with a lot of nulls! – Rachcha Apr 10 '13 at 19:02

score 1 · Answer 5 · edited Feb 10 '22 at 01:03

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.

score 0 · Answer 6 · answered Apr 10 '13 at 15:04

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.

QMaster · Answer 7 · 2014-12-01T23:08:17.470

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.

Captain O. · Answer 8 · 2016-03-17T22:58:04.273

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

Issues with SQL comparison and null values

8 Answers8

Linked

Related