NULL on joined columns

 

You know that
NULL values on joined columns are omitted from comparision when tables are joined

Consider this example

Declare @t1 table(col1 int, col2 varchar(10))

insert into @t1
select 1, 'test1' union all
select 2, 'test2' union all
select NULL,'test3' union all
select 5,' test4'

Declare @t2 table(col1 int, col2 varchar(10))

insert into @t2
select 1, 'testing1' union all
select 2, 'testing2' union all
select NULL, 'testing3' union all
select 3, 'testing4'

select t1.col1,t2.col2 from @t1 as t1 inner join @t2 as t2 on t1.col1=t2.col1

The result is

col1       col2
----------- ----------
1           testing1
2           testing2


What
if you want to compare NULL values too and get the value testing3?

The query is

select t1.col1,t2.col2 from @t1 as t1 inner join @t2 as t2 on t1.col1=t2.col1 or (t1.col1 is null and t2.col1 is null)

and the result is

col1      col2
----------- ----------
1           testing1
2           testing2
NULL      testing3

Published Wednesday, December 24, 2008 10:24 AM by Madhivanan

Comments

No Comments