isnull vs is null

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

In a WHERE clause

isnull(name,'') <> name

will always return no results regardless of whether name is null or not.

If name is Null you are doing '' <> Null which is unknown.

If name is not null and contains say Jim you are doing 'Jim' <> 'Jim' which is false.

Edit

Following the clarification to the question note that isnull(name,'') <> '' is equivalent to name is not null and name <> '' which is slightly less terse. The use of this pattern will result in a scan and is less efficient as can be seen in the below test.

SELECT * 
INTO TestTable
FROM
(
SELECT [name]
      ,[number]
      ,[type]
      ,[low]
      ,[high]
      ,[status]
  FROM [master].[dbo].[spt_values]
UNION ALL
SELECT TOP 1 NULL AS [name]
      ,[number]
      ,[type]
      ,[low]
      ,[high]
      ,[status]
  FROM [master].[dbo].[spt_values]
  UNION ALL
SELECT TOP 1 '' AS [name]
      ,[number]
      ,[type]
      ,[low]
      ,[high]
      ,[status]
  FROM [master].[dbo].[spt_values]
) data



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

Which gives

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

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

Answer 7

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

name is not null and name <> '' 

Answer 8

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

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

Answer 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