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?
Possibly related:
- NVL2 in Oracle
- Bye Bye 2006, Welcome 2007
- Cool Undocumented SQL Function SYS_OP_MAP_NONNULL and Some Alternatives
- I’m upgraded and updated
- Here’s a Quick Way to Get the Line Number in PL/SQL
Tagged function, null, sql | Post a Comment
Hope everything is working out...
(maybe more?)
select decode(x,0,null,x) from t;
is
select nullif(x,0) from t;
January 27th, 2006, at 7:20 am #Hey guys!
Karl
January 27th, 2006, at 7:51 am #be honest - i don not beleave that 60% used coalesce?
i always used nvl- the classic NULL -killer
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.
January 27th, 2006, at 9:12 am #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.
January 27th, 2006, at 2:16 pm #Robert,
January 27th, 2006, at 2:53 pm #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 0January 27th, 2006, at 3:46 pm #how to use nvl function and insert statement together… or… usage of nvl is limited to select statements.. please help
September 28th, 2006, at 11:47 pm #No, nvl is not limited to select statements.
October 5th, 2006, at 9:59 pm #For example: insert into t (comm) values (nvl(mycomm, 0))