NULL 值被排除在外.为什么? [英] NULL values are excluded. Why?

问题描述

这是关于我在 Microsoft Sql Server 中发现的一个奇怪的行为.如果我错了，请纠正我.

This is about a bizarre behaviour I found in Microsoft Sql Server. Please correct me if I'm wrong.

SELECT COUNT(*) FROM TABLEA 
WHERE [Column1] IS NULL;

这将返回 30018 行.

This returns 30018 rows.

CREATE VIEW VIEWB AS 
SELECT * FROM TABLEA AS t1 
WHERE t1.[Column1] NOT IN ('Cross/Up sell', 'Renegotiation', 'Renewal')  

如果我检查VIEWB，我在Column1中找不到NULL:

If I check VIEWB, I don't find NULL in Column1:

SELECT COUNT(*) FROM VIEWB 
WHERE [Column1] IS NULL;

这将返回 0 行.

为什么?上面的查询排除了 3 个值，但不应该排除 NULL.为什么 Ms Sql Server 会这样?我应该预料到这一点吗?我该如何解决?

Why? The query above excludes the 3 values, but it isn't supposed to exclude NULL. Why does Ms Sql Server behave this way? Should I have expected this? How can I fix it?

推荐答案

这实际上是 SQL Server 将 NULL 视为值时常见的错误.默认情况下，它被视为未知，如此处所述.因此，在您看来，您还需要包含一个 OR t1.[Column1] IS NULL.

This is actually a common mistake made with SQL Server in treating NULL as a value. By default, it's treated as UNKNOWN, as documented here. So, in your view, you also need to include an OR t1.[Column1] IS NULL.

您可以通过调用 SET ANSI_NULLS OFF 来更改此行为.但是，不建议使用此功能，因为正如@Martin Smith 所指出的那样，该功能已被弃用.

You can change this behavior by calling SET ANSI_NULLS OFF. It is not recommended to use this, however, as the feature is deprecated as pointed out by @Martin Smith.

然而，这不是 SQL Server 特定的问题.它是 ANSI SQL 标准的一部分.

This is not a SQL Server specific issue, however. It's part of the ANSI SQL standard.

这篇关于NULL 值被排除在外.为什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！