[sql] 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!

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.

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)

you can just map like that

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

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

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

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.

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