Nulls and the MERGE statement: I need to set a value to infinity. How?

Question

In SQL Server with a MERGE code, everything is fine except when there are 2 nullable columns.

If I pass a null value and the target isn't null, MERGE doesn't see a difference (evals against null = false). If I use IsNull on both sides (source & target) that works, but has the issue of potentially mis-evaluating a value.

What I mean by the last statement is, if I say:

WHEN MATCHED AND NOT (IsNull(tgt.C, 0) = IsNull(src.C, 0)) THEN

then if tgt.C is null and src.C = 0, no update will be performed. No matter what substitute value I choose, I'll have this problem.

I also tried the "AND NOT (...true...)" syntax since BOL states that evaluations against null result in FALSE. However, it seems they actually result in NULL and do not result in my multi-part statement becoming false.

I thought one solution is to use NaN or -INF or +INF since these are not valid in target. But I can't find a way to express this in the SQL.

Any ideas how to solve this?

EDIT:

The following logic solves the problem, but it's verbose and won't make for fast evals:

declare @i int, @j int

set @j = 0
set @i = 0

if ISNULL(@i, 0) != ISNULL(@j, 0) OR 
    ((@i is null or @j is null) and not (@i is null and @j is null))
    print 'update';

I would like to ask anyone who reads this to upvote this missing ISO feature of MSSQL: https://connect.microsoft.com/SQLServer/feedback/details/286422/add-language-and-optimizer-support-for-iso-distinct-predicate — DaFi4, Jul 12 '16 at 13:24
I know this is old, but the MERGE statement is not as old as the statements in your nucleotides. Just leaving this here: https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/ — Reversed Engineer, Apr 29 '19 at 07:52

Martin Smith · Accepted Answer · 2023-07-12T21:01:08.677

27

In SQL Server 2022 you can use

WHEN MATCHED AND tgt.C IS DISTINCT FROM src.C

In previous versions you can use

WHEN MATCHED AND EXISTS (SELECT tgt.C EXCEPT SELECT src.C)

The second version can still be more compact if you need to do this check across multiple columns.

WHEN MATCHED AND EXISTS (SELECT tgt.A, tgt.B, tgt.C 
                         EXCEPT 
                         SELECT src.A, src.B, src.C)

See this article for more on this issue.

edited Jul 12 '23 at 21:01

answered Jan 20 '12 at 22:04

Martin Smith

438,706
87
741
845

2

This is really a great approach. It actually LOOKS good in my code. Reads something like "when matched and a change exists in these fields of source vs target data" – OzrenTkalcecKrznaric May 08 '15 at 09:28
1

EXCEPT and INTERSECT documentation: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-except-and-intersect-transact-sql – yzorg Nov 03 '17 at 19:29

Anthony K · Answer 2 · 2017-03-06T05:37:28.343

18

You can change the ON part of the merge statement, putting in a check for when both source and target are null.

MERGE tgt
USING src
ON ( -- enter non-nullable columns to match on ...
    tgt.A = src.A
    AND (tgt.C = src.C OR (tgt.C IS NULL AND src.C IS NULL))
)
WHEN MATCHED -- ...

edited Mar 06 '17 at 05:37

answered Jul 11 '12 at 23:57

Anthony K

2,543
4
32
41

score 6 · Answer 3 · edited Mar 02 '17 at 13:15

6

Actually, this works better. Just add another substitution value as an OR :-

WHEN MATCHED AND 
    ( 
    NOT (IsNull(tgt.C, 0) = IsNull(src.C, 0)) OR NOT (IsNull(tgt.C, 1) = IsNull(src.C, 1)) 
    ) 
THEN ....

edited Mar 02 '17 at 13:15

EvilDr

8,943
14
73
133

answered Dec 23 '10 at 13:49

Dawn

193
2
9

Brilliant! Very obvious now that I see it. – IamIC Dec 23 '10 at 14:13
I've added this the booklet I'm compiling on things to know about SQL Server. :) – IamIC Dec 23 '10 at 14:15
Fab, I'm new to this site, so I'm glad that you're the first person I've helped. :) :) Do let me know when the booklet is finished. There's huge gaps in my knowledge....! – Dawn Dec 23 '10 at 14:21
This worked until the nullable column is a uniqueidentifier – Rod Talingting Jan 24 '22 at 13:31

Quassnoi · Answer 4 · 2010-12-22T14:38:40.673

2

WHEN MATCHED AND tgt.c <> src.c OR tgt.c IS NULL AND src.c IS NOT NULL OR tgt.c IS NOT NULL AND src.c IS NULL

edited Dec 22 '10 at 14:38

answered Dec 22 '10 at 13:57

Quassnoi

413,100
91
616
614

That will perform a needless update if both src & tgt are null. – IamIC Dec 22 '10 at 14:00
Isn't there a way to say INF? – IamIC Dec 22 '10 at 14:02
"WHEN MATCHED AND tgt.c <> src.c OR ((tgt.c IS NULL OR src.c IS NULL) AND NOT (tgt.c IS NULL AND src.c IS NULL))" works, but what a mess. – IamIC Dec 22 '10 at 14:06
1

@IanC: yes it is a mess. `PostgreSQL` supports `IS NOT DISTINCT FROM` and `MySQL` supports `NOT <=>` (both of which treat `NULL` as distinct comparable values), but `SQL Server` has nothing like that. – Quassnoi Dec 22 '10 at 14:08
@IanC: `SQL Server` does not support `Inf`. You can pick any other value that is out of your domain; but if you don't have one, you will need to use the ternary logic (the `NULL` mess). – Quassnoi Dec 22 '10 at 14:09
Well, one can tell SQL Server to do this by turning ANSI NULLS off, but that breaks SQL-92 compliance and the ability is being deprecated. Doing so makes it behave the same as PostgreSQL & MySQL. It's impossible to say infinity?? Crazy. – IamIC Dec 22 '10 at 14:14
@Quassnoi I have decided rather to alter the domain and disallow the nulls. – IamIC Dec 22 '10 at 14:16

score 2 · Answer 5 · answered Dec 22 '10 at 13:57

2

Have you tried SET ANSI_NULLS OFF, which will make NULL=NULL return true? This may create additional issues but it could be a script-level workaround (turn it off then on once you run your proc).

answered Dec 22 '10 at 13:57

JNK

63,321
15
122
138

2

BOL advises that ANSI NULLS are being deprecated. – IamIC Dec 22 '10 at 13:58

score 2 · Answer 6 · edited Aug 03 '12 at 05:10

2

This works as well and may be better when you have multiple columns that you want to check if they are different.

  MERGE @t2 a

  using @t1 b

  ON a.PK = b.PK

  WHEN MATCHED AND CHECKSUM(a.PK,a.VALUE)!= CHECKSUM(b.pk,b.VALUE)

  THEN UPDATE SET a.VALUE = b.VALUE;

edited Aug 03 '12 at 05:10

Jon Crowell

21,695
14
89
110

answered Jan 20 '12 at 20:10

IrmaT

21
1

score 1 · Answer 7 · answered Apr 18 '19 at 13:48

1

You can check for null in the ON Clause:

MERGE TargetTable
USING (VALUES (0)) as s(x)
ON last_run is not null
WHEN not matched then
insert (last_run) values(getdate())
when matched then
update set last_run=getDate();

answered Apr 18 '19 at 13:48

Pilso

61
3

score 0 · Answer 8 · answered Dec 22 '10 at 15:27

0

Instead of using 0 when the values are null, why not use a value that is highly unlikely to exist? EG (IsNull(tgt.C, 2093128301).

The datatypes are int so you have a lot to play with......

answered Dec 22 '10 at 15:27

Dawn

193
2
9

I thought of that... the problem is almost any number could be in the domain. Even if I did -0.0000000001... that still has some element of risk. – IamIC Dec 22 '10 at 18:11
In that case, use the value you substitute in the clause too, eg :- – Dawn Dec 23 '10 at 13:20
WHEN MATCHED AND (NOT (IsNull(tgt.C, 123456789) = IsNull(src.C, 123456789)) OR (tgt.C = 123456789 AND src.C ! = 123456789) OR (tgt.C ! = 123456789 AND src.C = 123456789) THEN or something similar or more elegant, as this is getting messy too.... :( – Dawn Dec 23 '10 at 13:31

score -1 · Answer 9 · edited Jun 27 '14 at 21:14

-1

WHEN MATCHED AND
(
   NULLIF(tgt.C, src.C) IS NOT NULL OR NULLIF(src.C, tgt.C) IS NOT NULL
)
THEN

edited Jun 27 '14 at 21:14

Igor

33,276
14
79
112

answered Jun 27 '14 at 20:42

user1674957

21
2

Nulls and the MERGE statement: I need to set a value to infinity. How?

9 Answers9

Linked