Comparing NULL against strings in SQL Server

Question

MERGE  DestinationTable AS D 
USING  SourceTable AS S
ON D.Alternate_ID = S.ID
WHEN MATCHED AND
(
      D.ID <> S.ID
   OR D.col1 <> S.col1
   OR D.col2 <> S.col2
   OR D.col3 <> S.col3
   OR D.col4 <> S.col4
   OR D.col5 <> S.col5
   OR D.col6 <> S.col6
   OR D.col7 <> S.col7
   OR D.col8 <> S.col8
)

Hi all, i'am trying to update DestinationTable if any of the column values in SourceTable have changed using the Merge statement snippet above.

However, if i have a NULL value in the destination column and a string or bit value in the source, the comparison D.col8 <> S.col8 will return false because of the way SQL handles comparisons to NULL values. As a result DestinationTable is not updated with new values from SourceTable.

What is the better way to handle this issue. If D.Col8 is NULL and S.Col8 is has a string or bit value, i still want to return true for an expression like D.col8 <> S.col8

SO if i have a value of "Test" in S.Col8 and NULL in D.Col8, I want to update Destination column from NULL to "Test"

Is performing the update when no columns have actually changed a measurable cost that you need to eliminate? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Sep 29, 2014 at 7:15

Giannis Paraskevopoulos · Accepted Answer · 2014-09-29 10:04:43Z

3

MERGE  DestinationTable AS D 
USING  SourceTable AS S
ON D.Alternate_ID = S.ID
WHEN MATCHED AND
(
      D.ID <> S.ID
   OR (D.col1 IS NULL AND S.col1 IS NOT NULL) 
      OR (D.col1 IS NOT NULL AND S.col1 IS NULL) 
      OR D.col1 <> S.col1
   OR (D.col2 IS NULL AND S.col2 IS NOT NULL) 
      OR (D.col2 IS NOT NULL AND S.col2 IS NULL) 
      OR D.col2 <> S.col2 
   OR (D.col3 IS NULL AND S.col3 IS NOT NULL) 
      OR (D.col3 IS NOT NULL AND S.col3 IS NULL) 
      OR D.col3 <> S.col3 
   OR (D.col4 IS NULL AND S.col4 IS NOT NULL) 
      OR (D.col4 IS NOT NULL AND S.col4 IS NULL) 
      OR D.col4 <> S.col4 
   OR (D.col5 IS NULL AND S.col5 IS NOT NULL) 
      OR (D.col5 IS NOT NULL AND S.col5 IS NULL) 
      OR D.col5 <> S.col5 
   OR (D.col6 IS NULL AND S.col6 IS NOT NULL) 
      OR (D.col6 IS NOT NULL AND S.col6 IS NULL) 
      OR D.col6 <> S.col6 
   OR (D.col7 IS NULL AND S.col7 IS NOT NULL) 
      OR (D.col7 IS NOT NULL AND S.col7 IS NULL) 
      OR D.col7 <> S.col7 
   OR (D.col8 IS NULL AND S.col8 IS NOT NULL) 
      OR (D.col8 IS NOT NULL AND S.col8 IS NULL) 
      OR D.col8 <> S.col8 
)

edited Sep 29, 2014 at 10:04

answered Sep 29, 2014 at 7:17

Giannis Paraskevopoulos

18.5k2 gold badges52 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Paolo Over a year ago

this solution will not handle the case of null in source table

StackTrace Over a year ago

Marked this as answer because ISNULL(D.col1, '') <> S.col1 fails if column data type is uniqueidentifier.

Giannis Paraskevopoulos Over a year ago

@Paolo SO stated that it is possible the Destination table to have NULL, and not the Source. Why check for something that it is not possible?

Paolo Over a year ago

@GiannisParaskevopoulos if there are null in the destination and the destination is kept in sync with the source the null have to arrive from the source. you are right, the OP didn't mention that case explicitly but imho is an important issue to point out.

Giannis Paraskevopoulos Over a year ago

@Paolo Can't say i argue with your reasoning. I will update my answer.

|

Remus Rusanu · Accepted Answer · 2014-09-29 10:43:01Z

1

You could do it a lot simpler with BINARY_CHECKSUM:

MERGE  DestinationTable AS D 
USING  SourceTable AS S
ON D.Alternate_ID = S.ID
WHEN MATCHED AND 
 BINARY_CHECKSUM(d.col1, d.col2, ...,d.col8) <> BINARY_CHECKSUM(s.col1, ..., s.col8)

There is a small collision probability of having false negatives (values did change, but checksum is the same), but is negligible.

answered Sep 29, 2014 at 10:43

Remus Rusanu

296k42 gold badges459 silver badges583 bronze badges

Comments

Ganesh Jadhav · Accepted Answer · 2014-09-29 07:17:14Z

0

Try this:

...
WHEN MATCHED AND
(
   ISNULL(D.ID, '') <> S.ID
   OR ISNULL(D.col1, '') <> S.col1
   OR ISNULL(D.col2, '') <> S.col2
   OR ISNULL(D.col3, '') <> S.col3
   OR ISNULL(D.col4, '') <> S.col4
   OR ISNULL(D.col5, '') <> S.col5
   OR ISNULL(D.col6, '') <> S.col6
   OR ISNULL(D.col7, '') <> S.col7
   OR ISNULL(D.col8, '') <> S.col8
)

answered Sep 29, 2014 at 7:17

Ganesh Jadhav

2,8481 gold badge22 silver badges34 bronze badges

Comments

adrianm · Accepted Answer · 2014-09-29 09:45:07Z

0

INTERSECT and EXCEPT handles NULL the way you want. (i.e. NULL = NULL)

...
WHEN MATCHED 
     AND EXISTS (SELECT D.Col1, D.Col2, ...
                 EXCEPT S.Col1, S.Col2, ...)

answered Sep 29, 2014 at 9:45

adrianm

14.8k6 gold badges60 silver badges110 bronze badges

Comments

Rajesh · Accepted Answer · 2014-09-29 10:27:23Z

0

Try Something like

MERGE  DestinationTable AS D 
USING  SourceTable AS S ON D.Alternate_ID = S.ID
WHEN MATCHED AND
(
   ISNULL(D.ID, '') <> ISNULL(S.ID,'')
   OR ISNULL(D.col1, '') <> ISNULL(S.col1,'')
   OR ISNULL(D.col2, '') <> ISNULL(S.col2,'')
   OR ISNULL(D.col3, '') <> ISNULL(S.col3,'')
   OR ISNULL(D.col4, '') <> ISNULL(S.col4,'')
   OR ISNULL(D.col5, '') <> ISNULL(S.col5,'')
   OR ISNULL(D.col6, '') <> ISNULL(S.col6,'')
   OR ISNULL(D.col7, '') <> ISNULL(S.col7,'')
   OR ISNULL(D.col8, '') <> ISNULL(S.col8,'')
)

D.ID or S.ID or D.colx or S.colx could also be Null

edited Sep 29, 2014 at 10:27

answered Sep 29, 2014 at 7:42

Rajesh

1,6305 gold badges36 silver badges63 bronze badges

Collectives™ on Stack Overflow

Comparing NULL against strings in SQL Server

5 Answers 5

6 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

6 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related