ISNULL vs COALESCE in SQL Server
In our T-SQL programming, we mostly use ISNULL function to replace the null value of a column with another value. The same can be achieved using COALESCE function too. In this blog post, we are going to see a significant difference between NULL and COALESCE functions. We are going to discuss about the implicit conversion used by ISNULL and COALESCE functions.
What is COALESCE:
Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.
Thus COALESCE gives a substitute value for NULL values from the given list of columns. It always returns the first NON-NULL from the list.
Syntax : – COALESCE ( expression [ ,…n ] )
COALESCE vs ISNULL
When we use ISNULL to get the substitute value for any NULL value it implicitly type casts the substitute as per the checked column or value type. But the COALESCE does not.
DECLARE @ch_ToCheck CHAR(1) SELECT ISNULL(@ch_ToCheck, 'XYZ') GO
Now from above query we are expecting to get ‘XYZ’ as the value, but it will return ‘X’ and not ‘XYZ’. Because the type of the checked Value is CHAR(1), it will implicitly cast the substitute as CHAR(1).
Now try the same with COALESCE:
DECLARE @ch_ToCheck CHAR(1) SELECT COALESCE(@ch_ToCheck, 'XYZ') GO
And you will get ‘XYZ’.
With the help of above example, i just tried to explain the type casting feature of ISNULLs with COALESCE and I am not saying that you should completely avoid the use of ISNULL . Just keep in your mind this significant difference.