“NOT”何时不是否定？

Question

为什么以下两个查询都返回零？第二个难道不是对第一个的否定吗？我正在使用 SQL Server 2008。

DECLARE 
    @a VARCHAR(10) = NULL ,
    @b VARCHAR(10) = 'a'

SELECT  
    CASE WHEN ( ( @a IS NULL
                      AND @b IS NULL
                    )
                    OR @a = @b
                  ) THEN 1
         ELSE 0
    END , -- Returns 0
    CASE WHEN NOT ( ( @a IS NULL
                      AND @b IS NULL
                    )
                    OR @a = @b
                  ) THEN 1
         ELSE 0
    END -- Also returns 0

- ChrisN

---

在查询语句前加上 set ansi_nulls off;，你就会看到不同之处。这样，在至少有一个操作数为 NULL 的比较中，你就不会得到 NULL，而是得到一个 false。 - Tim Schmelter

@Luaan的回答是100%正确的。然而，你可能正在寻找IS DISTINCT FROM操作符（在MySQL中为<=>）。 - Owen

Answer 1

这是一个否定。但是，您需要了解 ANSI NULLs - 对 NULL 的否定也是 NULL。而 NULL 是一个虚假的真值。

因此，如果您的任何参数为 null，则@a = @b 的结果将为 null（虚假），对其进行否定也将为 null（虚假）。

要按照您的意愿使用否定，您需要摆脱 NULL。但是，简单地反转比较结果可能更容易：

case when (...) then 1 else 0 end,
case when (...) then 0 else 1 end

这将始终为您提供1, 0或0, 1。

编辑：

正如jpmc26所指出的那样，可能需要稍微扩展一下空值的行为，以便您不会认为单个NULL会使所有内容NULL。当它们的一个参数为空时，有些运算符并不总是返回null，最明显的例子当然是is null。

在更广泛的例子中，T-SQL中的逻辑运算符使用Kleene代数（或类似物），它定义了OR表达式的真值，如下所示：

  | T | U | F
T | T | T | T
U | T | U | U
F | T | U | F

(AND类似，其他运算符也是类似的)

所以你可以看到，如果至少一个参数为true，则结果也将为true，即使另一个参数是未知的（“null”）。这也意味着not(T or U)将给您一个虚假的真值，而not(F or U)将同样给您一个虚假的真值，尽管F or U是虚假的 - 因为F or U是U，而not(U)也是U，这是虚假的。

这很重要，解释了为什么当两个参数都为null时，您的表达式按预期工作 - @a is null and @b is null计算为true，并且true or unknown计算为true。

- Luaan

---

显然，SQL Server 2008之后的版本将不允许您关闭ansi nulls。也就是说，如果您重建存储过程，这些版本会强制它们具有“正确”的行为，如果您有依赖于不正确行为的旧代码，则会遭受灾难。您可能需要进行大量重写。以下是关于那些邪恶的ansi nulls的好博客文章：http://www.sqlservercentral.com/blogs/sqlstudies/2014/07/28/what-is-ansi_nulls-and-why-will-i-be-glad-when-it-finally-goes-away/ - MickeyfAgain_BeforeExitOfSO

@NoSaidTheCompiler 意思是一个行为类似于 false 的真值。 - user253751

@mickeyf 事实上，ANSI NULL非常有用。它只是意味着您需要了解NULL的工作原理-NULL是未知的，而不是零。 0 + NULL 没有意义，让它引起错误比所谓的治疗更糟糕。主要问题是如果您采取主要语言的所有假设，并假设它们在SQL（以及所有SQL变体）中起作用相同。例如，T-SQL根本没有布尔类型-您无法声明真值变量。这会导致很多混淆，特别是当人们认为 bit 是布尔类型时。 - Luaan

@Luaan 我知道。true OR NULL 只是 true，因为"未知"并不重要。false OR NULL 是 NULL，因为 false 不重要。但是这个答案没有清楚地说明这个重要的事实。 - jpmc26

@jpmc26 哦，我现在明白你指的是什么了。我没有说所有运算符都会传播null（这将使得is null非常不可能），但我扩展了答案来涵盖这一点。 - Luaan

Answer 2

您遇到的这种“奇怪”的行为是由NULL值引起的。

对于NOT（返回NULL的操作）的否定，结果不是TRUE，而是仍然是NULL。

例如：

SELECT * FROM <Table> WHERE <Column> = null -- 0 rows 
SELECT * FROM <Table> WHERE NOT (<Column> = null) -- Still 0 rows

除了这里所说的，您可以通过使用以下方法避免这种行为：

SET ANSI_NULLS OFF

这将允许优化器将 NULL 视为正常值，并返回 TRUE\FALSE。需要注意的是，这种做法并不被推荐，应该避免使用！

- sagi

Answer 3

如果@a=@b的任意一个值为空，则会出现问题。如果您尝试下面的代码，将得到正确的结果。

DECLARE 
    @a VARCHAR(10) = NULL ,
    @b VARCHAR(10) = 'a'

SELECT  
    CASE WHEN ( ( @a IS NULL
                      AND @b IS NULL
                    )
                    OR @a = @b
                  ) THEN 1
         ELSE 0
    END , -- returns 0
    CASE WHEN NOT ( ( @a IS NULL
                      AND @b IS NULL
                    )
                    OR ISNULL(@a,-1) = ISNULL(@b,-1)
                  ) THEN 1
         ELSE 0
    END -- also returns 0

- Kannan Kandasamy

---

使用 ISNULL 可以使 @a IS NULL AND @b IS NULL 检查变得不必要。这也假设值永远不会是 -1，而且当类型为 VARCHAR 时，你确定可以放置 -1 吗？ - jpmc26

Answer 4

NOT总是表示否定。T-SQL行为的原因在于，null值根据数据库配置设置（称为ansi_nulls）以特殊方式处理。根据此设置，null被视为与任何其他值相同，或者被视为“未设置值”。在这种情况下，包含null值的所有表达式都被视为无效。

此外，表达式

(@a IS NULL AND @b IS NULL)
OR 
@a = @b

仅涵盖了两个变量都为NULL的情况，不处理@a或@b其中一个为NULL的情况。如果发生这种情况，则结果取决于ansi_nulls的设置：如果它是on，那么@a = @b的结果始终为false，如果其中一个变量为NULL。

如果ansi_nulls为off，则将NULL视为一个值，并按预期行为进行处理。

为避免此类意外行为，您应该按以下方式涵盖所有情况：

DECLARE 
    @a VARCHAR(10) = 'a',
    @b VARCHAR(10) = null

SELECT  
    CASE 
        WHEN (@a IS NOT null AND @b IS null) THEN 0
        WHEN (@a IS null AND @b IS NOT null) THEN 0
        WHEN (@a IS null AND @b IS null) THEN 1
        WHEN (@a=@b) THEN 1
    ELSE 0
    END 

请注意，在此示例中，所有空值情况都在检查@a=@b之前处理（在CASE语句中，WHEN按照它们出现的顺序进行处理，如果条件匹配，则处理完成并返回指定的值）。

---

为了测试所有可能的（相关）组合，您可以使用此脚本：

DECLARE @combinations TABLE (
    a VARCHAR(10),b VARCHAR(10)
    )

INSERT INTO @combinations
    SELECT 'a', null 
    UNION SELECT null, 'b'
    UNION SELECT 'a', 'b'
    UNION SELECT null, null
    UNION SELECT 'a', 'a'

SELECT a, b,
    CASE 
        WHEN (a IS NOT null AND b IS null) THEN 0
        WHEN (a IS null AND b IS NOT null) THEN 0
        WHEN (a IS null AND b IS null) THEN 1
        WHEN (a=b) THEN 1
    ELSE 0
    END as result
from @combinations
order by result 

它返回：

换句话说，在这个脚本中，null被视为一个值，因此a='a'和b=null返回0，这是您所期望的。只有当两个变量都相等（或都为null）时，它才返回1。

- Matt