NULL in JOIN

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...