Merge脚本中双向NULLIF列校验是否冗余？技术咨询

Great question—let’s unpack why those reverse NULLIF checks are there, and whether removing them would break your logic.

First, a quick recap of how NULLIF works: NULLIF(a, b) returns NULL if a equals b, otherwise it returns a. Crucially, NULL comparisons are tricky in SQL—NULL = anything evaluates to UNKNOWN, not true or false, which is where these dual checks come into play.

Let’s break down the critical scenario the reverse checks are solving. Suppose:

• Source.[code] is NULL
• Target.[code] is 'XYZ'

Now evaluate each condition:

• NULLIF(Source.[code], Target.[code]) IS NOT NULL → NULLIF(NULL, 'XYZ') returns NULL, so this is FALSE
• NULLIF(Target.[code], Source.[code]) IS NOT NULL → NULLIF('XYZ', NULL) returns 'XYZ', so this is TRUE

Without that reverse check, your WHEN MATCHED condition wouldn’t trigger an update here. But your update logic sets [code] = Source.[code]—which means you want to overwrite Target.[code] (currently 'XYZ') with NULL in this scenario. The reverse check ensures that happens.

Let’s confirm all possible value pairs to be clear:

• Both values non-NULL and different: Either direction of NULLIF will return a non-NULL value, so the condition triggers. No problem here whether you have reverse checks or not.
• Source non-NULL, Target NULL: NULLIF(Source.[code], Target.[code]) returns the Source value (non-NULL), so the condition triggers. The reverse check here does nothing, but it doesn’t hurt.
• Source NULL, Target non-NULL: Only the reverse NULLIF(Target.[code], Source.[code]) will return a non-NULL value, triggering the update. This is the case that would break if you remove the reverse checks.

So, to answer your question directly: removing the reverse NULLIF checks will change the script’s behavior. It will stop updating Target fields to NULL when the corresponding Source field is NULL. If that’s not part of your intended logic (e.g., you never expect Source fields to be NULL, or you don’t want to overwrite non-NULL Target values with NULL), then removing them might seem safe—but the original script was explicitly designed to handle NULL-to-non-NULL differences.

内容的提问来源于stack exchange，提问作者J.L