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

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

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

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

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 lt  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

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

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

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

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

[sql] Oracle Differences between NVL and Coalesce

Examples related to sql

Examples related to oracle

Examples related to coalesce

Examples related to nvl