SQL 比较和空值的问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15929269/
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
Issues with SQL comparison and null values
提问by Heather
I have an update query that updates a field in one table when the value does not match a field in another table.
我有一个更新查询，当值与另一个表中的字段不匹配时，它会更新一个表中的字段。
UPDATE table1
SET a.field1 = b.field3
FROM table1 a ,
table2 b
WHERE a.field2 = b.field2
AND a.field1 <> b.field3
The problem I am having is that it is not picking up when a.field1 is null and b.field3 is a value OR if a.field1 is a value and b.field3 is null.
我遇到的问题是，当 a.field1 为 null 且 b.field3 为值或 a.field1 为值且 b.field3 为 null 时，它不会启动。
I have gotten around this by adding the following...
我通过添加以下内容解决了这个问题......
UPDATE table1
SET a.field1 = b.field3
FROM table1 a ,
table2 b
WHERE a.field2 = b.field2
AND ( a.field1 <> b.field3
OR (a.field1 IS NOT NULL
AND b.field3 IS NULL)
OR (a.field1 IS NULL
AND b.field3 IS NOT NULL)
)
My question is more centered around why this is happening and how to best structure the query in order to prevent this?
我的问题更集中在为什么会发生这种情况以及如何最好地构建查询以防止这种情况发生？
回答by Norberto108
The problem is with NULL comparison. If a.field1 or b.field3 is NULL you need to use a IS NULL or IS NOT NULL statement. You could use a default value for a.field1 and b.field3 with the ISNULL function.
问题在于 NULL 比较。如果 a.field1 或 b.field3 为 NULL，则需要使用 IS NULL 或 IS NOT NULL 语句。您可以使用 ISNULL 函数为 a.field1 和 b.field3 使用默认值。
ISNULL(a.field1,0) <> ISNULL(b.field3,0)
in this case there is a comparison with the value 0.
在这种情况下，会与值 0 进行比较。
SELECT IIF(NULL=NULL,'true','false') -- The result is false. Amazing!
SELECT IIF(NULL=NULL,'true','false') -- 结果为假。惊人的！
回答by Aleksandr Fedorenko
The result of comparing anything to NULL, even itself, is always NULL(not TRUE or FALSE). Use option with EXISTS and EXCEPT operators.
将任何内容与 NULL 进行比较的结果，甚至其本身，始终为 NULL（不是 TRUE 或 FALSE）。将选项与 EXISTS 和 EXCEPT 运算符一起使用。
UPDATE table1
SET a.field1 = b.field3
FROM table1 a JOIN table2 b ON a.field2 = b.field2
WHERE EXISTS (
SELECT a.field1
EXCEPT
SELECT b.field3
)
回答by Chanoch
You can use coalesce in sql server to default the value of a column to a non-null value. Coalesce returns the first non-null value in the list.
您可以在 sql server 中使用 coalesce 将列的值默认为非空值。Coalesce 返回列表中的第一个非空值。
UPDATE table1
SET a.field1 = b.field3
FROM table1 a ,
table2 b
WHERE a.field2 = b.field2
AND (
coalesce(a.field1,-1) <> coalesce(b.field3, -1)
)
I've assumed that your type is number, though you can use other data types. I've also assumed that if both values are NULL then the two rows are equivalent.
我假设您的类型是数字，但您可以使用其他数据类型。我还假设如果两个值都是 NULL，那么这两行是等价的。
回答by DOK
In addition to handling the NULL logic correctly, you need to enclose multiple conditions that are to be applied together in parentheses.
除了正确处理 NULL 逻辑之外，您还需要将要一起应用的多个条件括在括号中。
Something like this (not sure I understood your conditions exactly).
像这样的事情（不确定我完全理解你的条件）。
UPDATE table1
SET a.field1 = b.field3
FROM table1 a ,
table2 b
WHERE a.field2 = b.field2
AND (
( a.field1 <> b.field3)
OR (a.field1 IS NOT NULL AND b.field3 IS NULL)
OR (a.field1 IS NULL AND b.field3 IS NOT NULL)
)
回答by Rachcha
Tim Shmelter is right in his comment, NULLis not equal to anything- even including NULL. NULLliterally means that the value is unknown.
Tim Shmelter 在他的评论中是对的，NULL它不等于任何东西——甚至包括NULL. NULL字面意思是值未知。
This means, even if a.field1and b.field3both are NULL, the conditions a.field1 <> b.field3as well as a.field1 = b.field3both will always return false. Try it and you will see!
这意味着，即使a.field1和b.field3两者都是NULL，条件a.field1 <> b.field3以及a.field1 = b.field3两者都将始终返回false。试试吧，你会看到的！
I think the solution here does not lie in the IFNULLfunction of SQL Server. It lies more in your joining logic. You already have your solution, i.e., the second query in your question. What I will recommend is you playing a bit more with NULLvalues so you can understand what really are they.
我认为这里的解决方案不在于IFNULLSQL Server的功能。它更多地取决于您的加入逻辑。您已经有了解决方案，即问题中的第二个查询。我会建议你多玩一些NULL价值观，这样你就可以了解它们的真正含义。
回答by KookieMonster
When you write in your query a.field1 = b.field3you actually make two assumptions: field1 in table a must contain a value and field3 in your b table must also contain a value. It is not possible to compare a 'missing information and inapplicable information' to a value. The result of this comparison is unknown. You can have a look for further information on Wikipedia.
当您编写查询时，a.field1 = b.field3您实际上做了两个假设：表 a 中的 field1 必须包含一个值，而 b 表中的 field3 也必须包含一个值。无法将“缺失信息和不适用信息”与某个值进行比较。这种比较的结果是未知的。您可以在Wikipedia上查找更多信息。
回答by Captain O.
Another way would be to use CHECKSUMfunction
另一种方法是使用CHECKSUM函数
create table #temp
(
val1 varchar(255),
val2 varchar(255)
)
insert into #temp values(NULL, NULL)
insert into #temp values(NULL, 'B')
insert into #temp values('A', NULL)
insert into #temp values('A', 'B')
insert into #temp values('A', 'A')
select *,
'Are Not Equal' = case
when val1 <> val2 or checksum(val1) <> checksum(val2) then 'true'
else 'false' end
from #temp
回答by QMaster
This will check if the Column1 and Column2 is equal, Additionally used Convertion to VARBINARY to compare in case sensitive and you can remove it if not necessary.
这将检查 Column1 和 Column2 是否相等，另外使用转换为 VARBINARY 来比较区分大小写，如果不需要，您可以将其删除。
--c1 = Length of Column1
--c2 = Length of Column2
ISNULL(NULLIF(CONVERT(VARBINARY(cl), LTRIM(RTRIM(Column1))), CONVERT(VARBINARY(c2),LTRIM(RTRIM(Column2)))), NULLIF(CONVERT(VARBINARY(c2),LTRIM(RTRIM(Column2))), CONVERT(VARBINARY(c1),LTRIM(RTRIM(Column1))))) IS NULL
You can change the end of expression to IS NOT NULLfor checking unequal condition.
您可以将表达式的结尾更改IS NOT NULL为检查不等条件。
Hope this help.
希望这有帮助。
