As it is known, NVL and COALESCE functions are used in order to eliminate NULL values. However, NVL function is much more popular in comparison with the COALESCE. Basically, COALESCE takes two or more arguments and return the first non-null argument. The main difference between these two functions is that if you are using a function or a SELECT statement as 2nd parameter, you should definitely choose COALESCE. Because, NVL always evaluates both parameters under all conditions.
Let’s see this with a simple example.
SQL> CREATE FUNCTION FNC_TEST_1(P_NUM NUMBER)
2 RETURN NUMBER
3 IS
4 BEGIN
5 DBMS_LOCK.SLEEP(1);
6 RETURN P_NUM*2;
7 END;
8 /
Function created.
SQL> set timing on
SQL> select nvl(level, fnc_test_1(level)) from dual connect by level <= 10;
NVL(LEVEL,FNC_TEST_1(LEVEL))
----------------------------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
Elapsed: 00:00:10.11
SQL>
SQL> select coalesce(level, fnc_test_1(level)) from dual connect by level <= 10;
COALESCE(LEVEL,FNC_TEST_1(LEVEL))
---------------------------------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
Elapsed: 00:00:00.11
As it can be seen from the above example, it is more advantageous to use COALESCE in such cases. I hope this helps 🙂