where 子句中的 SQL 和 NULL 值 [英] SQL And NULL Values in where clause

问题描述

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

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

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"的产品，所以我有

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

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

返回预期结果

010-00749-01    NULL

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

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

推荐答案

查看 在线书籍 - 默认情况下 ANSI_NULLS 处于开启状态，这意味着您需要使用您已经完成的方法.否则，您可以在查询开始时关闭该设置以切换行为.

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.

当 SET ANSI_NULLS 为 ON 时，一个 SELECT使用 WHERE column_name 的语句= NULL 返回零行，即使 column_name 中有空值.一种使用 WHERE 的 SELECT 语句column_name <> NULL 返回零行即使有非空值列名.
...
当 SET ANSI_NULLS为 ON，所有与 null 的比较值评估为 UNKNOWN.设置时ANSI_NULLS 为 OFF，所有的比较针对空值的数据评估为如果数据值为 NULL，则为 TRUE.

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 进行比较的行为:

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

这篇关于where 子句中的 SQL 和 NULL 值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！