SQL Server 与 NULL 的比较

Question

我需要对一个值和其之前的值进行大量比较。

例如：ReceivedBy 和 PreviousReceivedBy。

我开始使用以下代码：

WHERE ReceivedBy != PreviousReceivedBy

但如果任意一个值为null，则会返回false（而我需要它返回true）。因此，我将其更新为以下方式：

WHERE ReceivedBy != PreviousReceivedBy
      OR (ReceivedBy IS NULL AND PreviousReceivedBy IS NOT NULL)
      OR (ReceivedBy IS NOT NULL AND PreviousReceivedBy IS NULL)

这段代码可以正常工作，但是我有一个需要比较的大字段列表。我希望找到一种用更少的代码进行比较的方法（而不是关闭 ANSI_NULLS）。

显然，如果没有其他方法，我将为比较输入所有3行代码。

更新：

以下是我希望的示例。

ReceivedBy = 123  
PreviousReceivedBy = 123  
Result = FALSE  

ReceivedBy = 5  
PreviousReceivedBy = 123  
Result = TRUE  

ReceivedBy = NULL  
PreviousReceivedBy = 123  
Result = TRUE

ReceivedBy = 123  
PreviousReceivedBy = NULL  
Result = TRUE  

ReceivedBy = NULL  
PreviousReceivedBy = NULL  
Result = FALSE  

- Vaccano

---

WHERE isnull(ReceivedBy,'ReceivedBy is null') != isnull(PreviousReceivedBy,'PreviousReceivedBy is null') 这个怎么样？ - Hackerman

在Hackerman上面的答案中，如果两个值都为NULL，则当它应该是FALSE时，isnull(ReceivedBy,'ReceivedBy is null')') != isnull(PreviousReceivedBy,'PreviousReceivedBy is null')会变成TRUE。（可以通过用另一个相等的值替换两个文字来修复。） - Rob at TVSeries.com

Answer 1

如果两个列都是varchar类型，我建议使用以下代码：

coalesce(ReceivedBy, 'NULL') != coalesce(PreviousReceivedBy, 'NULL')

如果它们是整数，我会将某些值大幅下调（以清楚地表示null值），而不是使用'NULL'。

从列名可以推断出它必须是字符串值或整数值 :)

更新：

正如@Siyual指出的那样，替换字符串应该是“不可能发生的事”，您应该用一些非字母字符（例如'#'）代替上面的'NULL'。

- Michał Turczyn

---

我想指出的是，这个“值”可能是任何东西，而空值检查应该是在可能性范围之外的。我记得有一次航空公司就做了这样的检查，结果遇到了一个真实姓氏为“Null”的人。结果那家航空公司输了 ;) - Siyual

你也可以使用ISNULL代替COALESCE（它更短，更容易输入:p） - Aximili

如果表达式的类型为 UNIQUEIDENTIFIER，则会导致“无法将字符字符串转换为唯一标识符”错误，因为这些替代字符串无法转换为 uid 进行比较。 - djk

Answer 2

另一种不需修改数据的方法是使用 COALESCE

Where ReceivedBy != PreviousReceivedBy
And Coalesce(ReceivedBy, PreviousReceivedBy) Is Not Null

NULL不能等于任何值，甚至是另一个NULL，因此如果任何一个值为NULL，则ReceivedBy != PreviousReceivedBy将计算结果为true。

其次，如果两个值都是NULL，Coalesce(ReceivedBy, PreviousReceivedBy) Is Not Null将计算结果为false，强制过滤它们。

如果两者都不是NULL，则如果它们相等，第一个条件将失败。

诚然，这并没有节省太多代码，但它确实是一种改进方式。

您可以轻松地将其分组在括号中，并将其复制/粘贴到您需要检查的所有剩余字段中。

Where (ReceivedBy != PreviousReceivedBy And Coalesce(ReceivedBy, PreviousReceivedBy) Is Not Null)
And[Or] (Foo != Bar And Coalesce(Foo, Bar) Is Not Null)
...

- Siyual

---

当值相同时，这似乎无法工作。 - Vaccano

@Vaccano 我不知道你需要那个条件。你只是想要两者都不为空的东西吗？ - Siyual

我的原始语句是 WHERE ReceivedBy != PreviousReceivedBy，只捕获它们不同的情况。因此，如果这些值相同，我需要它评估为 FALSE。第一半将是 FALSE，但如果它们都是非空值，则第二半将是 TRUE。FALSE OR TRUE = TRUE。因此，我的表达式将捕获相同的事物，而我需要将它们排除在外。 - Vaccano

请查看我更新后的问题，以了解我希望得到的示例。 - Vaccano

@Vaccano 我明白了 - 将逻辑中的 OR 更新为 AND。这将需要一个值为非空并且它们不相等。我现在无法测试，但这应该是正确的 - 如果不正确，请告诉我。NULL 不能等于任何东西，因此如果其中一个为 NULL，第一个检查将通过，并且第二个将过滤掉两个 NULL 值。如果两者都不是 NULL，则比较将像正常情况下一样运行。 - Siyual

我认为这里有一个缺陷。我最近了解到，与null的比较总是等于UNKNOWN，而无法“NOT”为TRUE。https://stackoverflow.com/a/50919888/16241 我想我只能使用我的扩展逻辑了。感谢您花时间研究此问题。 - Vaccano

Answer 3

我在与可空值进行比较时遇到了与您相同的问题，NULL总是返回unknown，远离我们所期望的TRUE或FALSE之间的唯一比较。

最终，我声明了一个标量值函数，其中包含这些逻辑，如其他SQL(s)处理null，例如：

普通比较运算符产生 null（表示“未知”），而不是 true 或 false，当任一输入为 null 时。例如，7 = NULL 产生 null，7 <> NULL 也是如此。当此行为不合适时，请使用 IS [ NOT ] DISTINCT FROM 结构：

a IS DISTINCT FROM b => a != b
a IS NOT DISTINCT FROM b => a == b

a IS NOT DISTINCT FROM b可以被重写为什么？

(a IS NOT NULL AND b IS NOT NULL AND a=b) OR (a IS NULL AND b is NULL)

我使用 sql_variant 来处理这些基本参数：int，datetime，varchar等。

create function IsEqual(
    @a sql_variant,
    @b sql_variant
)
returns bit
as
begin
    return (CASE WHEN (@a IS NOT NULL AND @b IS NOT NULL AND @a=@b) OR (@a IS NULL AND @b is NULL) THEN 1 ELSE 0 END);
end

create function IsNotEqual(
    @a sql_variant,
    @b sql_variant
)
returns bit
as
begin
    return 1-dbo.IsEqual(@a,@b);
end

使用

select dbo.IsEqual(null, null) Null_IsEqual_Null,
dbo.IsEqual(null, 1) Null_IsEqual_1,
dbo.IsEqual(1, null) _1_IsEqual_Null,
dbo.IsEqual(1, 1)  _1_IsEqual_1,
dbo.IsEqual(CAST('2017-08-25' AS datetime), null) Date_IsEqual_Null,
dbo.IsEqual(CAST('2017-08-25' AS datetime), CAST('2017-08-25' AS datetime)) Date_IsEqual_Date

对于您的情况

select dbo.IsNotEqual(123,123) _123_IsNotEqual_123,
dbo.IsNotEqual(5,123) _5_IsNotEqual_123,
dbo.IsNotEqual(Null,123) Null_IsNotEqual_123,
dbo.IsNotEqual(123,Null) _123_IsNotEqual_Null,
dbo.IsNotEqual(Null,Null) Null_IsNotEqual_Null

- Tấn Nguyên

Answer 4

WHERE ISNULL(ReceivedBy, -1) != ISNULL(PreviousReceivedBy, -1)

假设列中没有负值

- Андрей Чеботюк

Answer 5

如果其中一个值为null，则返回false。实际上，它返回的是“unknown”，而不是“false”（SQL使用三值逻辑）。但是在WHERE子句中，组合谓词必须计算为“true”，才能返回行，因此这里的效果基本相同。从SQL Server 2022开始，您可以使用is distinct from。

WHERE  ReceivedBy IS DISTINCT FROM PreviousReceivedBy

- Martin Smith

---

这是一个改变游戏规则的东西。可惜我正在运行较旧版本的SQL Server。 - iruvar

Answer 6

DECLARE @phantom_not_null int = -1

DECLARE @Tests TABLE (Expected int, Actual int)

INSERT @Tests VALUES (NULL, NULL), (NULL, 0), (0, NULL), (0, 0), (0, 1)

SELECT * FROM @Tests 
WHERE ISNULL(Expected, @phantom_not_null) != ISNULL(Actual, @phantom_not_null)

- user12116884

Answer 7

(NULLIF(@a, @b) IS NOT NULL) OR (NULLIF(@b, @a) IS NOT NULL) 的意思是，即使其中一个或两个都为 null，"@a != @b 也成立。

- Garm

---

你可以使用反引号 (`) 来突出显示内联代码。这将使答案更易于理解。 - Exelian

这段代码是不起作用的，因为 declare @a int = null, @b int = 123; select nullif(@a, @b); 返回 NULL（假），即使 @a != @b（它应该是真）。 - Rob at TVSeries.com

尝试这个：(NULLIF(@a, @b) IS NOT NULL) OR (NULLIF(@b, @a) IS NOT NULL) - Garm