SQL Server ISNULL 不生效原因及解决

在SQL Server中，ISNULL是一种用于判断一个值是否为NULL并返回相应的值的函数。然而，在某些情况下，ISNULL函数可能不生效，本文将介绍SQL Server ISNULL不生效的原因以及如何解决这个问题。

原因分析

ISNULL函数的语法如下所示：

ISNULL(express1,express2)

其中express1是需要判断是否为NULL的表达式，express2是当express1为空值时替代的值。

如果express1不是NULL，那么ISNULL函数将返回express1的原始值，如果express1是NULL，则ISNULL函数将返回express2的值。

然而，有时我们在使用ISNULL函数时可能会发现返回的值与我们预期的不符，问题的原因可能有以下几点：

1. express1不是空值，但还是返回了express2的值

这种情况发生的原因是express1和express2的数据类型不匹配。例如，当我们使用以下查询语句时：

select isnull('','未知')

我们期望的结果是返回一个空字符串，但实际上返回了'未知'。这是因为当我们使用空字符串''作为express1时，它实际上是属于VARCHAR类型的，而'未知'则是NVARCHAR类型。由于两个类型的优先级不同，系统在转换数据类型时会将'未知'转换为VARCHAR类型，因此ISNULL函数返回的就是'未知'。

为了避免这种类型不匹配的问题，我们可以将express2的数据类型与express1保持一致，例如，将上述查询语句改为：

select isnull(''::varchar,'未知')

此时，我们就可以得到预期的结果——空字符串。

2. 返回“NULL”

有时，我们在使用ISNULL函数时会发现返回的值是“NULL”字符串而不是我们期望的结果。这通常发生在当我们使用ISNULL函数嵌套时，例如：

select isnull(isnull(p.Name,''), '未知')

我们期望的结果是当p.Name为NULL时返回“未知”字符串，但实际结果是返回“NULL”字符串。这是因为当p.Name为空时，外层的ISNULL函数实际上将ISNULL(p.Name,'')的结果作为express1进行判断。由于ISNULL(p.Name,'')的结果为NULL，因此最终ISNULL函数返回的就是我们期望的字符串“未知”的NULL值。

为了解决这个问题，我们可以使用COALESCE函数来代替ISNULL函数，例如：

select coalesce(p.Name,'未知')

COALESCE函数可以接受多个参数并从中选择第一个不为NULL的值作为结果返回，因此就可以避免上述问题的发生。

示例说明

示例1

下面是一个示例，它演示了当ISNULL函数中的表达式类型不匹配时会发生的问题。

假设我们有一张Person表，其中Name列是VARCHAR(10)类型的，并且有如下数据：

ID	Name
1	John
2
3	Michael
4	Jane

现在我们想要使用ISNULL函数将Name列中的NULL值替换为“未知”，可以使用以下查询语句：

SELECT ISNULL(Name,'未知') FROM Person

然而，执行这个查询语句后会发现，返回的结果是：

Column1
John
未知
Michael
Jane

我们发现第二行返回的不是空字符串，而是“未知”字符串。这是因为当Name列中的值为NULL时，ISNULL函数将NULL作为表达式的值进行判断，由于“未知”是NVARCHAR类型的字符串，因此系统将NULL转换为NVARCHAR类型的“NULL”字符串。

为了解决这个问题，我们可以将ISNULL函数改为下面这样的形式：

SELECT ISNULL(Name::nvarchar(10),'未知') FROM Person

此时，返回的结果将会是：

Column1
John
Michael
Jane

示例2

下面是另一个示例，它演示了当ISNULL函数嵌套使用时会发生的问题。

假设我们有一张Person表，其中Name列是VARCHAR(10)类型的，并且有如下数据：

ID	Name
1	John
2
3	Michael
4	Jane

现在我们想要使用ISNULL函数将Name列中的NULL值替换为“未知”，并在结果中添加“ID:”前缀。可以使用以下查询语句：

SELECT 'ID: ' + ISNULL(ISNULL(Name,''),'未知') FROM Person

然而，执行这个查询语句后会发现，返回的结果是：

Column1
ID: John
NULL
ID: Michael
ID: Jane

我们发现第二行返回的不是“ID: 未知”字符串，而是NULL值。这是因为当第二行的Name列的值为NULL时，内层的ISNULL函数将返回一个空字符串，由于空字符串不为NULL，外层的ISNULL函数就不会去使用默认值“未知”，因此最终返回的就是NULL。

为了解决这个问题，我们可以使用COALESCE函数代替ISNULL函数，例如：

SELECT 'ID: ' + COALESCE(Name,'未知') FROM Person

此时，返回的结果将会是：

Column1
ID: John
ID: 未知
ID: Michael
ID: Jane

结论

ISNULL函数是SQL Server中一个非常常用的函数，但是当使用不当时它可能会出现一些问题。在使用ISNULL函数时，我们需要注意以下几点：

1. 表达式类型不匹配会导致ISNULL函数不生效，我们需要确保express2的数据类型与express1保持一致；
2. ISNULL函数嵌套使用会导致返回“NULL”字符串而不是我们期望的结果，我们可以使用COALESCE函数来代替ISNULL函数。