Reputation: 11120
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
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
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