Oracle Differences between NVL and Coalesce

Question

Are there non obvious differences between NVL and Coalesce in Oracle   The obvious differences are that coalesce will return the first non null item in its parameter list whereas nvl only takes two parameters and returns the first if it is not null  otherwise it returns the second   It seems that NVL may just be a  Base Case  version of coalesce   Am I missing something

User · Answer

There is also difference is in plan handling.

Oracle is able form an optimized plan with concatenation of branch filters when search contains comparison of nvl result with an indexed column.

create table tt(a, b) as
select level, mod(level,10)
from dual
connect by level<=1e4;

alter table tt add constraint ix_tt_a primary key(a);
create index ix_tt_b on tt(b);

explain plan for
select * from tt
where a=nvl(:1,a)
  and b=:2;

explain plan for
select * from tt
where a=coalesce(:1,a)
  and b=:2;

nvl:

-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |     2 |    52 |     2   (0)| 00:00:01 |
|   1 |  CONCATENATION                |         |       |       |            |          |
|*  2 |   FILTER                      |         |       |       |            |          |
|*  3 |    TABLE ACCESS BY INDEX ROWID| TT      |     1 |    26 |     1   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IX_TT_B |     7 |       |     1   (0)| 00:00:01 |
|*  5 |   FILTER                      |         |       |       |            |          |
|*  6 |    TABLE ACCESS BY INDEX ROWID| TT      |     1 |    26 |     1   (0)| 00:00:01 |
|*  7 |     INDEX UNIQUE SCAN         | IX_TT_A |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:1 IS NULL)
   3 - filter("A" IS NOT NULL)
   4 - access("B"=TO_NUMBER(:2))
   5 - filter(:1 IS NOT NULL)
   6 - filter("B"=TO_NUMBER(:2))
   7 - access("A"=:1)

coalesce:

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |    26 |     1   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| TT      |     1 |    26 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IX_TT_B |    40 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A"=COALESCE(:1,"A"))
   2 - access("B"=TO_NUMBER(:2))

Credits go to http://www.xt-r.com/2012/03/nvl-coalesce-concatenation.html.

User · Answer

Actually I cannot agree to each statement    COALESCE expects all arguments to be of same datatype    This is wrong  see below  Arguments can be different data types  that is also documented  If all occurrences of expr are numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type  then Oracle Database determines the argument with the highest numeric precedence  implicitly converts the remaining arguments to that data type  and returns that data type   Actually this is even in contradiction to common expression  COALESCE stops at first occurrence of a non-Null value   otherwise test case No  4 should not raise an error   Also according to test case No  5 COALESCE does an implicit conversion of arguments   DECLARE     int val INTEGER    1      string val VARCHAR2 10      foo   BEGIN      BEGIN     DBMS OUTPUT PUT LINE   1  NVL int val string val  - gt      NVL int val string val         EXCEPTION WHEN OTHERS THEN DBMS OUTPUT PUT LINE  1  NVL int val string val  - gt     SQLERRM         END       BEGIN     DBMS OUTPUT PUT LINE   2  NVL string val  int val  - gt      NVL string val  int val         EXCEPTION WHEN OTHERS THEN DBMS OUTPUT PUT LINE  2  NVL string val  int val  - gt     SQLERRM         END       BEGIN     DBMS OUTPUT PUT LINE   3  COALESCE int val string val  - gt      COALESCE int val string val         EXCEPTION WHEN OTHERS THEN DBMS OUTPUT PUT LINE  3  COALESCE int val string val  - gt     SQLERRM         END       BEGIN     DBMS OUTPUT PUT LINE   4  COALESCE string val  int val  - gt      COALESCE string val  int val         EXCEPTION WHEN OTHERS THEN DBMS OUTPUT PUT LINE  4  COALESCE string val  int val  - gt     SQLERRM         END       DBMS OUTPUT PUT LINE   5  COALESCE SYSDATE SYSTIMESTAMP  - gt      COALESCE SYSDATE SYSTIMESTAMP      END    Output   1  NVL int val string val  - gt  ORA-06502  PL SQL  numeric or value error  character to number conversion error 2  NVL string val  int val  - gt  foo 3  COALESCE int val string val  - gt  1 4  COALESCE string val  int val  - gt  ORA-06502  PL SQL  numeric or value error  character to number conversion error 5  COALESCE SYSDATE SYSTIMESTAMP  - gt  2016-11-30 09 55 55 000000  1 0 -- gt  This is a TIMESTAMP value  not a DATE value

User · Answer

COALESCE is more modern function that is a part of ANSI-92 standard   NVL is Oracle specific  it was introduced in 80 s before there were any standards   In case of two values  they are synonyms   However  they are implemented differently   NVL always evaluates both arguments  while COALESCE usually stops evaluation whenever it finds the first non-NULL  there are some exceptions  such as sequence NEXTVAL    SELECT  SUM val  FROM              SELECT  NVL 1  LENGTH RAWTOHEX SYS GUID      AS val         FROM    dual         CONNECT BY                 level  lt   10000             This runs for almost 0 5 seconds  since it generates SYS GUID   s  despite 1 being not a NULL   SELECT  SUM val  FROM              SELECT  COALESCE 1  LENGTH RAWTOHEX SYS GUID      AS val         FROM    dual         CONNECT BY                 level  lt   10000             This understands that 1 is not a NULL and does not evaluate the second argument   SYS GUID s are not generated and the query is instant

User · Answer

NVL will do an implicit conversion to the datatype of the first parameter  so the following does not error  select nvl  a  sysdate  from dual    COALESCE expects consistent datatypes   select coalesce  a  sysdate  from dual    will throw a  inconsistent datatype error

User · Answer

Though this one is obvious  and even mentioned in a way put up by Tom who asked this question  But lets put up again   NVL can have only 2 arguments  Coalesce may have more than 2   select nvl       1  from dual    Result  ORA-00909  invalid number of arguments select coalesce        1   from dual     Output  returns 1

User · Answer

NVL  Replace the null with value   COALESCE  Return the first non-null expression from expression list   Table  PRICE LIST   ---------------- -----------    Purchase Price   Min Price    ---------------- -----------    10               null          20                             50               30            100              80            null             null         ---------------- -----------       Below is the example of   1  Set sales price with adding 10  profit to all products   2  If there is no purchase list price  then the sale price is the minimum price  For clearance sale    3  If there is no minimum price also  then set the sale price as default price  50      SELECT      Purchase Price       Min Price       NVL Purchase Price    Purchase Price   0 10   Min Price     AS NVL Sales Price  COALESCE Purchase Price    Purchase Price   0 10   Min Price 50  AS Coalesce Sales Price FROM  Price List   Explain with real life practical example    ---------------- ----------- ----------------- ----------------------    Purchase Price   Min Price   NVL Sales Price   Coalesce Sales Price    ---------------- ----------- ----------------- ----------------------    10               null        11                                  11     null             20          20                                  20     50               30          55                                  55     100              80          110                                110     null             null        null                                50    ---------------- ----------- ----------------- ----------------------    You can see that with NVL we can achieve rules  1   2   But with COALSECE we can achieve all three rules

User · Answer

Another proof that coalesce   does not stop evaluation with the first non-null value   SELECT COALESCE 1  my sequence nextval  AS answer FROM dual    Run this  then check my sequence currval

User · Answer

NVL and COALESCE are used to achieve the same functionality of providing a default value in case the column returns a NULL   The differences are    NVL accepts only 2 arguments whereas COALESCE can take multiple arguments NVL evaluates both the arguments and COALESCE stops at first occurrence of a non-Null value  NVL does a implicit datatype conversion based on the first argument given to it  COALESCE expects all arguments to be of same datatype  COALESCE gives issues in queries which use UNION clauses  Example below COALESCE is ANSI standard where as NVL is Oracle specific    Examples for the third case  Other cases are simple   select nvl  abc  10  from dual  would work as NVL will do an implicit conversion of numeric 10 to string   select coalesce  abc  10  from dual  will fail with Error - inconsistent datatypes  expected CHAR got NUMBER  Example for UNION use-case  SELECT COALESCE a  sysdate   from  select null as a from dual        union        select null as a from dual            fails with ORA-00932  inconsistent datatypes  expected CHAR got DATE  SELECT NVL a  sysdate   from  select null as a from dual        union        select null as a from dual             succeeds   More information   http   www plsqlinformation com 2016 04 difference-between-nvl-and-coalesce-in-oracle html

[sql] Oracle Differences between NVL and Coalesce

Examples related to sql

Examples related to oracle

Examples related to coalesce

Examples related to nvl