Problem: Join 2 tables, but there are some fit cases with NULL value – and they are not return.
Fast fix: ISNULL in the JOIN.
But, let's take a look to pay attention on this.
create table #aaa (id int, thedate datetime)
create table #bbb (id int, thedate datetime)
insert into #aaa (id, thedate) values (1, '2010-01-01'), (2, NULL), (3, getdate())
insert into #bbb (id, thedate) values (1, '2005-07-01'), (2, NULL), (3, getdate())
-- "equel" nulls will not be return
select *
from #aaa a
join #bbb b on a.id = b.id
and b.thedate = a.thedate
-- only "equel" nulls will be return
select *
from #aaa a
join #bbb b on a.id = b.id
where b.thedate is null
and a.thedate is null
-- all joins will be return
select *
from #aaa a
join #bbb b on a.id = b.id
and isnull(b.thedate, '1900-01-01') = isnull(a.thedate, '1900-01-01')
-- PAY ATTENTION: if the "isnull data" is a possible value in your tables - it can be a problem:
insert into #aaa (id, thedate) values (4, '1900-01-01')
insert into #bbb (id, thedate) values (4, NULL)
select *
from #aaa a
join #bbb b on a.id = b.id
and isnull(b.thedate, '1900-01-01') = isnull(a.thedate, '1900-01-01')
drop table #aaa
drop table #bbb
So:
ISNULL(AAA, GETDATE) can't be a 100% solution to join on datetime,
ISNULL on numeric columns MUST take into consideration which value will be in the ISNULL (common mistake is to put zero – it's a valid numeric value!!!),
ISNULL(AAA, N'') can't be a 100% solution to join on strings (empty string it's a valid numeric value!!!),
And so on...
