where子句中的SQL和NULL值

So I have a simple query that returns a listing of products

所以我有一个简单的查询返回产品列表

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

This returns

这回来了

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

Which is correct, so I wanted only the products whose CategoryID is not '00000000-0000-0000-0000-000000000000' so I have

这是正确的，所以我只想要其CategoryID不是'00000000-0000-0000-0000-000000000000'的产品，所以我有

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

But this returns no result. So I changed the query to

但这没有结果。所以我将查询更改为

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

Which returns expected result

哪个返回预期结果

010-00749-01    NULL

Can someone explain this behavior to me? MS SQL Server 2008

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

6 个解决方案

#1

Check out the full reference on Books Online - by default ANSI_NULLS is on meaning you'd need to use the approach you have done. Otherwise, you could switch that setting OFF at the start of the query to switch the behaviour round.

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

When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.
...
When SET ANSI_NULLS is ON, all comparisons against a null value evaluate to UNKNOWN. When SET ANSI_NULLS is OFF, comparisons of all data against a null value evaluate to TRUE if the data value is NULL.

当SET ANSI_NULLS为ON时，即使column_name中存在空值，使用WHERE column_name = NULL的SELECT语句也会返回零行。即使在column_name中存在非空值，使用WHERE column_name <> NULL的SELECT语句也会返回零行。 ...当SET ANSI_NULLS为ON时，对空值的所有比较都计算为UNKNOWN。当SET ANSI_NULLS为OFF时，如果数据值为NULL，则将所有数据与空值的比较计算为TRUE。

Here's a simple example to demonstrate the behaviour with regard to comparisons against 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'

#2

In general, you have to remember that NULL generally means UNKNOWN. That means if you say CategoryID <> '00000000-0000-0000-0000-000000000000' you have to assume that the query will only return values that it KNOWS will meet your criteria. Since there is a NULL (UNKNOWN) result, it does not actually know if that record meets your criteria and therefore will not be returned in the dataset.

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

#3

look at this:

看这个：

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

#4

Basically, a NULL is the absence of any value. So trying to compare the NULL in CategoryId to a varchar value in the query will always result in a false evaluation.

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

You might want to try using the COALESCE function, something like:

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

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

EDIT

编辑

As noted by AdaTheDev the COALESCE function will negate any indices that may exist on the CategoryID column, which can affect the query plan and performance.

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

#5

Null gets special treatment. You need to explicitly test for null. See http://msdn.microsoft.com/en-us/library/ms188795.aspx

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

#6

You may try using the Coalesce function to set a default value for fields that have null:

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

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

I think the problem lies in your understanding of NULL which basically means "nothing." You can't compare anything to nothing, much like you can't divide a number by 0. It's just rules of math/science.

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

Edit: As Ada has pointed out, this could cause an indexed field to no longer use an index.

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

Solution:

解：

You can create an index using the coalesce function: eg create index ... coalesce(field)
您可以使用coalesce函数创建索引：例如create index ... coalesce（field）
You can add a not null constraint to prevent NULLs from ever appearing
您可以添加非空约束以防止NULL出现
A de facto standard of mine is to always assign default values and never allow nulls
我的事实上的标准是始终分配默认值并且永远不允许空值

#1