Everybody knows the NVL function, and use it frequently. On the other hand, the COALESCE function, which belongs to the same family of functions, is less known, although it exists since version 9i and has more capabilities than the good old NVL.
Let’s start with the similarities between these functions:
What?
Both functions have a similar purpose – to return the first non-null element from a list of elements. If all the elements in the list are null, the result will be null as well.
Where?
Both functions can be called from SQL and from PL/SQL.
Regarding the differences between the functions – there are two major and significant ones:
How Many?
The first difference is in the number of arguments the functions get.
NVL supports in exactly two arguments. The expression
NVL(a,b)
returns the value of a if it is not null, and the value of b if a is null.
The COALESCE function supports two or more arguments.
Let’s look at the expression
COALESCE(X1, X2, X3,..., Xn)
- If X1 is not null its value is returned
- Otherwise, if X2 is not null its value is returned
- Otherwise, if X3 is not null its value is returned
- And so on…
So if you write sometimes something like
NVL(a,NVL(b,c))
or alternatively
CASE WHEN a IS NOT NULL THEN a WHEN b IS NOT NULL THEN b ELSE c END
then you can write instead
COALESCE(a,b,c)
When?
The second difference is less evident, but very significant in some cases.
For many years I assumed that if I write
NVL(expression1,expression2)
then expression2 will be evaluated only if expression1 is null. It seems logic, doesn’t it? But this is not how it works. in NVL both expressions are always evaluated. In COALESCE, on the other hand, the expressions are evaluated only when needed (and so is the case with DECODE and CASE, by the way).
Let’s see an example where this behavior of NVL is problematic:
In the following query we’d like to return the value of the DESCRIPTION column if it is not null. If the column is empty, we’d like to return the result of some function – get_description – that gets as input the columns A_ID and B_ID.
SELECT NVL(DESCRIPTION, get_description(A_ID,B_ID))
FROM TEST_TABLE;
It doesn’t matter what get_description does exactly. What matters is that every call to the function when DESCRIPTION contains a non-null value is unnecessary, but this is exactly what happens here, and the performance may be hurt significantly.
But if we use COALESCE instead of NVL, we’ll achieve the same functionality, while removing the unnecessary calls. get_description will be called only for records in which the DESCRIPTION column is empty:
SELECT COALESCE(DESCRIPTION, get_description(A_ID,B_ID))
FROM TEST_TABLE;
One advantage NVL does have over COALESCE is the number of characters we need to type.
So if we have exactly two expressions, and both of them are already evaluated, we can continue using NVL. In the other cases, using COALESCE is probably a better choice.