SQL Server JOIN 缺少 NULL 值 [英] SQL Server JOIN missing NULL values

问题描述

假设我有以下 2 个表:

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

现在，我想在 Col1 和 Col2 上加入这些表，并使整个集合看起来像:

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

所以，我尝试了一个 SQL:

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

但它与 Col2 中的 NULL 值不匹配，所以我最终得到:

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

谢谢！

推荐答案

您可以明确说明连接:

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

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 Server JOIN 缺少 NULL 值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！