在 SQL Server 中模拟 NULL 安全相等运算符
本文介绍如何使用 SQL Server 中的 UDF 来模拟安全 NULL 相等运算符。
引言
MySQL 有一个名为安全 NULL 相等运算符的非标准扩展运算符，即 <=>。该运算符比较两个值，如果它们相同，则返回 1。与普通相等运算符的区别在于，如果两个操作数都是 null，则结果是 1 而不是 null。
在深入实现之前
那么，为什么 null 的处理方式与其他值不同呢？ANSI 标准将 null 值定义为未知。将 null 与 null 进行比较时，它们永远不相等。更具体地说，它们也不不等，一个 null 永远不会小于另一个 null，依此类推。由于 null 的真实值是未知的，因此根据定义，任何比较结果都是未知的，我们只是不知道结果。
从设计的角度来看，null 代表缺失的信息，并且应始终以这种方式处理。通常，如果您需要查找 null 与 null 匹配的记录，最好的方法是坐下来，把脚放在桌子上，找出根本原因。一旦找到，如果可能，应予以修复。
那么为什么要写这篇文章呢？我在发布之前对此进行了很多思考。一个原因是引发关于 null 相等的讨论，另一个原因是仍然存在这种比较可能合理的情况。一个例子可能是报告应用程序，其中使用了动态条件。在此类应用程序中，null 与任何其他值一样，可能是用户查找记录的有效标准。在这种情况下，此比较可以简化查询构建过程。
长话短说：在使用此功能之前，请仔细考虑情况，如果您仍然确信这是正确的方法，那么希望本文能为您节省一些工作。
在 SQL Server 中实现
由于 SQL Server 没有这种运算符，因此典型解决方案是使用两个单独的条件，例如
…
WHERE Column1 = Column2
OR (Column1 IS NULL AND Column2 IS NULL)
另一种解决方案是创建一个用户定义函数来处理比较。为了管理不同的数据类型，最简单的方法是使用 sql_variant 数据类型作为参数。Sql_variant 可以接收大多数其他数据类型，除了
- 时间戳
- 图像
- ntext
- 文本
- xml
- hierarchyid
- 和 CLR UDT
最简单的形式是，该函数将直接评估操作数或 null 值的相等性，因此函数体看起来像这样
CREATE FUNCTION NullSafeEqual_incomplete(
@left sql_variant, @right sql_variant) RETURNS smallint
AS
BEGIN
DECLARE @conversionDone smallint = 0;
DECLARE @returnValue smallint = 0;
IF (@left IS NULL AND @right IS NULL) BEGIN
SET @returnValue = 1;
SET @conversionDone = 1;
END;
IF (@left IS NULL AND @right IS NOT NULL) BEGIN
SET @returnValue = 0;
SET @conversionDone = 1;
END;
IF (@left IS NOT NULL AND @right IS NULL) BEGIN
SET @returnValue = 0;
SET @conversionDone = 1;
END;
IF (@conversionDone = 0 AND @left = @right) BEGIN
SET @returnValue = 1;
SET @conversionDone = 1;
END;
RETURN (@returnValue);
END;
该函数首先检查两个操作数是否都是 null。如果为真，则返回 1。如果不是，则检查任一参数是否为 null 而另一个不是，最后测试相等性。现在，让我们用一个简单的整数值查询来测试一下
-- Test 1
SELECT dbo.NullSafeEqual_incomplete (1, 1) AS Comparison1,
dbo.NullSafeEqual_incomplete (1, null) AS Comparison2,
dbo.NullSafeEqual_incomplete (null, null) AS Comparison3;
结果是：
Comparison1 Comparison2 Comparison3
----------- ----------- -----------
1 0 1
因此，1 等于 1，null 也等于 null。如果使用其他数据类型或混合数据类型怎么办？
-- Test 2
SELECT dbo.NullSafeEqual_incomplete('A', 'A') AS Comparison1,
dbo.NullSafeEqual_incomplete('A', null) AS Comparison2,
dbo.NullSafeEqual_incomplete(1, '1') AS Comparison3;
结果是
Comparison1 Comparison2 Comparison3
----------- ----------- -----------
1 0 0
其他一切似乎都还可以，但是比较 3 是不正确的。例如，如果在 WHERE 子句中评估条件 1 = '1'，则结果为 true。但是，函数返回了 0。这是因为 sql_variant 值不同。即使它们看起来包含相同的值，但它们是不同的数据类型，也不会发生自动转换。这实际上意味着，如果数据类型混合使用，该函数将无法返回正确的结果。
SQL Server 有一个名为 SQL_VARIANT_PROPERTY 的函数，它返回实际的底层数据类型。为了正确地进行此比较，需要将数据转换为实际数据类型，然后使用适用于实际数据类型的相同规则进行比较。
这意味着每种数据类型都需要单独检查。一种方法是列出不同数据类型的所有组合。因此，该函数可以具有类似以下的逻辑
DECLARE @conversionDone smallint = 0;
DECLARE @returnValue smallint = 0;
DECLARE @leftdatatype nvarchar(100) =
CONVERT(nvarchar, SQL_VARIANT_PROPERTY(@left, 'BaseType'));
DECLARE @rightdatatype nvarchar(100) =
CONVERT(nvarchar, SQL_VARIANT_PROPERTY(@right, 'BaseType'));
IF (@left IS NULL AND @right IS NULL) BEGIN
SET @returnValue = 1;
SET @conversionDone = 1;
END;
IF (@left IS NULL AND @right IS NOT NULL) BEGIN
SET @returnValue = 0;
SET @conversionDone = 1;
END;
IF (@left IS NOT NULL AND @right IS NULL) BEGIN
SET @returnValue = 0;
SET @conversionDone = 1;
END;
IF (@conversionDone = 0 AND @left = @right) BEGIN
SET @returnValue = 1;
SET @conversionDone = 1;
END;
IF (@conversionDone = 0) BEGIN
-- Left operand is int
IF (@conversionDone = 0 AND @leftdatatype = N'int') BEGIN
IF (@conversionDone = 0 AND @rightdatatype = N'binary') BEGIN
IF CONVERT(int, @left) = CONVERT(binary, @right) BEGIN
SET @returnValue = 1;
END;
SET @conversionDone = 1;
END;
IF (@conversionDone = 0 AND @rightdatatype = N'varbinary') BEGIN
IF CONVERT(int, @left) = CONVERT(varbinary, @right) BEGIN
SET @returnValue = 1;
END;
SET @conversionDone = 1;
END;
… etc.
我知道实现不是很有花哨，但实际上很容易生成。
另请注意，函数中并未列出所有可能的数据类型组合，例如，如果两种数据类型之间不允许相等比较。
现在，让我们使用完整的函数运行相同的测试
-- Test 3
SELECT dbo.NullSafeEqual('A', 'A') AS Comparison1,
dbo.NullSafeEqual('A', null) AS Comparison2,
dbo.NullSafeEqual(1, '1') AS Comparison3;
结果是：
Comparison1 Comparison2 Comparison3
----------- ----------- -----------
1 0 1
现在，相等性检查也适用于混合数据类型。
该函数可以接收 sql_variant 的有效参数数据类型，但这些数据类型之间的比较无效，在这种情况下必须引发错误。但是，RAISERROR 不能在函数中使用，因此通过引起如下的强制类型转换错误来克服了这一限制
SET @returnValue = N'Incompatible datatype conversion from ' +
@leftdatatype + N' to ' + @rightdatatype;
让我们测试一下错误处理。如果我们执行以下操作
-- Test 4, incompatible data types
SELECT dbo.NullSafeEqual( convert(money,5), NEWID()) AS Comparison1;
会生成一个错误
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value
'Incompatible datatype conversion from money to uniqueidentifier'
to data type smallint.
运行时错误是从 nvarchar 到 smallint 的转换，但这里的诀窍是，不兼容参数的实际错误消息已嵌入到错误消息中；无法转换：“不兼容的数据类型转换，从 money 到 uniqueidentifier”。
另一种错误是 SQL Server 检测到的转换错误。例如，如果比较 varchar 和 date，则 varchar 将尽可能转换为 date。如果失败，则会发生错误
-- Test 5, wrong format, dates
SELECT dbo.NullSafeEqual(CURRENT_TIMESTAMP, 'This is not a date') AS Comparison1;
运行上述命令后，会返回转换错误
Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.
因此，函数中不需要额外的测试来检查转换本身是否成功。
一些进一步的研究
单表查询
那么如何在 SQL 查询中使用它呢？首先，让我们创建一个小表
CREATE TABLE Table1 (
T1_Col1 int not null identity(1,1),
T1_Col2 int,
T1_Col3 int,
CONSTRAINT pk_Table1 PRIMARY KEY (T1_Col1)
);
以及一些数据
INSERT INTO Table1 (T1_Col2, T1_Col3) VALUES (1, 1);
INSERT INTO Table1 (T1_Col2, T1_Col3) VALUES (1, null);
INSERT INTO Table1 (T1_Col2, T1_Col3) VALUES (null, null);
INSERT INTO Table1 (T1_Col2, T1_Col3) VALUES (2, null);
现在，如果我们想获取 T1_Col2 等于 T1_Col3 的所有行，则查询应如下所示
SELECT *
FROM Table1 t
WHERE dbo.NullSafeEqual(t.T1_Col2, t.T1_Col3) = 1
结果将是
T1_Col1 T1_Col2 T1_Col3
------- ------- -------
1 1 1
3 NULL NULL
因此，找到了两行。
连接
那么连接呢？首先，让我们创建一个子表
CREATE TABLE Table2 (
T2_Col1 int not null identity(1,1),
T2_Col2 int,
CONSTRAINT pk_Table2 PRIMARY KEY (T2_Col1)
);
再次是一些数据
INSERT INTO Table2 (T2_Col2) VALUES (1);
INSERT INTO Table2 (T2_Col2) VALUES (null);
INSERT INTO Table2 (T2_Col2) VALUES (null);
INSERT INTO Table2 (T2_Col2) VALUES (2);
如果我们想基于 T1_Col2 和 T2_Col2 获取这些表中的匹配记录，则查询可以如下所示
SELECT *
FROM Table1 t1,
Table2 t2
WHERE dbo.NullSafeEqual(t1.T1_Col2, t2.T2_Col2) = 1
ORDER BY t1.T1_Col1, t2.T2_Col1;
结果
T1_Col1 T1_Col2 T1_Col3 T2_Col1 T2_Col2
------- ------- ------- ------- -------
1 1 1 1 1
2 1 NULL 1 1
3 NULL NULL 2 NULL
3 NULL NULL 3 NULL
4 2 NULL 4 2
重要的是要注意，笛卡尔积是双向应用的，当使用函数时，null 在此情况下也不会例外。表 2 中的第 1 行重复了两次，因为它有两个父项，而表 1 中的第 3 行也重复了两次，因为它有两个子项。
上面的查询也可以使用标准的 Join 语法编写
SELECT *
FROM Table1 t1 inner join Table2 t2
ON dbo.NullSafeEqual(t1.T1_Col2, t2.T2_Col2) = 1
ORDER BY t1.T1_Col1, t2.T2_Col1;
性能
那么性能呢？如果我们创建另一个测试表
CREATE TABLE Table3 (
T3_Col1 int not null identity(1,1),
T3_Col2 int
CONSTRAINT pk_Table3 PRIMARY KEY (T3_Col1)
);
并用 200,000 行的一些随机数据和几行 null 填充它
SET NOCOUNT ON
DECLARE @counter int = 0;
BEGIN
-- Some nulls
INSERT INTO Table3 (T3_Col2) VALUES (NULL);
INSERT INTO Table3 (T3_Col2) VALUES (NULL);
-- non null data
WHILE (@counter < 200000) BEGIN
INSERT INTO Table3 (T3_Col2)
SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE())) * 100000;
SET @counter = @counter + 1;
END;
-- Some nulls
INSERT INTO Table3 (T3_Col2) VALUES (NULL);
INSERT INTO Table3 (T3_Col2) VALUES (NULL);
END;
让我们看一下一些行
SELECT * FROM Table3;
在我的测试运行中，结果开始如下
T3_Col1 T3_Col2
------- -------
1 NULL
2 NULL
3 52343
4 52343
5 52349
6 52349
7 52349
8 52349
9 52349
10 52362
下一步是在 T3_Col2 上创建索引
CREATE INDEX X_Table3_T3Col2 ON Table3 (T3_Col2);
如果使用传统的 select，例如获取 null 值，则查询应如下所示
SELECT * FROM Table3 WHERE T3_Col2 IS NULL;
但更重要的是，执行计划显示索引用于获取数据
现在，如果使用新创建的函数执行相同的操作
SELECT * FROM Table3 WHERE dbo.NullSafeEqual(T3_Col2, NULL) = 1;
结果相同，但执行计划存在巨大差异
实际上，SQL Server 必须读取所有记录并将 T3_Col2 的值传递给函数，才能知道条件是否为真。这会带来性能损失，因此在使用大量数据时要小心。
历史
- 2011 年 9 月 20 日：创建。
- 2011 年 9 月 20 日：基于与 Mel Padden 的一次良好讨论，添加了“在深入实现之前”章节。