SQL Server JOIN missing NULL values

Suppose I had the following 2 tables:

      Table1:                                Table2:
Col1:      Col2:     Col3:             Col1:       Col2:       Col4:
a          b         c                 a           b           d
e          <null>    f                 e           <null>      g
h          i         j                 h           i           k
l          <null>    m                 l           <null>      n
o          <null>    p                 o           <null>      q

Now, I want to join these tables on Col1 and Col2 and bring back the entire set to look like:

     Result:
Col1:      Col2:     Col3:     Col4:
a          b         c         d
e          <null>    f         g
h          i         j         k
l          <null>    m         n
o          <null>    p         q

So, I tried a SQL like:

SELECT Table1.Col1, Table1.Col2, Table1.Col3, Table2.Col4
FROM Table1 INNER JOIN Table2
ON Table1.Col1 = Table2.Col1 
AND Table1.Col2 = Table2.Col2

But it isn't matching the NULL values in Col2, so I end up with:

     Result:
Col1:      Col2:     Col3:     Col4:
a          b         c         d
h          i         j         k

How can I get the result I am looking for??

Thanks!

You can be explicit about the joins:

SELECT Table1.Col1, Table1.Col2, Table1.Col3, Table2.Col4
FROM Table1 INNER JOIN
     Table2
      ON (Table1.Col1 = Table2.Col1 or Table1.Col1 is NULL and Table2.Col1 is NULL) AND
         (Table1.Col2 = Table2.Col2 or Table1.Col2 is NULL and Table2.Col2 is NULL)

In practice, I would be more likely to use coalesce() in the join condition:

SELECT Table1.Col1, Table1.Col2, Table1.Col3, Table2.Col4
FROM Table1 INNER JOIN
     Table2
     ON (coalesce(Table1.Col1, '') = coalesce(Table2.Col1, '')) AND
        (coalesce(Table1.Col2, '') = coalesce(Table2.Col2, ''))

Where '' would be a value not in either of the tables.

Just a word of caution. In most databases, using any of these constructs prevents the use of indexes.

Use Left Outer Join instead of Inner Join to include rows with NULLS.

SELECT Table1.Col1, Table1.Col2, Table1.Col3, Table2.Col4
FROM Table1 LEFT OUTER JOIN 
    Table2 ON Table1.Col1 = Table2.Col1 
    AND Table1.Col2 = Table2.Col2

For more information, see here: http://technet.microsoft.com/en-us/library/ms190409(v=sql.105).aspx

Try using ISNULL function:

SELECT Table1.Col1, Table1.Col2, Table1.Col3, Table2.Col4
FROM Table1 
INNER JOIN Table2
   ON Table1.Col1 = Table2.Col1 
   AND ISNULL(Table1.Col2, 'ZZZZ') = ISNULL(Table2.Col2,'ZZZZ')

Where 'ZZZZ' is some arbitrary value never in the table.

Dirty and quick hack:

SELECT Table1.Col1, Table1.Col2, Table1.Col3, Table2.Col4
FROM Table1 INNER JOIN Table2 ON Table1.Col1 = Table2.Col1
 AND ((Table1.Col2 = Table2.Col2) OR (Table1.Col2 IS NULL AND Table2.Col2 IS NULL))

you can just map like that

select * from tableA a
join tableB b on isnull(a.colID,'') = isnull(b.colId,'')

for some reason I couldn't get it to work with the outer join.

So I used:

SELECT * from t1 where not Id in (SELECT DISTINCT t2.id from t2)

Try using additional condition in join:

SELECT Table1.Col1, Table1.Col2, Table1.Col3, Table2.Col4
FROM Table1 
INNER JOIN Table2
ON (Table1.Col1 = Table2.Col1 
    OR (Table1.Col1 IS NULL AND Table2.Col1 IS NULL)
   )

The only correct answer is not to join columns with null values. This can lead to unwanted behaviour very quickly.

e.g. isnull(b.colId,''): What happens if you have empty strings in your data? The join maybe duplicate rows which I guess is not intended in this case.

declare @Table1 as Table(Col1 varchar(1),Col2 varchar(1), Col3 varchar(1))
declare @Table2 as Table(Col1 varchar(1),Col2 varchar(1), Col4 varchar(1))
insert into @Table1
values('a',          'b',     'c'),
('e',          null,    'f'), 
('h',          'i'  ,   'j'), 
('l',          null  ,  'm'), 
('o',          null  ,  'p') 
insert into @Table2
values('a',          'b',     'd'),
('e',          null,    'g'), 
('h',          'i'  ,   'k'), 
('l',          null  ,  'n'), 
('o',          null  ,  'q') 


select * from @Table1 tbl1
join @Table2 tbl2
on tbl1.Col1 =tbl2.Col1
and isnull(tbl1.Col2,0) =isnull(tbl2.Col2,0)

output:

 Col1	Col2	Col3	Col1	Col2	Col4
 a   	b	    c    	a	    b	    d
 e	    NULL	f	    e	    NULL	g
 h	    i	    j	    h	    i	    k
 l	    NULL	m	    l	    NULL	n
 o	    NULL	p	    o	    NULL	q

Some SQL implementations have a special Null-safe equal operator.

For instance Snowflake has EQUAL_NULL so ou can do

SELECT 
  Table1.Col1, Table1.Col2, Table1.Col3, Table2.Col4 
FROM 
  Table1 
  INNER JOIN Table2 ON EQUAL_NULL(Table1.Col1, Table2.Col1) 
  AND EQUAL_NULL(Table1.Col2, Table2.Col2)