SQL比较和空值的问题

Question

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

UPDATE  table1
SET     a.field1 = b.field3
FROM    table1 a ,
        table2 b
WHERE   a.field2 = b.field2
        AND a.field1 <> b.field3

我遇到的问题是它不能检测到当a.field1为空值且b.field3为一个值时，或者当a.field1为一个值且b.field3为空值时。

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

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

我的问题更集中于为什么会发生这种情况以及如何最好地构建查询以防止这种情况发生？

- Heather

---

空值等同于无，它是一种未定义的值，您无法将其与任何东西进行比较（即使使用<>）。这就是为什么空值记录被省略的原因。因此，您必须明确使用 IS NULL 或 IS NOT NULL。 - Tim Schmelter

可能是在NULL上使用不等于<> !=运算符的T-SQL重复问题。 - Cyril Gandon

最近我遇到了一个“相反”的问题。有人给我提供了一条涉及比较的 SQL 语句，例如 WHERE val=null ...，而它在我们的 SQL Server 上实际上确实可以像 WHERE val IS NULL 一样工作！原来我们的服务器上启用了一个名为 SET ANSI_NULLS OFF 的设置。这个（已弃用的！！）设置允许空值比较，请参见此处。 - Carsten Massmann

这种行为是特定于Microsoft SQL Server吗？ - undefined

Answer 1

问题出在 NULL 比较上。如果 a.field1 或 b.field3 是 NULL，你需要使用 IS NULL 或者 IS NOT NULL 语句进行比较。你可以使用 ISNULL 函数为 a.field1 和 b.field3 设置默认值。

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

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

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

- Norberto108

---

我从未使用过这个isnull...我会尝试一下..谢谢你的新提示。 - Heather

@Heather：ISNULL函数是为了展示而设计的，而不是用于过滤（WHERE）。 - Bogdan Sahlean

@Norberto108：如果“它[似乎]工作”，那并不意味着这是一个“好的解决方案”。如果a.field1 = 0 AND b.field3 IS NULL怎么办？ - Bogdan Sahlean

@BogdanSahlean，0必须是一个任意且超出范围的值。重点是继续使用<>运算符。它可以是-1等等... - Norberto108

@BogdanSahlean 我同意Norberto的观点。ISNULL只是一个函数，你可以在任何地方使用它，前提是你理解使用函数的风险和注意事项。我经常使用这种模式来快速进行即时查询，以避免像OP提出的那样像意大利面条一样混乱的查询解决方案。 - Vland

Answer 2

将任何东西与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
              )

- Aleksandr Fedorenko

---

迄今为止最好的答案，如此记录在这里。ISNULL()选项很好用，直到它不再适用。 - AHiggins

Answer 3

除了正确处理NULL逻辑外，您还需要将要一起应用的多个条件括在括号中。就像这样（不确定我是否完全理解了您的条件）。

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

- DOK

---

谢谢...我在示例中漏掉了括号，但它们在我的代码中...我已经编辑过以使其更清晰。 - Heather

Answer 4

在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)
        )

我假设你的类型是数字，虽然你可以使用其他数据类型。我也假设如果两个值都为NULL，则这两行是相等的。

- Chanoch

Answer 5

Tim Shmelter在他的评论中是正确的，NULL不等于任何东西-甚至包括NULL本身。 NULL字面意思是该值未知。

这意味着，即使a.field1和b.field3都是NULL，条件a.field1 <> b.field3以及a.field1 = b.field3都将始终返回false。试一下你就会发现！

我认为这里的解决方案并不在于SQL Server的IFNULL函数。它更多地在于您的连接逻辑。您已经有了解决方案，即您问题中的第二个查询。我建议您再玩一下NULL值，这样您就可以理解它们到底是什么。

- Rachcha

---

从我所阅读的内容来看，如果想要进行任何比较查询，几乎都需要使用ifnull函数？ - Heather

@Rachcha - 小修正：如果a.field1或b.field3中任意一个（或两个）为NULL，则条件a.field1 <> b.field3和a.field1 = b.field3将不会返回FALSE，而是返回NULL。在大多数情况下（如此例），它的行为类似于FALSE，因为它不是TRUE，但它仍然不是FALSE，不能被否定回到TRUE。如果在前面加上NOT（a.field1 = b.field3），它仍将为NULL并且类似于FALSE。 - Nenad Zivkovic

这是一个非常有帮助的评论。看起来你已经处理了很多空值！ - Rachcha

Answer 6

这将检查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

您可以将表达式的结尾改为IS NOT NULL，以检查不等条件。

希望这能帮到您。

- QMaster

Answer 7

另一种方法是使用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

- Captain O.

Answer 8

当您在查询中编写a.field1 = b.field3时，实际上做出了两个假设：表a中的field1必须包含一个值，而您的b表中的field3也必须包含一个值。不可能将“缺失信息和不适用信息”与值进行比较。此比较的结果是未知的。您可以查看Wikipedia以获取更多信息。

- KookieMonster