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 COALESCE and 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 NULL value:
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 ISNULL:
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 numeric(12,2).
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.
