SQL 比较和空值的问题 [英] Issues with SQL comparison and null values

问题描述

我有一个更新查询，当值与另一个表中的字段不匹配时，它会更新一个表中的字段.

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

我遇到的问题是，当 a.field1 为 null 且 b.field3 为值或 a.field1 为值且 b.field3 为 null 时，它无法启动.

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.

我通过添加以下内容解决了这个问题...

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?

推荐答案

问题在于 NULL 比较.如果 a.field1 或 b.field3 为 NULL，则需要使用 IS NULL 或 IS NOT NULL 语句.您可以使用 ISNULL 函数为 a.field1 和 b.field3 使用默认值.

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.

ISNULL(a.field1,0) <> ISNULL(b.field3,0)

在这种情况下，会与值 0 进行比较.

in this case there is a comparison with the value 0.

SELECT IIF(NULL=NULL,'true','false') -- 结果为假.太棒了！

SELECT IIF(NULL=NULL,'true','false') -- The result is false. Amazing!

这篇关于SQL 比较和空值的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！