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?


Create polls and vote for free. dPolls.com


Possibly related:


Tagged , , | Post a Comment