SQL isnull 与为 null
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3118213/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
isnull vs is null
提问by tgandrews
I have noticed a number of queries at work and on SO are using limitations in the form:
我注意到工作中和 SO 上的许多查询都在使用以下形式的限制：
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?
这是遗留问题还是性能问题？
回答by Martin Smith
where isnull(name,'') <> ''
is equivalent to
相当于
where name is not null and name <> ''
which in turn is equivalent to
这又相当于
where name <> ''
(if name IS NULLthat final expression would evaluate to unknown and the row not returned)
（如果IS NULL最终表达式的名称将计算为未知并且未返回该行）
The use of the ISNULLpattern will result in a scan and is less efficient as can be seen in the below test.
使用该ISNULL模式将导致扫描并且效率较低，如下面的测试所示。
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.
这应该为您提供所需的执行计划。
回答by Justin Niessner
is not null
Will only check if the field is not null. If the field contains an empty string, then the field is no longer null.
只会检查字段是否不为空。如果该字段包含空字符串，则该字段不再为空。
isnull(name, '') <> name
Checks for both a null and an empty string.
检查空字符串和空字符串。
回答by Donnie
isnull(name,'') <> :nameis shorthand for (name is null or name <> :name)(assuming that :namenever contains the empty string, thus why shorthands like this can be bad).
isnull(name,'') <> :name是简写(name is null or name <> :name)（假设:name从不包含空字符串，因此为什么像这样的简写可能很糟糕）。
Performance-wise, it depends. orstatements in whereclauses can give extremely bad performance. However, functions on columns impair index usage. As usual: profile.
性能方面，这取决于。 子句or中的where语句可能会产生极差的性能。但是，列上的函数会影响索引的使用。像往常一样：个人资料。
回答by kemiller2002
isnull(name,'') <> name
Well I can see them using this because this way if the name doesn't match or is null it returns as a failed comparison. This really means: name is nullor name <> name
好吧，我可以看到他们使用这个，因为这样如果名称不匹配或为空，它会作为失败的比较返回。这真的意味着：name is null或name <> name
Where as this one name is not nulljust checks to see if the name is null.
因为这个name is not null只是检查名称是否为空。
回答by Jay
Others have pointed out the functional difference. As to the performance issue, in Postgres I've found that -- oh, I should mention that Postgres has a function "coalesce" that is the equivalent of the "isnull" found in some other SQL dialects -- but in Postgres, saying
其他人指出了功能差异。至于性能问题，在 Postgres 中，我发现——哦，我应该提到 Postgres 有一个函数“coalesce”，它相当于在其他一些 SQL 方言中找到的“isnull”——但在 Postgres 中，说
where coalesce(foobar,'')=''
is significantly faster than
明显快于
where foobar is null or foobar=''
Also, it can be awesomely dramatically faster to say
此外，它可以非常快地说
where foobar>''
over
超过
where foobar!=''
A greater than test can use the index and thus skip over all the blanks, while a not-equal test has to do a full file read. (Assuming you have an index on the field and no other index is used in preference.)
大于测试可以使用索引并因此跳过所有空白，而不相等测试必须执行完整文件读取。（假设您在该字段上有一个索引，并且没有优先使用其他索引。）
回答by HLGEM
They don't mean the same thing.
它们不是同一回事。
name is not null
This checks for records where the name field is null
这将检查名称字段为空的记录
isnull(name,'') <> name
This one changes the value of null fields to the empty string so they can be used in a comparision. In SQL Server (but not in Oracle I think), if a value is null and it is used to compare equlaity or inequality it will not be considered becasue null means I don't know the value and thus is not an actual value. So if you want to make sure the null records are considered when doing the comparision, you need ISNULL or COALESCE(which is the ASCII STANDARD term to use as ISNULL doen't work in all databases).
这将空字段的值更改为空字符串，以便它们可以用于比较。在 SQL Server 中（但我认为不是在 Oracle 中），如果一个值为 null 并且它用于比较相等性或不平等性，则不会将其视为空值，因为 null 意味着我不知道该值，因此不是实际值。因此，如果您想确保在进行比较时考虑空记录，则需要 ISNULL 或 COALESCE（这是使用的 ASCII 标准术语，因为 ISNULL 不适用于所有数据库）。
What you should be looking at is the differnece between
你应该看的是两者之间的区别
isnull(a.name,'') <> b.name
a.name <> b.name
a.name <> b.name
then you will understand why the ISNULL is needed to get correct results.
那么你就会明白为什么需要 ISNULL 来获得正确的结果。
回答by JC Ford
I apparently misread your question. So let me strike my first answer and try this one:
我显然误读了你的问题。所以让我找到我的第一个答案并尝试这个：
isnull(name,'') <> ''
is a misguided shortcut for
是被误导的捷径
name is not null and name <> ''
回答by Madhivanan
Also if you want to make use of the index on that column, use
此外，如果您想使用该列上的索引，请使用
name is not null and name <> ''
回答by A-K
These two queries are not the same. For example, I do not have a middle name, this is a known fact, which can be stored as
这两个查询并不相同。比如我没有中间名，这是一个已知的事实，可以存储为
MiddleName=''
However, if we don't know someone's middle name, we can store NULL. So, ISNULL(MiddleName, '') means "persons without known middle names".
但是，如果我们不知道某人的中间名，我们可以存储 NULL。所以， ISNULL(MiddleName, '') 的意思是“没有已知中间名的人”。
回答by onedaywhen
It is to handle both the empty string and NULL. While it is good to be able to do with with one statement, isnullis proprietary syntax. I would write this using portable Standard SQL as
它是处理空字符串和NULL. 虽然能够用一个语句来处理是件好事，但这isnull是专有语法。我会使用可移植的标准 SQL 将其编写为
NULLIF(name, '') IS NOT NULL
