SQL Server - MERGE join condition with NULLable Columns

Question

I am trying to merge two of my tables (identical definition).

MERGE INTO xx.dbo.acc_s AS Target 
 USING yy.dbo.acc_s AS Source 
 ON (Target.acc_id= Source.acc_id AND Target.s_id= Source.s_id AND a_code= Source.a_code) 
WHEN NOT matched BY Target THEN
INSERT (acc_id,s_id,a_code)
VALUES (Source.acc_id,Source.s_id,Source.a_code);

What I expected is that "If Target doesn't have the row, INSERT it" - Nothing Else.

I got a "Violation of PRIMARY KEY" error on one of the rows. If it's a matching row BY TARGET, it shouldn't have attempted to insert it in the first place.

What I did after that was:

MERGE INTO xx.dbo.acc_s AS Target 
 USING yy.dbo.acc_s AS Source 
 ON (Target.acc_id= Source.acc_id AND Target.s_id= Source.s_id AND ISNULL(a_code, '')= ISNULL(Source.a_code, '') 
WHEN NOT matched BY Target THEN
INSERT (acc_id,s_id,a_code)
VALUES (Source.acc_id,Source.s_id,Source.a_code);

And it picked up correctly. From this, I am thinking that when I am merging, if I have two NULL columns joining - SQL Server cannot resolve this correctly. Have I encountered an expected behaviour here?

I know that I should probably rather use

WHEN matched THEN
UPDATE SET a_code = Source.a_code

But in that case I might have to change quite a lot of things as I am trying to find a general way to update other tables by creating a template query. However, I understand that it might not be practical.

Answer 1

Mark Sinkinson nailed it in the comments:

This is standard behaviour. NULL does not equal NULL

What you can do is adjust your predicate from this:

WHERE Destination.ColumnA = Source.ColumnA
    AND ...  -- other column comparisons

to the following:

WHERE ((Destination.ColumnA = Source.ColumnA)
       OR (Destination.ColumnA IS NULL AND Source.ColumnA IS NULL))
   AND ...  -- other column comparisons

This will provide a workaround that effectively equates a NULL in one column to a NULL in another.

So to work that into your Merge, it would be as follows:

MERGE INTO xx.dbo.acc_s AS Target 
 USING yy.dbo.acc_s AS Source 
 ON (Target.acc_id= Source.acc_id 
     AND Target.s_id= Source.s_id 
     AND (a_code= Source.a_code 
          OR (a_code IS NULL AND Source.a_code IS NULL)
         )
    ) 
WHEN NOT matched BY Target THEN
INSERT (acc_id,s_id,a_code)
VALUES (Source.acc_id,Source.s_id,Source.a_code);