CODEWITHSUNDEEP
sqlsql-servert-sqlnullequality
Ian R. O'Brien
Ian R. O'Brien

Reputation: 6920

What is the best way to equate NULL columns in SQL?

I am performing a MERGE between two tables

MERGE indexdecomp.Constituent targ
USING (SELECT ic.ConstituentName           
       FROM   indexdecomp.IndexConstituents ic) src
ON (((targ.Name = src.ConstituentName) OR (targ.Name IS NULL AND src.ConstituentName IS NULL)))
WHEN NOT MATCHED BY TARGET THEN 
UPDATE SET
    targ.Name = src.ConstituentName
;

and in my ON clause I have the following predicate:

(targ.Name = src.ConstituentName) OR (targ.Name IS NULL AND src.ConstituentName IS NULL)

I have this predicate since I am considering it a match if both Names are equal or if both names are `null.

Is there a better or more conventional way to handle equality between two null columns? What way would produce the quickest execution?

Upvotes: 3

Views: 667

Answers (2)

Rich Andrews
Rich Andrews

Reputation: 4188

You could try..

ISNULL (targ. Name,'a magic string value') =ISNULL (src.ConstituentName,'a magic string value')

Of course add your own magic string as appropriate for example use newid () to get a guid and use that.

Not really sure if this is "better" than an and or but is a little more human readable; worth benchmarking and testing both approaches though.

Upvotes: 2

Paul Sasik
Paul Sasik

Reputation: 81429

You can do something like this: (SQL ref)

SET ANSI_NULLS OFF;

MERGE indexdecomp.Constituent targ
USING (SELECT ic.ConstituentName           
   FROM #IndexConstituents ic) src
ON (((targ.Name = src.ConstituentName)))
WHEN NOT MATCHED BY TARGET THEN 
UPDATE SET
    targ.Name = src.ConstituentName;

SET ANSI_NULLS ON;

But that seems to be a pretty heavy trade off for lumping off a predicate and neither is very readable. You could actually abstract this mess with a UDF that takes two string arguments and returns a boolean.

Something like:

create function StrNullCompare(@a varchar(max), @b varchar(max))
returns int
as
begin
    if ((@a = @b) or (@a is null and @b is null)) return 1;

    return 0;
end

-- tests
select dbo.StrNullCompare('wer', 'were');
select dbo.StrNullCompare('wer', 'wer');
select dbo.StrNullCompare('hi', null);
select dbo.StrNullCompare(null, null);

And your predicate becomes:

(dbo.StrNullCompare(targ.Name, src.ConstituentName)=1)

Upvotes: 3

Related Questions