SQL和WHERE子句中的NULL值

Question

我有一个简单的查询，返回产品列表。

SELECT     Model, CategoryID
FROM         Products
WHERE     (Model = '010-00749-01') 

这会返回什么？

010-00749-01    00000000-0000-0000-0000-000000000000
010-00749-01    NULL

哪个是正确的，我只想要CategoryID不是“00000000-0000-0000-0000-000000000000”的产品，所以我有

SELECT     Model, CategoryID
FROM         Products
WHERE     (Model = '010-00749-01') 
AND (CategoryID <> '00000000-0000-0000-0000-000000000000') 

但是这样返回的结果为空。所以我将查询更改为

SELECT     Model, CategoryID
FROM         Products
WHERE     (Model = '010-00749-01') 
AND ((CategoryID <> '00000000-0000-0000-0000-000000000000') OR  (CategoryID  IS NULL))

哪个会返回预期结果

010-00749-01    NULL

有人能给我解释一下这种行为吗？ MS SQL Server 2008

- Greg

---

维基百科有一篇很好的解释NULL的文章 - http://en.wikipedia.org/wiki/Null_%28SQL%29 - Russ Cam

@Russ Cam - 你应该把这个作为答案发布... - veljkoz

我觉得这更像是一个lmgtfy答案 :) - Russ Cam

Answer 1

请查看完整的参考资料：Books Online - 默认情况下，ANSI_NULLS是开启的，这意味着您需要使用您已经采用的方法。否则，您可以在查询开始时将该设置关闭以切换行为。

当SET ANSI_NULLS为ON时，使用WHERE column_name = NULL的SELECT语句返回零行，即使column_name中有null值。使用WHERE column_name <> NULL的SELECT语句返回零行，即使column_name中有非空值。

当SET ANSI_NULLS为ON时，针对null值的所有比较都计算为UNKNOWN。当SET ANSI_NULLS为OFF时，针对所有数据与null值的比较都计算为TRUE，如果数据值为NULL。

以下是一个简单的示例，演示了与NULL进行比较的行为：

-- This will print TRUE
SET ANSI_NULLS OFF;
IF NULL <> 'A'
    PRINT 'TRUE'
ELSE
    PRINT 'FALSE'

-- This will print FALSE
SET ANSI_NULLS ON;
IF NULL <> 'A'
    PRINT 'TRUE'
ELSE
    PRINT 'FALSE'

- AdaTheDev

---

请注意，将ANSI_NULLS设置为“off”会降低性能并增加逻辑读取次数。打开它会影响查询计划。 - vol7ron

@Vol7ron真的值得一票否决吗？这对于一个正确的答案来说太严厉了。如果你真的觉得它值得一票否决，那么可以理解，但在我看来，似乎很奇怪，也不是一个诚实的投票。 - AdaTheDev

有什么理由给它踩个反对票？我不认为这值得点赞或者点踩。这是一个答案。我也不会说它是“正确”的。实现这个会影响性能，但更重要的是，它在技术上并不符合SQL标准。一旦你实现了它，就偏离了ISO标准。SQL最好的一点之一是可以在各种平台之间进行转换，但你在这里放弃了这一点。Coalesce是一个更好的解决方案。正如我所说，如果你担心性能问题，请使用函数索引该字段，否则请清理底层数据。 - vol7ron

警告：应避免使用ANSI_NULLS，微软表示不建议使用，并且它的弃用已经在即。详情请参考：https://msdn.microsoft.com/zh-cn/library/ms188048.aspx - DaFi4

Answer 2

基本上，NULL 是任何值的缺失。因此，试图将 CategoryId 中的 NULL 与查询中的 varchar 值进行比较将始终导致错误的评估。

您可能想尝试使用 COALESCE 函数，例如：

SELECT     ModelId, CategoryID 
FROM       Products 
WHERE      (ModelId = '010-00749-01')  
AND        ( COALESCE( CategoryID, '' ) <> '00000000-0000-0000-0000-000000000000' ) 

编辑

正如AdaTheDev所指出的，COALESCE函数将使得CategoryID列上可能存在的索引失效，这会影响查询计划和性能。

- Bob Mc

---

请注意这种方法，因为使用COALESCE可能会导致执行计划不够优化，从而阻止对该列进行索引搜索。 - AdaTheDev

Ada是正确的，COALESCE确实会影响执行计划。我会编辑我的答案以包含这一点。 - Bob Mc

Answer 3

总的来说，你需要记住NULL通常意味着UNKNOWN。这意味着如果你说CategoryID <> '00000000-0000-0000-0000-000000000000'，你必须假设查询只会返回符合你条件的已知值。由于存在NULL（未知）结果，它实际上不知道该记录是否符合你的条件，因此不会在数据集中返回。

- Joe Phillips

---

虽然其他答案包含更多的细节，但我喜欢这个答案，因为它非常简单易懂，即使对于非技术人员也很有价值，特别是对于新手开发者。给你一个赞！ - David

Answer 4

看这个：

1=1        --true
1=0        --false
null=null  --false
null=1     --false

1<>1       --false
1<>0       --true
null<>null --false
null<>1    --false    <<<--why you don't get the row with: AND (CategoryID <> '00000000-0000-0000-0000-000000000000') 

- KM.

---

从技术上讲，SQL使用三值逻辑系统，因此，所有与NULL进行的比较都会产生UNKNOWN而不是FALSE。请参见：SQL和三值逻辑的陷阱。 - Joe Stefanelli

无论是称之为“false”还是“unknown”，都无关紧要，因为该行不包含在结果集中，而这正是问题的关键所在。 - KM.

Answer 5

您可以尝试使用Coalesce函数为具有null的字段设置默认值：

   SELECT    Model , CategoryID
   FROM      Products
   WHERE     Model = '010-00749-01'
     AND     Coalesce(CategoryID,'') <> '00000000-0000-0000-0000-000000000000'

我认为问题在于你对NULL的理解，它基本上意味着“没有任何东西”。你不能将任何东西与空值进行比较，就像你不能将一个数字除以0一样。这只是数学/科学规则。

编辑： 正如Ada指出的那样，这可能会导致索引字段不再使用索引。

解决方案：

• 您可以使用coalesce函数创建索引：例如create index ... coalesce(field)
• 您可以添加not null约束以防止出现NULL
• 我的实际标准是始终分配默认值并永远不允许为空

- vol7ron

---

请注意这种方法，因为使用COALESCE可能会导致执行计划不够优化，从而阻止对该列进行索引搜索。 - AdaTheDev

这可能是真的，然而，你可以创建一个索引作为coalesce(field)，或者添加一个非空约束来防止NULL值的出现。我的一种事实标准是始终分配默认值并且不允许为空。 - vol7ron

Answer 6

Null值需要特殊处理。您需要明确地测试null值。请参见http://msdn.microsoft.com/en-us/library/ms188795.aspx

- Michael Levy