SQL Server JOIN 缺少 NULL 值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14366004/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server JOIN missing NULL values
提问by John Bustos
Suppose I had the following 2 tables:
假设我有以下 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
Now, I want to join these tables on Col1and Col2and bring back the entire set to look like:
现在，我想加入这些表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
So, I tried a SQL like:
所以，我尝试了一个 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
But it isn't matching the NULLvalues in Col2, so I end up with:
但它与 中的NULL值不匹配Col2，所以我最终得到：
Result:
Col1: Col2: Col3: Col4:
a b c d
h i j k
How can I get the result I am looking for??
我怎样才能得到我正在寻找的结果？？
Thanks!
谢谢！
回答by Gordon Linoff
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:
在实践中，我更有可能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, ''))
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.
只是提醒一下。在大多数数据库中，使用这些结构中的任何一个都会阻止使用索引。
回答by Dave Hackett
Use Left Outer Join instead of Inner Join to include rows with NULLS.
使用 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
For more information, see here: http://technet.microsoft.com/en-us/library/ms190409(v=sql.105).aspx
有关详细信息，请参见此处：http: //technet.microsoft.com/en-us/library/ms190409(v=sql.105).aspx
回答by sgeddes
Try using ISNULLfunction:
尝试使用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')
Where 'ZZZZ'is some arbitrary value never in the table.
'ZZZZ'表中从未出现过的任意值在哪里。
回答by jap1968
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))
回答by wiretext
you can just map like that
你可以这样映射
select * from tableA a
join tableB b on isnull(a.colID,'') = isnull(b.colId,'')
回答by user11333280
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)
回答by Danny Costin
Try using additional condition in join:
尝试在 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)
)
