isnull vs is null

问题:

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?

回答1:

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

is equivalent to

where name is not null and name <> '' 

which in turn is equivalent to

where name <> ''

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

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.

回答2:

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.

回答3:

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

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

回答4:

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 null or name <> name

Where as this one name is not null just checks to see if the name is null.

回答5:

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

What you should be looking at is the differnece between

isnull(a.name,'') <> b.name  

a.name <> b.name

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

回答6:

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 <> ''

回答7:

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

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

回答8:

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

name is not null and name <> '' 

回答9:

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

回答10:

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

NULLIF(name, '') IS NOT NULL