What is COALESCE anyway?
Suppose you have several columns in your database that may contain all
NULL values. Now you want the value of the first column, but if it’s not there, then the value of the second column, if it’s not there, then the value of the third column, and so on… There are voices that claim that
COALESCE is actually like
ISNULL, only different from
ISNULL, which is the ANSI standard. But there are other differences between the two functions that you should get to know today.
How is COALESCE different from ISNULL?
The main difference between
ISNULL is that with
ISNULL, you know the return value from the beginning, because it is the data type of the column you pass as the first argument. Let’s take a look at the example. For this we first create a table with one column in which we insert a
DROP TABLE IF EXISTS #Ninja CREATE TABLE #Ninja ( NinjaHandle varchar(15) null ) INSERT INTO #Ninja (NinjaHandle) VALUES (NULL)
Now we work with this
NULLline. First we replace the
NULL value using
SELECT ISNULL(NinjaHandle, 'T-SQL Ninja') FROM #Ninja
As expected, the result set contains the value “T-SQL Ninja”. But now we modify the query slightly:
SELECT ISNULL(NinjaHandle, 'Brilliantly querying T-SQL Ninja') FROM #Ninja
Now, to our surprise, we only get the value “Brilliantly que”. Why is that? Because
ISNULL returns the value to be inserted as the value of
NinjaHandle, i.e. as a varchar of length 15. If our string is now longer than 15 characters,
ISNULL truncates and returns only the first 15 characters.
This is different if we use
COALESCE. Again, the first query returns the value “T-SQL Ninja”:
SELECT COALESCE(NinjaHandle, 'T-SQL Ninja') FROM #Ninja
This is different with the second query:
SELECT COALESCE(NinjaHandle, 'Brilliantly querying T-SQL Ninja') FROM #Ninja
Here we get the complete string returned, regardless of the type of
NinjaHandle. This means that while
ISNULL guarantees us type security,
COALESCE can only do this to a limited extent. This means that if your application depends on a type security in the return, then you are always on the safe side with
ISNULL, but with
COALESCE you can also get a nasty surprise.
Let’s look at another example that illustrates this problem. First we modify the ninja table and add another column:
ALTER TABLE #Ninja ADD NinjaSkillLevel INT NULL
Now we want to calculate with this column and use it as modifier of a value, for this we divide the value by 2 if no NinjaSkillLevel is available, otherwise by the skill level. Cast in SQL, the two variants we have to do this look like this:
SELECT 5/ISNULL(NinjaSkillLevel, 2.00) ,5/COALESCE(NinjaSkillLevel, 2.00) FROM #Ninja
What happens now is that due to the type safety of
ISNULL, the 2.00 is returned as integer 2, which means that the 5 is divided by the integer 2, here the SQL Server then returns the integer value, so we get the 2 as return value. In the case of
COALESCE the 2.00, which is passed to the function but has a higher accuracy than the NinjaSkillLevel column, so here the SQL Server decides to convert the
NinjaSkillLevel to the datatype of 2.00, i.e. to do an implicit conversion. Thus we divide 5 by a floating-point number, which results in a floating-point number, so here we get the correct value of 2.5 (with some trailing zeros). And indeed, we also find an implicit type conversion in the execution plan:
Here we see two things: on the one hand, that
COALESCE is converted internally to
CASE statements, and on the other hand, that SQL Server implicitly converts to the datatype
However, in addition to the danger of an unexpected return type, the
COALESCE function does have advantages. One of the advantages is that you can pass multiple columns to it, as long as they are type-compatible. Another advantage is that it is also possible to return a
NULL value if all columns contain
NULLvalues. A side effect of this fact is that if you define a derived column with
ISNULL, that column can be used as the primary key, because
ISNULL cannot be NULL and the return value is eligible for SQL Server as the primary key. It’s different with
COALESCE, the return value here could theoretically be
NULL, although you can effectively prevent this with a constant value as the last argument. Therefore, the attempt to define a primary key on a column derived with
COALESCE will fail.
You should not take the message from today’s Dojo that
COALESCE is not type-safe and therefore evil. Rather, you should be aware that unexpected datatypes can be returned here, and that your applications may react to them. Better than avoiding the function is to understand the function and take advantage of its features as necessary.