SQL Server JOIN missing NULL values

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