How to handle the null in merge SQL's WHEN MATCHED?

Question

MERGE TARGET T
USING SOURCE S
ON T.ID=S.ID

WHEN MATCHED AND (S.NAME<>T.NAME OR S.DOB<>T.DOB)
THEN UPDATE
SET T.NAME=S.NAME, T.DOB=S.DOB;

The above is not able to handle situation where name/dob is null on source or target side. Because comparisons with NULL return false. How to handle this?

Example:

COALESCE(S.NAME,'')<>COALESCE(T.NAME,'')

Or

COALESCE(S.NAME,0)<>COALESCE(T.NAME,0)

Or

NULLIF(S.NAME,'')<>NULLIF(T.NAME,'')

You appear to have answered your own question? What do you actually want to know? — Dale K, Mar 29 '22 at 07:51
which of the approaches is good? Or for example is it better to write `COALESCE(S.NAME,0)` instead of `COALESCE(S.NAME,'')`? — variable, Mar 29 '22 at 07:51
Don't all almost do the same thing? I'm really confused which 1 to choose so looking for some guidance here please. — variable, Mar 29 '22 at 07:53
Use the first or second. The last one is wrong. Depending on the data type, if it is string type, `COALESCE(col, '')` or numeric then `COALESCE(col,0)` — Squirrel, Mar 29 '22 at 07:57
@variable Relying on `'' == 1901-01-01` is bad practice, imo - and anyway: `1901-01-01` is not the same thing as a `NULL` value in a `date` column. — Dai, Mar 29 '22 at 08:21
@variable I already have, **in my answer (posted 30 minutes ago)**. My answer works for all data-types and `NULL`-ability. Is there any good reason for your seeming _allergy_ to using `EXCEPT`? — Dai, Mar 29 '22 at 08:23
Obligatory: [So, you want to use MERGE, eh?](https://sqlblog.org/merge) — Aaron Bertrand, Mar 29 '22 at 12:32

Dai · Answer 1 · 2022-03-29T08:09:42.077

Use EXISTS( SELECT src.* EXCEPT SELECT tgt.* ) to detect if the source row differs from the target row in MERGE INTO statements.
In T-SQL, EXCEPT does sucuessfully compare NULL values, and NULL with NOT NULL values, whereas (as you've noticed) the = and <> operators do not.
- Another alternative is to use IS DISTINCT FROM for each column, however SQL Server does not (yet) support this feature.
Don't forget to also specify WITH HOLDLOCK. It's very important with MERGE.

Like so:

MERGE INTO destTable WITH (HOLDLOCK) AS tgt
USING srcRelation AS src ON
    /* Only specify PK columns here: */
    tgt.Pk1 = src.Pk1
    AND
    tgt.Pk2 = src.Pk2

WHEN MATCHED AND EXISTS (

    /*
    Only specify "data" columns here; these are the columns which are also included in the UPDATE SET clause below.
    Don't include GENERATED, rowversion, PRIMARY KEY, and other "non-data" columns
    */

    SELECT tgt.Col1, tgt.Col2, tgt.Col3, etc
    EXCEPT
    SELECT src.Col1, src.Col2, src.Col3, etc
)
THEN UPDATE SET
    tgt.Col1 = src.Col1,
    tgt.Col2 = src.Col2,
    tgt.Col3 = src.Col3

WHEN NOT MATCHED BY TARGET THEN etc...;

Specifically, in your case:

MERGE INTO "target" WITH (HOLDLOCK) AS tgt
USING "source" AS src ON
    tgt."ID" = src."ID"
WHEN MATCHED AND EXISTS (
    SELECT tgt."Name"
    EXCEPT
    SELECT src."Name"
)
THEN UPDATE SET
    tgt."Name" = src."Name";

Does COALESCE not cover any scenario that is covered by your code example? — variable, Mar 29 '22 at 08:16
@variable As others have pointed out in their comment-replies already: **`COALESCE` is inappropriate in this case** because it doesn't indicate that `''` is distinct from `NULL`, not to mention introducing other ambiguities w.r.t. data-types. Point is: **you should not** use `COALSECE` (nor `ISNULL`) in a `MERGE ... WHEN MATCHED AND...` clause. - whereas `EXISTS ( SELECT src... EXCEPT SELECT tgt... )` is idiomatic in T-SQL and _works as-expected_. — Dai, Mar 29 '22 at 08:18
@variable and using `coalesce` can result in the query being unsargable i.e. unable to use indexes. Hence this is a better solution. (You did ask for the best way to do it :) ) — Dale K, Mar 29 '22 at 08:24

How to handle the null in merge SQL's WHEN MATCHED?

1 Answers1