Wednesday, July 4, 2012

Oracle NVL vs COALESCE

Now I try to accumulate all my knowledge about these similar functions.
NVL is native Oracle function whereas COALESCE is ANSI SQL one.

SQL Syntax

SELECT NVL(expr1, expr2) FROM some_table;
SELECT COALESCE(expr1, expr2, ..., exprN) FROM some_table;

Where expr can be table's column, subselect, function or constant value.

PL/SQL Syntax

v := NVL(expr1, expr2);
v := COALESCE(expr1, expr2, ..., exprN);

Where expr can be function or constant value.

Common features

  1. Both returns first non-null value from input list of values
  2. Both returns null if all values in input list are nulls.
  3. Both can be used in SQL and in PL/SQL

Differences

Quantity of arguments

NVL can take two arguments only, COALESCE - more than two. If you want to use nvl with more than two arguments you have to use nested calling of NVL.

Returned datatype, implicit convertation

NVL imlicity tries to convert datatype of value2 to datatype of value1 and return this datatype. If the convertation is impossible the exception will be raised.

Datatypes of all arguments in COALESCE should be consistent. If you try to input CHAR and NUMERIC arguments it will be cause of exception. But at the same time arguments with INTEGER and NUMBER datatype will be taken without problem.

And as remark concerning COALESCE function. If all arguments have numeric datatype then the argument with the highest numeric precedence is determined,  the remaining arguments are implicitly converted to that datatype, and that datatype is returned.

As a small example I created test_table
CREATE TABLE test_table (
  varchar_column  VARCHAR2(32)
, date_column     DATE
, integer_column  INTEGER
, number_column   NUMBER
);

For empty table all these calling of NVL are executed successfully.
SELECT NVL(varchar_column, number_column) FROM test_table;
SELECT NVL(number_column, varchar_column) FROM test_table;
SELECT NVL(number_column, integer_column) FROM test_table;
SELECT NVL(integer_column, number_column) FROM test_table;

Oracle Database supposes that can convert the second argument to datatype of the first one. Problems will appear when real data in a table cann't be converted implicity.

For COALESCE function the picture is a bit different.
SELECT COALESCE(varchar_column, number_column) FROM test_table;
SELECT COALESCE(number_column, varchar_column) FROM test_table;
SELECT COALESCE(number_column, integer_column) FROM test_table;
SELECT COALESCE(integer_column, number_column) FROM test_table;

Only the third and the fourth cases are executed without errors. Others raise ORA-00932: inconsistent datatypes. Because datatype of all arguments is checked on compatibility.

Evaluation of arguments

NVL function evaluates both arguments despite the second argument should be used or not.

If you run the following
SELECT NVL(1, 1/0) FROM dual;
you will see ORA-01476: division by zero.

So the first argument (1) is not null and the second argument (1/0) should not be returned. But it is evaluated in any way.

COALESCE function evaluates its argument if it's really necessary. In other words it evaluates exprK only in case if expr(K-1) is null. It's called short-circuit evaluation.

The previous example is executed successfully.
SELECT COALESCE(1, 1/0) FROM dual;

Performance

To be honest I have not measured the performance of NVL function vs COALESCE function on truly huge data. As I have googled there is no one opinion what function is faster. Try to use both in your tasks and compare what is better in your case.

But I suppose COALESCE function will be more productive when arguments are complex user-defined functions or subqueries.

Conclusions

As conclusion I usually use NVL function and replace it by COALESCE when

  • there are nested NVL (I need more than two arguments to compare)
  • the deferred (short-circuit) evaluation of arguments increases statement's performance
  • SQL should be easy portable on another database engines

Code snippets have been tested on Oracle Database 11g Release 11.2.0.1.0

2 comments:

  1. Except Oracle has the function NVL2 which is the direct equivalent of coalesce

    ReplyDelete
    Replies
    1. I am confused a bit concerning "direct equivalent".
      Of course expression with COALESCE can be rewritten with nested NVL2.
      As for me the main advantage of COALESCE is short-circuit evaluation. NVL2 doesn't support such functionality.
      Please compare
      select nvl2(null, nvl2(null, 1 / 0, 2), 0) from dual
      and
      select coalesce(null, 0, 1 / 0, 2) from dual
      The former raises "division by zero" exception, the latter completes without anyone.

      So I have not caught why NVL2 is the direct equivalent. Please clarify.

      Delete