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

这是正确的，所以我只想要类别ID不是'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))

Which returns预期结果

010-00749-01    NULL

有人可以向我解释这种行为吗？ 微软 SQL Server 2008

Answer 1

查看联机丛书上的完整参考资料- 默认情况下 ANSI_NULLS 处于开启状态，这意味着您需要使用您已经完成的方法。否则，您可以在查询开始时关闭该设置以切换行为。

当 SET ANSI_NULLS 为 ON 时，SELECT
使用 WHERE column_name 的语句
= NULL 即使column_name 中存在空值也会返回零行。一个
使用 WHERE 的 SELECT 语句
列名 <> NULL 返回零行
即使其中有非空值
列名。

...
当设置 ANSI_NULLS 时
为 ON 时，所有与空值的比较
值评估为未知。当设置时
ANSI_NULLS 为 OFF，所有的比较
针对空值的数据评估为
如果数据值为 NULL，则为 TRUE。

下面是一个简单的示例，演示了与 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'

Answer 2

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

Answer 3

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

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

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

EDIT

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

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

Answer 5

Null 得到特殊对待。您需要显式测试 null。请参阅 http://msdn.microsoft.com/en-us/library/ms188795。 ASPX

Answer 6

您可以尝试使用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 我的事实上的标准是始终分配默认值并且绝不允许空值