where 子句中的 SQL 和 NULL 值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3937562/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL And NULL Values in where clause
提问by Greg
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
有人可以向我解释这种行为吗？微软 SQL Server 2008
采纳答案by AdaTheDev
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'
回答by Joe Phillips
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) 结果，它实际上并不知道该记录是否符合您的条件，因此不会在数据集中返回。
回答by KM.
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')
回答by Bob Mc
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 AdaTheDevthe COALESCE function will negate any indices that may exist on the CategoryID column, which can affect the query plan and performance.
正如AdaTheDevCOALESCE 函数所指出的那样，CategoryID 列上可能存在的任何索引都将取反，这可能会影响查询计划和性能。
回答by vol7ron
You may try using the Coalescefunction 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 NULLwhich 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) - You can add a
not nullconstraint to prevent NULLs from ever appearing - A de facto standard of mine is to always assign default values and never allow nulls
- 您可以使用 coalesce 函数创建索引：例如
create index ... coalesce(field) - 您可以添加
not null约束以防止出现 NULL - 我的一个事实上的标准是总是分配默认值并且永远不允许空值
回答by Michael Levy
Null gets special treatment. You need to explicitly test for null. See http://msdn.microsoft.com/en-us/library/ms188795.aspx
Null 得到特殊处理。您需要明确测试是否为空。请参阅http://msdn.microsoft.com/en-us/library/ms188795.aspx
