In Transact-SQL (as well as on T-SQL on Microsoft SQL Server), you can use both ISNULL and COALESCE to use a default in case you have NULL values:
1> select ISNULL(NULL, 123)
2> go
-----------
123
1> select COALESCE(NULL, 123)
2> go
-----------
123
So what are the differences between these two functions:
- COALESCE is ANSI standard. So if you think you might have to port the code to another DBMS, it’s a safer bet.
- ISNULL means something totally different in other DBMS e.g. MySQL. There it returns a boolean value meaning whether the expression is NULL or not. So this might confuse colleagues coming from different DBMS.
- COALESCE is harder to spell… After you’ve mispelled it 20 times you might consider using ISNULL instead !
- COALESCE can do more than ISNULL. You can provide a list of X expressions and it will return the first one which is not NULL. You can of course write something like ISNULL(expression1, ISNULL(expression2, expression3)) but it’s then much more complex to read.
- From a performance point of view COALESCE is converted to a case statement which seems to be a little bit slower than ISNULL which is a system function. But I guess the difference in performance is so small that it shouldn’t be a criterion to go for ISNULL instead of COALESCE.
- The datatype, scale and precision of the return expression is the one of the first expression for ISNULL. With COALESCE it’s more difficult to figure it out since it is determined by datatype hierarchy. You can get the hierarchy with the following query:
select name, hierarchy from systypes order by hierarchy
If you come from Microsoft SQL Server, you should also note that the following does work in SQL Server (recursively concatenating the strings) but will only return the last row in ASE:
declare @triggertext varchar(16384) select @triggertext=coalesce(@triggertext, '')+text from syscomments c, sysobjects o where o.id=c.id and o.name=@trigger_name order by c.colid