Welcome to a brief stop in the twilight zone of database functions.
If you want to check two (or more) fields and get the value of the first non-null field in Oracle you would use nvl. I needed to do the same thing in SQL Server 2000, which doesn’t have the function nvl (of course). After looking around SQL Server has a function that does the same exact thing called isnull.
By this point I figured I’d better be complete and find out if MySQL had a function called nvl or if they called it something else. If you guessed that it is called something else then you would be right, MySQL calls it ifnull. I was already familiar with the PostgreSQL equivalent which is called coalesce. Unlike all of the other functions mentioned above though, coalesce takes a list of fields/values/expressions instead of just two. Although the name sounds a little strange (nvl just rolls of the tongue so nicely) it certainly made more sense to have a more generalized function that would accept a list. As an additional reference the mysql compatibility project includes a ifnull function for folks porting SQL from MySQL to PostgreSQL.
So do you feel a little more enlightened now? Don’t stop now, it gets even stranger.
I got to thinking more about how much better the coalesce approach seems to be than nvl, isnull and ifnull so I did some more research to see what other databases besides PostgreSQL supported it. Guess what I found out. There is a coalesce in Oracle, a coalesce in SQL Server and a coalesce in MySQL. Huh? Why do folks even bother writing SQL that includes the use of nvl, isnull and ifnull?
That concludes our brief tour through the twilight zone of database functions for now.
9 replies on “NVL, ISNULL, IFNULL and COALESCE”
DB2 also supports COALESCE(…)
You might want to add that to the list.
Good notes. DB2 also uses Coalesce.
In SQL Server, COALESCE() can result in a sub-optimal execution plan, so ISNULL() is better if you only need to test one value for NULL. There are some other differences as well (more info at http://databases.aspfaq.com/database/coalesce-vs-isnull-sql.html)
Joseph,
I’m not sure if you’re aware or not but there is a difference between an NVL and a COALESCE in Oracle. Using an NVL, you check a single field and replace a NULL value with your preferred replacement, for example:
SELECT NVL(dog, 'cat') FROM Animals;In which case, if the dog column contained a NULL value the value of ‘cat’ would be substituted in.
A COALESCE is essentially an inline if-then-elsif-else block. The COALESCE function can accept any number of values and do the replacement, for example:
SELECT COALESCE(dog, 'foxy', 'energetic', 'poodle', 'happy', 'cats win') FROM Animal;In the above example, if the value of the dog column is ‘foxy’, then ‘energetic’ will be substituted in; likewise for ‘poodle’ and ‘happy’. If it isn’t either of those values though, then ‘cats win’ will be substituted in.
Regards,
Al.
Alistair, you are confusing DECODE and COALESCE. What you describe is the DECODE function. COALESCE works in Oracle as in other dbms. It takes the first value from the list which is not null. Hence giving it two args only equals NVL.
Thorsten.
There is one MAJOR HUGE difference you seem to be ignoring.
sql server:
SELECT IFNULL(‘word’, 123)
> word
postgres:
SELECT COALESCE(‘word’, 123)
> invalid input syntax for integer: “woot”
At Potgresql you can do:
SELECT COALESCE(‘word’, cast(123 as varchar))
it will work
You’re confusing COALESCE() with DECODE().
This is an area of bizarre inconsistency across database engines. In PostgreSQL, and in Redshift, the NVL() function is a synonym for COALESCE. In other dialects, like Netezza, the NVL() function is nothing like COALESCE. Its purpose is to REPLACE ALL VALUES OF NULL WITH THE FOLLOWING VALUE. Similar to case x when null then y else x end.
The observation of the various permutations is a failure on the part of the ANSI committee in defining what NVL should mean.