由于多个Null而导致连接出现问题 [英] Problem with a join due to multiple Nulls
问题描述
我想通过一个唯一的ID字段加入2个表，但是ID字段也有多个NULLS的多个NULLS，我不想忽视它，我担心它们会导致
重复。
使用下面的TableA和TableB，我将演示问题。
TableA
TableA。 ID Field1 Field2
1 Paul 1
Null John 1
2 John 1
TableB
TableB.ID Field3 Field4
1 25
Null 32 1
Null 23 1
2 26 1
我想要的表是
TableA.ID TableB.ID Field1 Field2 Field3 Field4
1 1 Paul 1 25 1
2 2 John 1 26 1
Null Null John 1 Null Null
Null Null Null Null 32 1
Null Null Null Null 26 1
我想选择一个完整的外部联接的distinct语句可能会做什么
我想要，但我不确定所以想检查。
问候，
$ b $bCiarán
I want to join 2 tables by a unique ID field, but the ID field also has
multiple NULLS which I do not want to ignore and I fear they will cause
duplication.
Using TableA and TableB below i will demonstrate the problem.
TableA
TableA.ID Field1 Field2
1 Paul 1
Null John 1
2 John 1
TableB
TableB.ID Field3 Field4
1 25 1
Null 32 1
Null 23 1
2 26 1
The Table I want is
TableA.ID TableB.ID Field1 Field2 Field3 Field4
1 1 Paul 1 25 1
2 2 John 1 26 1
Null Null John 1 Null Null
Null Null Null Null 32 1
Null Null Null Null 26 1
I think a select distcinct statement with a full outer join may do what
I want, but I''m not certain so want to check.
Regards,
Ciarán
推荐答案
chudson ... @ hotmail.com写道：
chudson...@hotmail.com wrote:
我想通过一个唯一的ID字段加入2个表，但ID字段也有多个NULLS，我不想忽视它，我担心它们会导致重复。
使用下面的TableA和TableB，我将演示这个问题。
TableA
TableA.ID Field1 Field2
1 Paul 1
Null John 1
2 John 1
TableB
TableB.ID Field3 Field4
1 25 1
Null 32 1
Null 23 1
2 26 1
我想要的表是
TableA.ID TableB.ID Field1 Field2 Field3 Field4
1 1 Paul 1 25 1
2 2 John1 26 1
Null Null John 1 Null Null
Null Null Null Null 32 1
Null Null Null Null N 1 1
我认为这是一个完整的选择外连接可以做我想要的，但我不确定所以想检查。
问候，
Ciarán
I want to join 2 tables by a unique ID field, but the ID field also has
multiple NULLS which I do not want to ignore and I fear they will cause
duplication.
Using TableA and TableB below i will demonstrate the problem.
TableA
TableA.ID Field1 Field2
1 Paul 1
Null John 1
2 John 1
TableB
TableB.ID Field3 Field4
1 25 1
Null 32 1
Null 23 1
2 26 1
The Table I want is
TableA.ID TableB.ID Field1 Field2 Field3 Field4
1 1 Paul 1 25 1
2 2 John 1 26 1
Null Null John 1 Null Null
Null Null Null Null 32 1
Null Null Null Null 26 1
I think a select distcinct statement with a full outer join may do what
I want, but I''m not certain so want to check.
Regards,
Ciarán
似乎TableA没有钥匙。还不清楚
TableB中的关键是什么。请发布DDL而不是表格草图否则
我们只需要猜测。
根据你发布的内容我会说你需要在尝试查询之前修复一些数据模型
问题（丢失密钥）。也许这就是你想要做的事情，但是你要求的输出
对你有什么帮助并不明显。
-
David Portas，SQL Server MVP
如果可能，请发布足够的代码来重现您的问题。
包含CREATE TABLE和INSERT语句通常会有所帮助。
说明您正在使用的SQL Server版本，并指定任何错误消息的内容
。
SQL Server联机丛书：
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
-
It appears that TableA doesn''t have a key. Not clear what the key is in
TableB either. Please post DDL rather than sketches of tables otherwise
we just have to guess.
Based on what you''ve posted I''d say you need to fix some data model
issues (missing keys) before you attempt your query. Maybe that''s what
you are trying to do but it isn''t obvious how your requested output
will help you.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--
TableA.ID和TableB.ID是相应的键，但问题是
它们包含多个Null
问候，
$ b $bCiarán
TableA.ID and TableB.ID are the respective keys, but the problem is
that they contain multiple Nulls
Regards,
Ciarán
（ch ******** @ hotmail .com）写道：
(ch********@hotmail.com) writes:
使用下面的TableA和TableB，我将演示这个问题。
TableA
TableA.ID Field1 Field2
1 Paul 1
Null John 1 2 John 1
TableB
TableB.ID Field3 Field4
1 25 1
Null 32 1
Null 23 1
2 26 1
我想要的表是
TableA.ID TableB.ID Field1 Field2 Field3 Field4
1 1 Paul 1 25 1
2 2 John 1 26 1
Null Null John 1 Null Null
Null Null Null Null 32 1
Null Null Null Null 26 1
I want to join 2 tables by a unique ID field, but the ID field also has
multiple NULLS which I do not want to ignore and I fear they will cause
duplication.
Using TableA and TableB below i will demonstrate the problem.
TableA
TableA.ID Field1 Field2
1 Paul 1
Null John 1
2 John 1
TableB
TableB.ID Field3 Field4
1 25 1
Null 32 1
Null 23 1
2 26 1
The Table I want is
TableA.ID TableB.ID Field1 Field2 Field3 Field4
1 1 Paul 1 25 1
2 2 John 1 26 1
Null Null John 1 Null Null
Null Null Null Null 32 1
Null Null Null Null 26 1
ID不能如果有NULL值，真的是键，如果有
是多，则更少iple NULL。
如果我猜测你在寻找什么，可能就是这样：
SELECT a。 ID，b.ID，a.Field1，a.Field2，b.Field3，b.Field4
FROM TableA a
JOIN TableB b ON a.ID = b。 ID
UNION ALL
SELECT NULL，NULL，a.Field1，a.Field2，NULL，NULL
FROM TableA a
WHERE a.ID为空
UNION ALL
SELECT NULL，NULL，NULL，NULL，b.Field3，b.Field4
FROM TableB b
WHERE b.ID IS NULL
-
Erland Sommarskog，SQL Server MVP， es **** @ sommarskog.se
SQL Server 2005联机丛书
http://www.microsoft.com/technet/ pro ... ads / books.mspx
SQL Server 2000联机丛书
http://www.microsoft.com/sql/prodinf...ons/books.mspx
The IDs cannot really be keys if there are NULL values, even less if there
are multiple NULL.
If I''m taking a guess of what you are looking for, this might be it:
SELECT a.ID, b.ID, a.Field1, a.Field2, b.Field3, b.Field4
FROM TableA a
JOIN TableB b ON a.ID = b.ID
UNION ALL
SELECT NULL, NULL, a.Field1, a.Field2, NULL, NULL
FROM TableA a
WHERE a.ID IS NULL
UNION ALL
SELECT NULL, NULL, NULL, NULL, b.Field3, b.Field4
FROM TableB b
WHERE b.ID IS NULL
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
这篇关于由于多个Null而导致连接出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
