msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

NVL, NVL2 or COALESCE?

In Oracle, as in other RDBMSs, nulls should be given a special treatment. In Oracle, there are three (maybe more?) functions that deal with nulls, NVL, NVL2 and COALESCE. I would like to ask you a question, but first, let me quickly refresh my memory:

NVL ( expr1 , expr2 )
If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1.

NVL2 ( expr1 , expr2 , expr3 )
If expr1 is null, then NVL2 returns expr3. If expr1 is not null, then NVL2 returns expr2.

COALESCE(expr[,expr]…)
Returns the first non-null expr in the expression list.

I can also use CASE and DECODE to deal with nulls.

All of the following queries return the same result:

Using NVL:

HR@XE> select nvl(commission_pct,0)
  2  from employees
  3  where commission_pct is null
  4  and rownum = 1;

NVL(COMMISSION_PCT,0)
---------------------
                    0

Using NVL2:

HR@XE> select nvl2(commission_pct,commission_pct,0)
  2  from employees
  3  where commission_pct is null
  4  and rownum = 1;

NVL2(COMMISSION_PCT,COMMISSION_PCT,0)
-------------------------------------
                                    0

Using COALESCE:

HR@XE> select COALESCE(commission_pct,0)
  2  from employees
  3  where commission_pct is null
  4  and rownum = 1;

COALESCE(COMMISSION_PCT,0)
--------------------------
                         0

Using CASE:

HR@XE> select
  2      case
  3      when commission_pct is null then 0
  4      else commission_pct
  5      end commission_pct
  6  from employees
  7  where commission_pct is null
  8  and rownum = 1;

COMMISSION_PCT
--------------
             0

Using DECODE:

HR@XE> select
  2     decode(commission_pct,null, 0,
  3      commission_pct) commission_pct
  4  from employees
  5  where commission_pct is null
  6  and rownum = 1;

COMMISSION_PCT
--------------
             0                        

I have always used NVL to check for null values. However, it looks like COALESCE is more generic (can check more than one expression for null) and also COALESCE is a standard and works for all RDBMS’s.

So should I break my habit of using NVL and start using COALESCE? What say you?


Filed in Oracle on 27 Jan 06 | Tags: , ,


Reader's Comments

  1. |

    (maybe more?)

    select decode(x,0,null,x) from t;

    is

    select nullif(x,0) from t;

  2. |

    Hey guys! be honest – i don not beleave that 60% used coalesce? i always used nvl- the classic NULL -killer :-) Karl

  3. |

    I remember reading recently (on Ask Tom I believe) that Nvl() has the disadvantage that expr2 is always evaluated even when expr1 is not null, which can be expensive if expr2 is non-trivial — a PL/SQL function call for example.

    That makes Coalesce the winner for me.

  4. |

    I believe this is the article to which David is referring:

    http://www.oracledba.co.uk/tips/plsql_nvl_costs.htm

    I remember there being another, but I can’t find it.

    For the record, I use coalesce in Sybase, but in Oracle that’s the one I don’t use. Don’t know why. Probably DECODE is the one I use the most.

  5. |

    Robert, I think that the difference in perf mentionned on donald’s site (oracledba) is mostly due to the operations used in the tests other than the first one (e.g. nvl(c,to_date(‘0001′,’yyyy’)) != nvl(d,to_date(‘0001′,’yyyy’)) where the to_date costs a lot). If you workaround these, then the timing differences are much less important. rgds

  6. |

    In certain situations in 8i, COALESCE did not behave the same as NVL, in fact it errored out. An example:

    user@8i> create table t (id int, f blob)
      2  /
    
    Table created.
    
    user@8i> insert into t (id) values (1)
      2  /
    
    1 row created.
    
    user@8i> select
      2      id
      3      ,dbms_lob.getlength(f)
      4      ,nvl(dbms_lob.getlength(f),0)
      5  from t
      6  /
    
            ID DBMS_LOB.GETLENGTH(F) NVL(DBMS_LOB.GETLENGTH(F),0)
    ---------- --------------------- ----------------------------
             1                                                  0
    
    user@8i> select
      2      id
      3      ,dbms_lob.getlength(f)
      4      ,coalesce(dbms_lob.getlength(f),0)
      5  from t
      6  /
        ,coalesce(dbms_lob.getlength(f),0)
         *
    ERROR at line 4:
    ORA-00904: invalid column name
    

    The above worked in 9i (and XE)

    user@9i> create table t (id int, f blob)
      2  /
    
    Table created.
    
    user@9i> insert into t (id) values (1)
      2  /
    
    1 row created.
    
    user@9i> select
      2      id
      3      ,dbms_lob.getlength(f)
      4      ,nvl(dbms_lob.getlength(f),0)
      5  from t
      6  /
    
            ID DBMS_LOB.GETLENGTH(F) NVL(DBMS_LOB.GETLENGTH(F),0)
    ---------- --------------------- ----------------------------
             1                                                  0
    
    user@9i> select
      2      id
      3      ,dbms_lob.getlength(f)
      4      ,coalesce(dbms_lob.getlength(f),0)
      5  from t
      6  /
    
            ID DBMS_LOB.GETLENGTH(F) COALESCE(DBMS_LOB.GETLENGTH(F),0)
    ---------- --------------------- ---------------------------------
             1                                                       0
    
    
  7. |

    how to use nvl function and insert statement together… or… usage of nvl is limited to select statements.. please help

  8. |

    No, nvl is not limited to select statements. For example: insert into t (comm) values (nvl(mycomm, 0))