blocks|key|999652|text|where+isnull(name,'')+<>+''|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|999653|等同于|unstyled|999654|where+name+is+not+null+and+name+<>+''+|999655|，它又相当于|999656|where+name+<>+''|999657|(如果命名为IS+NULL，则最终表达式将计算为unknown，且不返回行)|offset|length|style|CODE|999658|使用ISNULL模式将导致扫描，并且效率较低，如下面的测试所示。|999659|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+<>+''+*/|999660|这将为您提供所需的执行计划。|999661|​|999662|📷|atomic|999663|999664|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/c064956ff12dba2038d47283bd5f59b6.png|imageAlt^0|0|0|0|0|0|6|7|0|2|6|0|0|0|0|0|1|0|0|0^^$0|@$1|2|3|4|5|6|7|1D|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|1E|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|1F|8|@]|9|@]|A|$B|C]]|$1|I|3|J|5|F|7|1G|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|1H|8|@]|9|@]|A|$B|C]]|$1|M|3|N|5|F|7|1I|8|@$O|1J|P|1K|Q|R]]|9|@]|A|$]]|$1|S|3|T|5|F|7|1L|8|@$O|1M|P|1N|Q|R]]|9|@]|A|$]]|$1|U|3|V|5|6|7|1O|8|@]|9|@]|A|$B|C]]|$1|W|3|X|5|F|7|1P|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|F|7|1Q|8|@]|9|@]|A|$]]|$1|10|3|11|5|12|7|1R|8|@]|9|@$O|1S|P|1T|1|1U]]|A|$]]|$1|13|3|Z|5|F|7|1V|8|@]|9|@]|A|$]]|$1|14|3|-4|5|F|7|1W|8|@]|9|@]|A|$]]]|15|$16|$5|17|18|19|A|$1A|1B|1C|-4]]]]

<pre><code>where isnull(name,'') &lt;&gt; ''
</code></pre>

is equivalent to 

<pre><code>where name is not null and name &lt;&gt; '' 
</code></pre>

which in turn is equivalent to 

<pre><code>where name &lt;&gt; ''
</code></pre>

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

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

<pre><code>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,'') &lt;&gt; ''

SELECT name FROM TestTable WHERE name is not null and name &lt;&gt; ''
/*Can be simplified to just WHERE name &lt;&gt; '' */
</code></pre>

Which should give you the execution plan you need.

<a href="https://i.stack.imgur.com/rHCvE.png" rel="noreferrer"><img src="https://i.stack.imgur.com/rHCvE.png" alt="enter image description here"></a>

blocks|key|1213814|text|is+not+null|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1213815|将只检查该字段是否不为空。如果该字段包含空字符串，则该字段不再为null。|unstyled|1213816|isnull(name,+'')+<>+name|1213817|同时检查null和空字符串。|1213818|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|N|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$B|C]]|$1|I|3|J|5|F|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|F|7|Q|8|@]|9|@]|A|$]]]|L|$]]

<pre><code>is not null
</code></pre>

Will only check if the field is not null. If the field contains an empty string, then the field is no longer null.

<pre><code>isnull(name, '') &lt;&gt; name
</code></pre>

Checks for both a null and an empty string.

blocks|key|1213742|text|isnull(name,'')+<>+:name是(name+is+null+or+name+<>+:name)的缩写(假设:name从不包含空字符串，因此像这样的缩写可能是不好的)。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1213743|在性能方面，这取决于。where子句中的or语句会带来极差的性能。但是，列上的函数会影响索引的使用。像往常一样:配置文件。|1213744|entityMap^0|0|O|P|V|1Q|5|0|B|5|K|2|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]|$9|M|A|N|B|C]|$9|O|A|P|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|Q|8|@$9|R|A|S|B|C]|$9|T|A|U|B|C]]|D|@]|E|$]]|$1|H|3|-4|5|6|7|V|8|@]|D|@]|E|$]]]|I|$]]

<code>isnull(name,'') &lt;&gt; :name</code> is shorthand for <code>(name is null or name &lt;&gt; :name)</code> (assuming that <code>:name</code> never contains the empty string, thus why shorthands like this can be bad).

Performance-wise, it depends. <code>or</code> statements in <code>where</code> clauses can give extremely bad performance. However, functions on columns impair index usage. As usual: profile.

blocks|key|1213801|text|isnull(name,'')+<>+name|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1213802|我可以看到他们在使用这个，因为在这种情况下，如果名称不匹配或为空，它将作为失败的比较返回。这实际上意味着：name+is+null或name+<>+name|unstyled|offset|length|style|CODE|1213803|在这里，name+is+not+null只检查名称是否为空。|1213804|entityMap^0|0|1H|C|1U|C|0|4|G|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|P|8|@$G|Q|H|R|I|J]|$G|S|H|T|I|J]]|9|@]|A|$]]|$1|K|3|L|5|F|7|U|8|@$G|V|H|W|I|J]]|9|@]|A|$]]|$1|M|3|-4|5|F|7|X|8|@]|9|@]|A|$]]]|N|$]]

<pre><code>isnull(name,'') &lt;&gt; name
</code></pre>

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: <code>name is null</code> or <code>name &lt;&gt; name</code> 

Where as this one <code>name is not null</code> just checks to see if the name is null.

blocks|key|1213944|text|它们的意思不是一样的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1213945|name+is+not+null+|code-block|syntax|javascript|1213946|这将检查名称字段为空的记录|1213947|isnull(name,'')+<>+name++|1213948|它将null字段的值更改为空字符串，以便可以在比较中使用它们。在SQL+Server中(但我认为不是在Oracle中)，如果一个值为null，并且用于比较等式或不等式，则不会被考虑，因为null表示我不知道该值，因此不是一个实际值。因此，如果您想确保在进行比较时考虑到null记录，则需要ISNULL或COALESCE(这是ASCII标准术语，因为ISNULL并不适用于所有数据库)。|1213949|您应该关注的是|1213950|isnull(a.name,'')+<>+b.name++|1213951|a.name+<>+b.name|1213952|然后您就会理解为什么需要ISNULL来获得正确的结果。|1213953|entityMap^0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|10|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|11|8|@]|9|@]|A|$]]|$1|O|3|P|5|D|7|12|8|@]|9|@]|A|$E|F]]|$1|Q|3|R|5|6|7|13|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|14|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|V|$]]

They don't mean the same thing.

<pre><code>name is not null 
</code></pre>

This checks for records where the name field is null

<pre><code>isnull(name,'') &lt;&gt; name 
</code></pre>

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). 

What you should be looking at is the differnece between 

<pre><code>isnull(a.name,'') &lt;&gt; b.name 
</code></pre>

a.name &lt;> b.name 

then you will understand why the ISNULL is needed to get correct results.

blocks|key|1214001|text|我显然误解了你的问题。因此，让我尝试一下我的第一个答案：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1214002|isnull(name,'')+<>+''|code-block|syntax|javascript|1214003|是一个误入歧途的快捷键|1214004|name+is+not+null+and+name+<>+''|1214005|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

I apparently misread your question. So let me strike my first answer and try this one:

<pre><code>isnull(name,'') &lt;&gt; ''
</code></pre>

is a misguided shortcut for

<pre><code>name is not null and name &lt;&gt; ''
</code></pre>

blocks|key|999867|text|其他人则指出了功能上的差异。至于性能问题，在Postgres中我发现--哦，我应该提一下Postgres有一个函数"coalesce“，它等同于在一些其他SQL方言中找到的"isnull”--但是在Postgres中，|type|unstyled|depth|inlineStyleRanges|entityRanges|data|999868|where+coalesce(foobar,'')=''|code-block|syntax|javascript|999869|明显快于|999870|where+foobar+is+null+or+foobar=''|999871|此外，它可以惊人地更快地说|999872|+where+foobar>''|999873|结束|999874|where+foobar!=''|999875|大于测试可以使用索引，从而跳过所有的空白，而不相等的测试必须执行完整的文件读取。(假设您在字段上有一个索引，并且没有优先使用其他索引。)|999876|entityMap^0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|10|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|12|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|S|3|T|5|6|7|14|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|V|$]]

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

<pre><code>where coalesce(foobar,'')=''
</code></pre>

is significantly faster than

<pre><code>where foobar is null or foobar=''
</code></pre>

Also, it can be awesomely dramatically faster to say

<pre><code> where foobar&gt;''
</code></pre>

over

<pre><code>where foobar!=''
</code></pre>

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.)

blocks|key|999796|text|另外，如果要使用该列上的索引，请使用|type|unstyled|depth|inlineStyleRanges|entityRanges|data|999797|name+is+not+null+and+name+<>+''+|code-block|syntax|javascript|999798|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Also if you want to make use of the index on that column, use

<pre><code>name is not null and name &lt;&gt; '' 
</code></pre>

blocks|key|999923|text|这两个查询是不同的。例如，我没有中间名，这是一个已知事实，可以存储为|type|unstyled|depth|inlineStyleRanges|entityRanges|data|999924|MiddleName=''|code-block|syntax|javascript|999925|但是，如果我们不知道某人的中间名，我们可以存储NULL。因此，ISNULL(MiddleName，'')的意思是“没有已知中间名的人”。|999926|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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 

<pre><code>MiddleName=''
</code></pre>

However, if we don't know someone's middle name, we can store NULL.
So, ISNULL(MiddleName, '') means "persons without known middle names".

blocks|key|1214267|text|它将同时处理空字符串和NULL。虽然使用一条语句就可以了，但isnull是专有的语法。我将使用可移植的标准SQL将其编写为|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1214268|NULLIF(name,+'')+IS+NOT+NULL|code-block|syntax|javascript|1214269|entityMap^0|B|4|U|6|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|S|8|@]|D|@]|E|$]]]|L|$]]

It is to handle both the empty string and <code>NULL</code>. While it is good to be able to do with with one statement, <code>isnull</code> is proprietary syntax. I would write this using portable Standard SQL as

<pre><code>NULLIF(name, '') IS NOT NULL
</code></pre>

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

<pre><code>isnull(name,'') &lt;&gt; ''
</code></pre>

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

<pre><code>name is not null
</code></pre>

Is it a legacy or a performance issue?

isnull vs is null

SQL Server

数据库

我注意到在工作中有许多查询，所以在表单中使用限制：isnull(name,'') <> ''有没有什么特别的原因让人们这样做，而不是更简洁name is not null这是遗留问题还是性能问题？

问isnull vs is null
EN

回答 10

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问isnull vs is nullEN

回答 10

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问isnull vs is null
EN