A Quick Note on COALESCE and ISNULL

I’ve noticed couple of commentators on my blog post on Entity Framework and setting primary keys on views pointing out the difference between COALESCE and ISNULL. Apparently they were using COALESCE and expecting it to return non-nullable value, but instead, got the different result. I would like to clarify this a bit further.

In my example, I’ve been using ISNULL. It is a function which returns some replacement value in case an expression equals NULL. This basically means that (given the replacement value is not NULL) return value will always be non-nullable.

On the other hand, COALESCE returns the first expression which is not NULL. Only if all expressions are non-nullable, the result is considered non-nullable. Here’s the explanation from msdn site:

The NULLability of the result expression is different for ISNULL and COALESCE. The ISNULL return value is always considered NOT NULLable (assuming the return value is a non-nullable one) whereas COALESCE with non-null parameters is considered to be NULL. So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1) although equivalent have different nullability values.

To demonstrate this, here’s a quick example. I’ve created a view extracting a column (of bit type, nullable), using ISNULL, COALESCE and NULLIF (I’ve added the last one since it was used in my previously mentioned post).

CREATE VIEW test1
AS
SELECT ISNULL([Activated],1) AS Col1,
COALESCE([Activated],1) AS Col2,
NULLIF([Activated],1) AS Col3
FROM [User]

The resulting view:

test1

As expected, ISNULL returns non-nullable column, unlike COALESCE and NULLIF.

Also worth noting: ISNULL and NULLIF return type matches the column type, while COALESCE returns the data type of expression with the highest data type precedence.

Another note: I’m not familiar with Oracle, so can’t give an advice on which function to use instead of ISNULL. If you have an idea on that, feel free to comment this post.

Post a comment

You may use the following HTML:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>