What? Really! Just Use ISNULL…

I hear this all the time (especially from developers that are new to SQL). Here is a story about George and how Jim changed his life forever.

It was a normal day in the office. George is head down busy writing some code that makes use of data from SQL when he stops dead in his tracks. Some of his values are coming back as NULL and he feels there must be a better way of setting a default value at the source of the data rather than in his code. George is fairly new at SQL and is just learning how to modify select statements.

“How do I stop a select from returning NULL values in SQL?” asks George.

Bob, a seasoned developer, looks up and over the top of his monitor and replies confidently: “Just use ISNULL.”

And just like that, George has just picked up another bad habit born from a lack of understanding.

The Challenge:

So Jim, the resident SQL expert, sits quietly in the corner trying to ignore the mumblings of his fellow co-workers, when he hears something, something that he just can’t let slide: An incorrect statement about SQL.

Like a red rag to a bull, the inner beast surfaces from within and with it he makes his stand…

“Shouldn’t you use COALESCE instead?” he says in a voice just loud enough to be heard.

The room goes quiet. Bob looks round and realises Jim has spoken.

“It’s definitely ISNULL to replace a NULL with another value. I use it all the time.” Bob states, albeit a little less confidently that 20 seconds ago.

Jim is not having it. “There are actually two ways of doing that, ISNULL and COALESCE, and both do it differently. You need to know which one to use depending on the result required.”

Bob is not feeling the urge to challenge Jim on this matter. After all, Jim is the SQL guy and they must pay him for something.

George looks Jim, then at Bob and then at Jim again. Should he side with his fellow dev or should he take a lesson from the quiet guy in the corner.

“What’s COALESCE?” asks George, his curiosity getting the better of him.

The Lesson:

Jim swivels his chair around to face them both. “It all depends on the data and type you expect back.”

And with that, Jim begins:

Firstly we should look at what is standard. When we say standard, we mean what is SQL and what is TSQL. COALESCE is standard SQL and can be used on other standard SQL platforms. ISNULL however is more of a TSQL thing. As a general rule, if COALESCE will do what you need then get into the habit of using it. If you ever end up on foreign ground then COALESCE will be familiar to you.

Secondly, ISNULL is limited to replacing one value with another if the first is NULL. COALESCE on the other hand will allow you to pass through multiple parameters and will return the first one it finds that is not NULL. This is much easier on the eye than a bag full of nested ISNULL calls.
Thirdly, and this is the most important one, COALESCE and ISNULL have a different decision making process when it comes to what data type gets returned.

With ISNULL, the returning data type is decided based on the data type of the first parameter. For example:

The above statement may look perfectly fine. If the variable @text is NULL then return '1234567890'. However, you would be wrong. The first data type passed to the ISNULL function is of type VARCHAR(5), so the returning data type will be the same; resulting in '12345'. No errors are thrown and no warnings of data being truncated are presented. It may be that this is what you are after, but it is more likely that this is going to give you pain further down the line. If you think it’s a pain when your string gets truncated, imagine what it will be like when your numbers get rounded (especially in nested statements where the result is being used as part of an aggregate function – debug nightmare).

COALESCE does it slightly different by using data type precedence. For example, if we update our previous statement to:

We see that our result is no longer being truncated (column 2). What is interesting here is that the first parameter value returned has a data type of VARCHAR(7) but the COALESCE function has actually returned a data type of VARCHAR(10) (column 3). This is because the COALESCE function has inspected all the data types that have been passed and has opted to use the one with the highest precedence. This could have been the data type of the @text variable. Increase the size of the @text variable to 50 and have a look at the result. This way you are sure that you will get the correct data rather than a truncated version. This really takes affect when you are using numbers due to FLOATs and DECIMALs taking higher precedence over BITs and INTs.

“Does that make sense George?”

“George…” 

Jim looks at George and waits patiently while he processes what has been said.

The Outcome:

From this, George has learned something new. He has gained a new friend – a new source of knowledge for future SQL related questions. 

George realises that there is a whole new world out there, outside of his development cubical, with many new SQLey wonders. He must pass on his new found knowledge to others and make the world a better place for it and with that packs up his things, sells his life on eBay and travels the earth on a vow to teach the difference between ISNULL and COALESCE to all those that will listen.