Oracle Differences between NVL and Coalesce

问题:

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?

回答1:

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 <= 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 <= 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.

回答2:

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\'

回答3:

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.

回答4:

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:

1. NVL accepts only 2 arguments whereas COALESCE can take multiple arguments
2. NVL evaluates both the arguments and COALESCE stops at first occurrence of a non-Null value.
3. NVL does a implicit datatype conversion based on the first argument given to it. COALESCE expects all arguments to be of same datatype.
4. COALESCE gives issues in queries which use UNION clauses. Example below
5. 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

回答5:

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;

回答6:

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

回答7:

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) -> \'|| NVL(int_val,string_val) );
    EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(\'1. NVL(int_val,string_val) -> \'||SQLERRM ); 
    END;

    BEGIN
    DBMS_OUTPUT.PUT_LINE( \'2. NVL(string_val, int_val) -> \'|| NVL(string_val, int_val) );
    EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(\'2. NVL(string_val, int_val) -> \'||SQLERRM ); 
    END;

    BEGIN
    DBMS_OUTPUT.PUT_LINE( \'3. COALESCE(int_val,string_val) -> \'|| COALESCE(int_val,string_val) );
    EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(\'3. COALESCE(int_val,string_val) -> \'||SQLERRM ); 
    END;

    BEGIN
    DBMS_OUTPUT.PUT_LINE( \'4. COALESCE(string_val, int_val) -> \'|| COALESCE(string_val, int_val) );
    EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(\'4. COALESCE(string_val, int_val) -> \'||SQLERRM ); 
    END;

    DBMS_OUTPUT.PUT_LINE( \'5. COALESCE(SYSDATE,SYSTIMESTAMP) -> \'|| COALESCE(SYSDATE,SYSTIMESTAMP) );

END;

Output:

1. NVL(int_val,string_val) -> ORA-06502: PL/SQL: numeric or value error: character to number conversion error
2. NVL(string_val, int_val) -> foo
3. COALESCE(int_val,string_val) -> 1
4. COALESCE(string_val, int_val) -> ORA-06502: PL/SQL: numeric or value error: character to number conversion error
5. COALESCE(SYSDATE,SYSTIMESTAMP) -> 2016-11-30 09:55:55.000000 +1:0 --> This is a TIMESTAMP value, not a DATE value!

回答8:

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.