This blog has been retired, new updates are happening at http://josephscott.org/
5

NVL, ISNULL, IFNULL and COALESCE

Posted on March 15th, 2006 / 5 Comments »

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.

5 Responses to “NVL, ISNULL, IFNULL and COALESCE”

  1. May 23rd, 2006 at 11:26 pm Raimond Verwei

    DB2 also supports COALESCE(…)
    You might want to add that to the list.

  2. November 4th, 2006 at 5:15 pm Jason

    Good notes. DB2 also uses Coalesce.

  3. December 28th, 2006 at 11:08 am Richard Tallent

    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)

  4. February 22nd, 2007 at 5:06 am Alistair

    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.

  5. March 20th, 2007 at 4:20 am Thorsten Kettner

    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.

Tags

Ads