isnull 与为 null [英] isnull vs is null

问题描述

我注意到工作中和 SO 上的许多查询都在使用以下形式的限制:

I have noticed a number of queries at work and on SO are using limitations in the form:

isnull(name,'') <> ''

人们这样做有什么特别的原因而不是更简洁的原因

Is there a particular reason why people do that and not the more terse

name is not null

这是遗留问题还是性能问题?

Is it a legacy or a performance issue?

推荐答案

where isnull(name,'') <> ''

相当于

where name is not null and name <> '' 

又等价于

where name <> ''

(如果 name IS NULL 最终表达式将评估为未知并且未返回行)

(if name IS NULL that final expression would evaluate to unknown and the row not returned)

使用 ISNULL 模式将导致扫描并且效率较低，如下面的测试所示.

The use of the ISNULL pattern will result in a scan and is less efficient as can be seen in the below test.

SELECT ca.[name],
       [number],
       [type],
       [low],
       [high],
       [status]
INTO   TestTable
FROM   [master].[dbo].[spt_values]
       CROSS APPLY (SELECT [name]
                    UNION ALL
                    SELECT ''
                    UNION ALL
                    SELECT NULL) ca 


CREATE NONCLUSTERED INDEX IX_TestTable ON dbo.TestTable(name)

GO


SELECT name FROM TestTable WHERE isnull(name,'') <> ''

SELECT name FROM TestTable WHERE name is not null and name <> ''
/*Can be simplified to just WHERE name <> '' */

这应该为您提供所需的执行计划.

Which should give you the execution plan you need.

这篇关于isnull 与为 null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！