Oracle Differences between NVL and Coalesce
Solution 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.
Solution 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'
Solution 3
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
Solution 4
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.
Solution 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;
Related videos on Youtube
Tom Hubbard
Updated on February 14, 2020Comments
-
Tom Hubbard over 4 years
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?
-
William Robertson over 6 years
-
-
DanielM over 9 yearsThey are not exactly synonyms... At least you can find a difference in the fact that NVL makes an implicit data type casting if the given values are of different types. So for instance, I was getting an error using COALESCE passing it two NULL values (one explicitly set and the other taken from a column in the database, of type NUMBER), that just disappear by changing the function to NVL.
-
Florin Ghita over 8 yearswhat you say about
NVL(Purchase_Price + (Purchase_Price * 0.10), nvl(Min_Price,50))
. Or about:nvl(NVL(Purchase_Price + (Purchase_Price * 0.10), Min_Price) ,50)
:) -
mathguy over 7 yearsRe: Test 4 contradicts "COALESCE stops evaluation at the first non-null value". I disagree. Test 4 shows that the compiler checks for data type consistency with COALESCE. Stopping at the first non-null value is a runtime issue, not a compile-time issue. At compile time the compiler doesn't know that the third value (say) will be non-null; it insists that the fourth argument be of the right data type too, even if that fourth value will never actually be evaluated.
-
rickyProgrammer over 6 yearswhich is faster, performance wise what should be used? considering thousand of records to load?
-
splashout over 4 yearsI don't think that there is a specific problem with "union" so much it appears Oracle wants to type cast null in you sub-query to a char by default and then you have the same issue listed in your item 3 (mixed data types). If you change it to TO_DATE(NULL) you probably wouldn't get the error (I can't reproduce the error on the version of Oracle I'm using). Otherwise I agree with and appreciate your answer. :-)
-
llepec over 2 yearsYes. It does. I created my_sequence start with 1 increment by 1, then run your query, then selected my_sequence.currval and it return 1.