Which operator is used to compare the null values in SQL equal is is not both B and C?

<=>

SELECT (t1.a, t1.b) <=> (t2.x, t2.y) 
FROM t1 INNER JOIN t2;

SELECT (t1.a <=> t2.x) AND (t1.b <=> t2.y)
FROM t1 INNER JOIN t2;

SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
+---------+---------------+------------+
| 1 <=> 1 | NULL <=> NULL | 1 <=> NULL |
+---------+---------------+------------+
|       1 |             1 |          0 |
+---------+---------------+------------+

SELECT 1 = 1, NULL = NULL, 1 = NULL;
+-------+-------------+----------+
| 1 = 1 | NULL = NULL | 1 = NULL |
+-------+-------------+----------+
|     1 |        NULL |     NULL |
+-------+-------------+----------+

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?

asked Apr 10, 2013 at 14:52

HeatherHeather

1911 gold badge1 silver badge4 bronze badges

3

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!

Dale K

23.9k15 gold badges42 silver badges71 bronze badges

answered Apr 10, 2013 at 15:00

10

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
              )

answered Apr 10, 2013 at 15:07

1

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

answered Apr 10, 2013 at 14:58

DOKDOK

32.1k7 gold badges60 silver badges92 bronze badges

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, 2013 at 15:00

RachchaRachcha

8,3168 gold badges47 silver badges70 bronze badges

3

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.

Dale K

23.9k15 gold badges42 silver badges71 bronze badges

answered Apr 16, 2015 at 14:20

ChanochChanoch

5637 silver badges16 bronze badges

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.

answered Apr 10, 2013 at 15:04

0

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.

answered Dec 1, 2014 at 23:00

QMasterQMaster

3,6163 gold badges42 silver badges56 bronze badges

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

answered Mar 17, 2016 at 18:56

Captain O.Captain O.

3831 silver badge9 bronze badges

Which operator is used to compare the NULL value in SQL?

Can we compare 2 NULL values in SQL?

Which operator is used to compare the equal values in SQL?

Is != And same in SQL?