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

Answer 1

查看Books Online的完整引用- 默认情况下 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.

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