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?
<br/>
Related articles:
- NVL2 in Oracle
- Cool Undocumented SQL Function SYS_OP_MAP_NONNULL and Some Alternatives
- Different ways to count
(maybe more?)
select decode(x,0,null,x) from t;
is
select nullif(x,0) from t;
Hey guys! be honest – i don not beleave that 60% used coalesce? i always used nvl- the classic NULL -killer
Karl
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.
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.
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
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 nameThe 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 0how to use nvl function and insert statement together… or… usage of nvl is limited to select statements.. please help
No, nvl is not limited to select statements. For example: insert into t (comm) values (nvl(mycomm, 0))