SQL Server JOIN отсутствует значения NULL

Предположим, что у меня были следующие 2 таблицы:

      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

Теперь я хочу присоединиться к этим таблицам на Col1 и Col2 и вернуть весь набор, чтобы он выглядел следующим образом:

     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

Итак, я попробовал SQL как:

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

Но это не соответствует значениям NULL в Col2, поэтому я получаю:

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

Как я могу получить результат, который я ищу?

Спасибо!

Ответ 1

Вы можете быть явно о соединениях:

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)

На практике я бы с большей вероятностью использовал coalesce() в условии соединения:

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

Где '' будет значением не в любой из таблиц.

Просто предостережение. В большинстве баз данных использование любой из этих конструкций предотвращает использование индексов.

Ответ 2

Используйте Left Outer Join вместо Inner Join, чтобы включить строки с 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

Для получения дополнительной информации см. здесь: http://technet.microsoft.com/en-us/library/ms190409(v=sql.105).aspx

Ответ 3

Попробуйте использовать функцию ISNULL:

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

Где 'ZZZZ' - какое-то произвольное значение никогда в таблице.

Ответ 4

Грязный и быстрый взлом:

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

Ответ 5

вы можете просто нарисовать это как

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

Ответ 6

по какой-то причине я не мог заставить его работать с внешним соединением.

Поэтому я использовал:

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