NVL()

Here’s a point that’s probably fairly well-known, but worth repeating: nvl() and coalesce() aren’t identical in behaviour but you may have some cases where you’re using nvl() when coalesce() would be a more efficient option.

The reason for this is “short-circuiting”. The expression nvl(expr1, expr2) will return expr2 if expr1 is null, otherwise it will return expr1; the expression coalesce(expr1, expr2, …, exprN) will return the first non-null expression in the list so, in particular, coalesce(expr1, expr2) will give the same result as nvl(expr1, expr2) ; the big difference is that nvl() will evaluate both expressions while coalesce will evaluate expr2 only if it needs to (i.e. only if expr1 evaluates to null). In many cases the difference in performance will be insignificant – but consider the following type of construct (t1 is a table with a single, numeric, column n1 and a single row):

rem
rem     Script:         nvl_coalesce.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Dec 2013
rem

create table t1 as select 1 n1 from dual;
execute dbms_stats.gather_table_stats(user,'t1')

select
	nvl(n1, (select max(object_id) from all_objects))	nn
from
	t1
;

select
	coalesce(n1, (select max(object_id) from all_objects))	nn
from
	t1
;

In the first query Oracle will execute the inline scalar subquery whether n1 is null or not.
In the second query Oracle will execute the inline scalar subquery only if n1 is null.
I know which option I would prefer to use if I knew that n1 could be null.

Footnote:

There is a trap that you have to watch out for – try recreating t1 with n1 defined as a varchar2() column and the query with coalesce() will fail with Oracle error ORA-00932: inconsistent datatypes: expected CHAR got NUMBER. The expressions that appear in the coalesce() must all be explicitly of the same type while nvl() will do implicit conversions when necessary, so be a little careful with the code when you’re looking for opportunities to make the change.

Note: the same difference (strictness of typing) appears when you compare decode() – which does implicit conversion when necessary – with case end – which does not. (Both case and decode() short-circuit, though).

Footnote 2:

On the plus side for the nvl() construct – Oracle has a special optimisation that allows a single occurrence of a particular type of predicate based on nvl() to split your query into a concatenation of two pieces that might allow the query to run more efficiently.