如何正确比较SQL Server中两个可以为null的BIT值

Question

SQL Server中可为null的BIT值有3个可能的值：null、0和1。我有两列都是可为NULL的BIT，我想知道它们何时"相等"，即NULL"等于"NULL，1等于1，0不"等于"NULL。但是，我所有的案例陈述都没有给我想要的答案。准确地进行比较的最佳方法是什么？

DECLARE @BitComparison TABLE
(
OldValue BIT, 
NewValue BIT,
ActuallyEqual VARCHAR(10)
)
INSERT INTO @BitComparison (OldValue, NewValue, ActuallyEqual)
VALUES 
(null,null,'equal'),
(null,0,'not equal'),
(null,1,'not equal'),
(0,null,'not equal'),
(0,0,'equal'),
(0,1,'not equal'),
(1,null,'not equal'),
(1,0,'not equal'),
(1,1,'equal')
SELECT *
, CASE WHEN OldValue <> NewValue then 'not equal' else 'equal' end as 'ComparisonTestA'
, CASE WHEN ISNULL(OldValue, 0) <> ISNULL(NewValue, 0) then 'not equal' else 'equal' end as 'ComparisonTestB'
, CASE WHEN ISNULL(OldValue, -1) <> ISNULL(NewValue, -1) then 'not equal' else 'equal' end as 'ComparisonTestC'
FROM @BitComparison

给定上面的脚本，硬编码的ActuallyEqual列中没有任何值与ComparisonTest列中的值匹配。我可以向SELECT中添加什么来动态匹配ActuallyEqual列？

Answer 1

使用IS NULL进行NULL检查

CASE
WHEN OldValue = NewValue
OR ( OldValue IS NULL AND NewValue IS NULL ) THEN 'equal'
ELSE 'not equal'
END AS 'ComparisonTestA'

Answer 2

2022年更新

SQL Server现在有IS [NOT] DISTINCT FROM谓词，因此您可以使用：

SELECT *, CASE WHEN OldValue IS DISTINCT FROM NewValue THEN 'not equal' ELSE 'equal' END AS ComparisonTest
FROM @BitComparison;

这将返回所需的结果(例如，如果两者都为空，则将它们标识为相等)。

对于以前没有这个谓词的版本，还有另一种方法可以使用，我在6年半前没有提到，它是有效的，那就是使用INTERSECT或EXCEPT，因为这两种方法都会将NULL的两边视为相等，例如

SELECT  *, CASE WHEN EXISTS (SELECT OldValue EXCEPT SELECT NewValue) THEN 'not equal' ELSE 'equal' END AS ComparisonTest
FROM    @BitComparison;

这对于像这样的单列比较来说用处有限，但如果您要比较多个值，检查相等性和null可能会变得非常冗长，例如

WHERE (OldValue1 = NewValue1 OR (OldValue1 IS NULL AND NewValue1 IS NULL))
AND (OldValue2 = NewValue2 OR (OldValue2 IS NULL AND NewValue2 IS NULL)) 
AND (OldValue3 = NewValue3 OR (OldValue3 IS NULL AND NewValue3 IS NULL)) 

使用上述方法，这变成：

WHERE EXISTS (SELECT OldValue1, OldValue2, OldValue3 
INTERSECT  
SELECT NewValue1, NewValue2, NewValue3)

这(至少在我看来)读写起来容易得多。

db<gt；小提琴

---

原始答案

已经发布的解决方案是我能想到的最简单的解决方案。这里有一个细微的变化：

CASE WHEN OldValue = NewValue 
OR ISNULL(NewValue, OldValue) IS NULL THEN 'Equal' ELSE 'Not Equal' END

但我想补充一个解释，为什么你的每个表达式都不起的作用

---

CASE WHEN OldValue <> NewValue THEN 'not equal' ELSE 'equal' END

将失败，因为NULL <> Anything是NULL，因此当其中一个值是NULL时，它将落入ELSE语句并返回equal。

---

CASE WHEN ISNULL(OldValue, 0) <> ISNULL(NewValue, 0) THEN 'not equal' ELSE 'equal' END

当其中一个值为NULL时，它被替换为0，因此，当一个值是NULL，另一个为0时，当两者不匹配时，它们将被识别为匹配。

---

CASE WHEN ISNULL(OldValue, -1) <> ISNULL(NewValue, -1) THEN 'not equal' ELSE 'equal' END

这看起来像是应该工作的，但由于ISNULL将返回第一个参数的数据类型，-1将转换为1(如SELECT CONVERT(BIT, -1)所示)，因此，当一个值为1而另一个为null时，这将错误地识别匹配。

考虑到这一点，您也可以将ISNULL替换为COALESCE:

CASE WHEN COALESCE(OldValue, -1) = COALESCE(NewValue, -1) THEN 'equal' ELSE ' not equal' END

由于COALESCE将返回优先级最高的数据类型(INT > BIT)，而不是第一个参数的数据类型。

Answer 3

这里有一个使用CONCAT()的有趣替代方案。

SELECT OldValue,NewValue,ActuallyEqual
, Test = IIF(concat(OldValue,'-',NewValue) = concat(NewValue,'-',OldValue),'equal','not equal')
FROM @BitComparison

返回

OldValue    NewValue    ActuallyEqual   Test
NULL        NULL        equal           equal
NULL        0           not equal       not equal
NULL        1           not equal       not equal
0           NULL        not equal       not equal
0           0           equal           equal
0           1           not equal       not equal
1           NULL        not equal       not equal
1           0           not equal       not equal
1           1           equal           equal