CODEWITHSUNDEEP
sql-server-2008t-sql
James A Mohler
James A Mohler

Reputation: 11120

Doing a join on null = null on dates

I want to join two tables on date fields

INNER JOIN ##MetricBase
ON  A.MetricID = ##MetricBase.MetricID
AND     A.ImportDate = ##MetricBase.ImportDate

I also want the data to match if the dates are null.

Is there a cleaner way than

 INNER JOIN ##MetricBase
 ON    A.MetricID = ##MetricBase.MetricID
 AND   (A.ImportDate = ##MetricBase.ImportDate OR
        A.ImportDate IS NULL AND  ##MetricBase.ImportDate IS NULL)

Addendum

Some comments suggested that I avoid null dates. This query is a part of a grouping set

 GROUP BY GROUPING SETS(
(BossID, ID, ImportDate),
(BossID, ImportDate),
(BossID),
(BossID, ID),
(ImportDate),
()
)

Nulls are going to be a part of the dataset

Upvotes: 4

Views: 109

Answers (2)

Aaronaught
Aaronaught

Reputation: 122684

There's an MS Connect Request currently out to support IS DISTINCT FROM, which is the proper, ISO way of doing it. The lack of support is currently a pain in the neck, and the way you're already doing it is in fact technically correct.

Somebody did figure out a clever workaround but it's not applicable to all scenarios. In your case I believe it would be written as:

INNER JOIN ##MetricBase
ON A.MetricID = ##MetricBase.MetricID
AND EXISTS(SELECT A.ImportDate INTERSECT SELECT ##MetricBase.ImportDate)

Supposedly, recent versions of SQL Server are pretty good at optimizing this.

Upvotes: 2

Joshua Honig
Joshua Honig

Reputation: 13235

Logically you cannot reduce that further, because for many other reasons null is not equal to anything, even itself.

A syntactic trick to keep in it in one predicate would be to use COALESCE with a 'magic' value. Without running tests I don't know what impact if any, this would have on performance. I believe your explicit statement of both conditions (equal OR both null) is clearer and more maintainable. But the for the sake of ideas, here's the COALESCE version:

DECLARE @nullDate date = '04-02-4242';

...
ON A.MetricID   = ##MetricBase.MetricID
AND COALESCE(A.ImportDate           , @nullDate) = 
    COALESCE(##MetricBase.ImportDate, @nullDate)

Upvotes: 1

Related Questions