Why prefer COALESCE over NVL

I prefer using "COALESCE" over "NVL" is some of the scenarios. Last week One of my friend asked me what is the advantage of using "COALESCE" where we can simply use "NVL". I simply gave him the reply from Oracle Docs i.e. NVL lets you replace null (returned as a blank) with a string in the results of a query and COALESCE returns the first non-null expr in the expression list. Oracle Database uses short-circuit evaluation with "COALESCE".

He replied that he knows the difference, he knows that "COALESCE" can take multiple arguments and so on. He was more interested in understanding "short-circuit evaluation" of "COALESCE" and the scenarios where I prefer using "COALESCE" over "NVL".

To make him understand I created following function which takes One second in every execution and simply returns '--null--' and executed 2 very similar queries one with NVL and other with COALESCE.

SQL> create or replace function f_null return varchar2
  2  is
  3  begin
  4     dbms_lock.sleep(1);
  5     return '--null--';
  6  end;
  7  /

Function created.

Following are the queries which I used to demonstrate that NVL evaluates both arguments even if the second argument is not used and COALESCE uses short-circuit evaluation i.e. it only evaluates the arguments only if they are needed.

SQL> select e.empno, e.ename ename, nvl(m.ename,f_null) mname
  2  from scott.emp e, scott.emp m
  3  where e.mgr = m.empno(+);

     EMPNO ENAME      MNAME
---------- ---------- ----------
      7902 FORD       JONES
      7788 SCOTT      JONES
      7900 JAMES      BLAKE
      7844 TURNER     BLAKE
      7654 MARTIN     BLAKE
      7521 WARD       BLAKE
      7499 ALLEN      BLAKE
      7934 MILLER     CLARK
      7876 ADAMS      SCOTT
      7782 CLARK      KING
      7698 BLAKE      KING
      7566 JONES      KING
      7369 SMITH      FORD
      7839 KING       --null--

14 rows selected.

Elapsed: 00:00:14.01

SQL> select e.empno, e.ename ename, coalesce(m.ename,f_null) mname
  2  from scott.emp e, scott.emp m
  3  where e.mgr = m.empno(+);

     EMPNO ENAME      MNAME
---------- ---------- ----------
      7902 FORD       JONES
      7788 SCOTT      JONES
      7900 JAMES      BLAKE
      7844 TURNER     BLAKE
      7654 MARTIN     BLAKE
      7521 WARD       BLAKE
      7499 ALLEN      BLAKE
      7934 MILLER     CLARK
      7876 ADAMS      SCOTT
      7782 CLARK      KING
      7698 BLAKE      KING
      7566 JONES      KING
      7369 SMITH      FORD
      7839 KING       --null--

14 rows selected.

Elapsed: 00:00:01.01

Here we can see easily that the first query with NVL took 14+ seconds, one second for each record even if the "f_null" value was used only in one record. On the contrary as mentioned in Oracle Documentation "COALESCE" uses its "short-circuit evaluation" and "f_null" was called only once and so second query took only One Second. "COALESCE" is certainly use less resources than NVL.

Hope you have enjoyed this little article on "COALESCE" and its big advantage called "short-circuit evaluation". Feedbacks are always welcome :)

Related Posts:
- Why Primary Key Foreign Key Relationship and Join Elimination
- SQL Tuning: Partition Pruning and HASH JOIN - Real Life Example
- Tune Complete Refresh of Materialized View by atomic_refresh=>false parameter of dbms_mview.refresh
- Why my distinct query is not using the Index?
- Avoiding unnecessary function calls to optimize SQL statements
- How to Get Execution Plan and Statistics of Query
- Query optimization tips for Oracle
- Append String to CLOB in Optimized way
- Using TKPROF with simple steps