SQL Server JOIN missing NULL values

Question

Suppose I had the following 2 tables         Table1                                 Table2  Col1       Col2      Col3              Col1        Col2        Col4  a          b         c                 a           b           d e           lt null gt     f                 e            lt null gt       g h          i         j                 h           i           k l           lt null gt     m                 l            lt null gt       n o           lt null gt     p                 o            lt null gt       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           lt null gt     f         g h          i         j         k l           lt null gt     m         n o           lt null gt     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

User · Answer

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

User · Answer

you can just map like that  select   from tableA a join tableB b on isnull a colID       isnull b colId

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] SQL Server JOIN missing NULL values

Examples related to sql

Examples related to sql-server