Comparing Null to Null in merge statement

Question

Which statement is perfect or better when dealing with billion of records for comparing NULL's in merge statement. I have tried with SET ANSI_NULLS OFF but that didn't work in merge statement. Here is my two ways

ISNULL(SRCColumn,-11111) = ISNULL(DSTColumn, -11111)

Or

SRCColumn = DSTColumn OR (SRCColumn  IS NULL AND DSTColumn  IS NULL)

Please let me know if there is any better way to deal with it. As I have around 15 columns to compare.

this link here is supposed to be the ISO way to do it, but its not supported: please vote for this https://connect.microsoft.com/SQLServer/feedback/details/286422/add-language-and-optimizer-support-for-iso-distinct-predicate — DaFi4, Jul 12 '16 at 13:22

score 5 · Accepted Answer · answered Mar 18 '13 at 02:01

5

SRCColumn = DSTColumn OR (SRCColumn IS NULL AND DSTColumn IS NULL)

I'd suggest that you use this version because it most accurately expresses what you want SQL Server to do.

Both statements are logically equivalent (unless -11111 is a legal value for the column), however this statement is much more recognizable, and there's probably only a negligible difference in the run-time performance of the two statements.

answered Mar 18 '13 at 02:01

Ryan

2,948
3
30
41

I used the same statement. Though, thanks for posting the answer. I believe it would be helpful for others also. – Zerotoinfinity Mar 18 '13 at 19:51

score 2 · Answer 2 · answered Jul 14 '14 at 14:17

If you are more concerned with succinctness than performance, CHECKSUM() is also an option. It will match NULL -> NULL:

MERGE A
USING B
ON A.Key = B.Key
WHEN MATCHED AND CHECKSUM(A.Col1, A.Col2, ... ) <> CHECKSUM(B.Col1, B.Col2, ... )
THEN UPDATE SET Col1 = B.Col1, Col1 = B.Col2, ...

score 0 · Answer 3 · answered Jun 10 '19 at 01:37

How about using the NOT comparison on matching:

MERGE [TGT]
USING [SRC]
ON [SRC].Key = [TGT]. Key
…

WHEN MATCHED AND
(
NOT ([TGT].[dw_patient_key] = [SRC].[dw_patient_key] OR ([TGT].[dw_patient_key] IS NULL AND [SRC].[dw_patient_key] IS NULL))
OR NOT ([TGT].[dw_patient_key] = [SRC].[dw_patient_key] OR ([TGT].[dw_patient_key] IS NULL AND [SRC].[dw_patient_key] IS NULL))
...
)
THEN UPDATE
...

Comparing Null to Null in merge statement

3 Answers3