Oracle NVL vs COALESCE vs DECODE: Eager and Short-Circuit Evaluation
Oracle’s NVL, COALESCE, and DECODE can produce similar results, but they differ in how expressions are evaluated.
NVL: Oracle may evaluate both arguments even when the first argument is not NULL.
COALESCE: Uses short-circuit evaluation, stopping at the first non-NULL expression.
DECODE: Uses short-circuit evaluation when checking search expressions.
CASE: Uses short-circuit evaluation when evaluating conditions.
These differences matter when an expression calls a resource-intensive function or could raise an exception.
In this article, I demonstrate these behaviors using Oracle SQL examples, a sequence to count function executions, and a division-by-zero test that produces ORA-01476 with NVL but not with COALESCE.
At first glance, Oracle’s NVL and DECODE seem like simple tools to handle NULL values, right? But under the hood, they have a different behavior some of them you may not know and this behavior is the topic of this blog post. If you’re using them interchangeably, you might be introducing performance issues or even runtime errors without knowing it.
This isn’t a new discovery; Tom Kyte demonstrated this behavior over 25 years ago in 2000. However, it’s still not widely known. For more insights, you can refer to the Ask TOM discussion titled “DECODE or NVL.“
Comparison Table:
| Function | Evaluation Behavior | Potential Consequence |
| NVL | May evaluate both arguments | Unnecessary function calls or errors |
| COALESCE | Short-circuit | Avoids evaluating later expressions once a non-NULL result is found |
| DECODE | Short-circuits search expressions | Stops searching after a match |
| CASE | Short-circuits conditions | Skips subsequent conditions once a match is found |
First I will explain the lazy and eager evaluation terms simply:
Eager evaluation:
Eager evaluation refers to a programming or query evaluation strategy where all expressions (or arguments) are evaluated immediately, whether or not they are needed. In the context of SQL functions like NVL, eager evaluation means that both arguments of the function are evaluated, even if the first input is sufficient to determine the result.
Lazy Evaluation (The Opposite) – Short Circuit
In contrast, lazy evaluation only evaluates the second argument when it is actually needed. In the NVL example, lazy evaluation would mean that counter_function(col1) is only called when col1 is null. In SQL, CASE, DECODE and COALESCE expressions exhibit lazy evaluation behavior, only executing the second argument if the first condition isn’t met.
Let’s unravel the difference, starting with an eye-opener:
NVL / NVL2 does not short circuit :
Here’s a common pattern that looks harmless:
SQL> SELECT NVL(col1, counter_function(col1)) FROM my_table;
You might expect that:
If col1 is not null, it should be returned directly without executing high_cost_computation(col1).
If col1 is null, only then should counter_function(col1) be called.
But that’s not what happens. Oracle evaluates both arguments of NVL, no matter what. That means counter_function(col1) is always executed even if col1 is not null. This can lead to unnecessary work, performance issues, or even runtime errors.
Here is the case :
I will create a table, a sequence for a counter, and an inline function (counter_function) – Prepare Phase:
SQL> CREATE TABLE my_table (col1 VARCHAR2(20));SQL> INSERT INTO my_table VALUES ('insanedba');SQL> INSERT INTO my_table VALUES (NULL);SQL> COMMIT;SQL> CREATE SEQUENCE call_counter_seq ;
Now i will query the sql statement for all the table records and see how much counter_function is executed.
-- Demonstrate (eager evaluation)SQL> WITH FUNCTION counter_function(p_val VARCHAR2) RETURN VARCHAR2 IS BEGIN -- Increment the sequence to track calls DECLARE n NUMBER; BEGIN SELECT call_counter_seq.NEXTVAL INTO n FROM dual; END; RETURN 'fallback'; END;SELECT NVL(col1, counter_function(col1)) AS resultFROM my_table;/RESULT--------------------------------------------------------------------------------insanedbafallback
SQL> SELECT call_counter_seq.CURRVAL AS function_call_count FROM dual;FUNCTION_CALL_COUNT------------------- 2

It is not only for performance reasons, but you may also encounter unexpected errors.
SQL> CREATE TABLE sample_table ( col1 NUMBER, col2 NUMBER, col3 NUMBER);SQL> insert into sample_table (col1, col2, col3) values (1,0,63);SQL> insert into sample_table (col1, col2, col3) values (null,1,64);SQL> COMMIT;SQL> select * from sample_table; COL1 COL2 COL3_______ _______ _______ 1 0 63 1 64SQL> select NVL(col1,col3/col2) from sample_table;Error starting at line : 1 in command -select NVL(col1,col3/col2) from sample_tableError at Command Line : 1 Column : 21Error report -SQL Error: ORA-01476: divisor is equal to zerohttps://docs.oracle.com/error-help/db/ora-01476/01476. 00000 - "divisor is equal to zero"*Cause: An expression attempted to divide by zero.*Action: Correct the expression, then retry the operation.More Details :https://docs.oracle.com/error-help/db/ora-01476/SQL> select COALESCE(col1,col3/col2) from sample_table; COALESCE(COL1,COL3/COL2)___________________________ 1 64
DECODE short circuits :
SQL> ALTER SEQUENCE call_counter_seq RESTART START WITH 1;
-- Demonstrate (lazy evaluation - short circuit)SQL> WITH FUNCTION counter_function(p_val VARCHAR2) RETURN VARCHAR2 IS BEGIN -- Increment the sequence to track calls DECLARE n NUMBER; BEGIN SELECT call_counter_seq.NEXTVAL INTO n FROM dual; END; RETURN 'fallback'; END;SELECT DECODE(col1, null, counter_function(col1),col1) AS resultFROM my_table;/
SQL> SELECT call_counter_seq.CURRVAL AS function_call_count FROM dual;FUNCTION_CALL_COUNT------------------- 1

COALESCE also short circuits :
You may also use COALESCE which is part of the ANSI SQL standard.
SQL> ALTER SEQUENCE call_counter_seq RESTART START WITH 1;
-- Demonstrate (lazy evaluation)SQL> WITH FUNCTION counter_function(p_val VARCHAR2) RETURN VARCHAR2 IS BEGIN -- Increment the sequence to track calls DECLARE n NUMBER; BEGIN SELECT call_counter_seq.NEXTVAL INTO n FROM dual; END; RETURN 'fallback'; END;SELECT COALESCE(col1, counter_function(col1)) AS resultFROM my_table;
SQL> SELECT call_counter_seq.CURRVAL AS function_call_count FROM dual;FUNCTION_CALL_COUNT------------------- 1
Frequently Asked Questions:
Does Oracle NVL use short-circuit evaluation?
Unlike COALESCE, Oracle’s NVL does not guarantee short-circuit evaluation. Its second argument may be evaluated even when the first argument is not NULL. This can cause unnecessary function executions or unexpected errors.
Is COALESCE faster than NVL in Oracle?
Not necessarily. For simple expressions, the performance difference may be negligible. However, COALESCE can be more efficient when later expressions involve expensive functions because it avoids evaluating them once a non-NULL result has been found. Always verify performance using the actual SQL statement and execution plan.
Why can NVL raise ORA-01476 when COALESCE does not?
NVL may evaluate its second argument even when the first argument is not NULL. If that argument contains a division by zero, Oracle can raise ORA-01476. COALESCE avoids evaluating later arguments once it encounters a non-NULL value, preventing that error in the demonstrated scenario.
Conclusion:
Although NVL, COALESCE, and DECODE can produce similar results, their evaluation behavior differs significantly.
When fallback expressions involve expensive function calls or operations that might raise exceptions, COALESCE or CASE may be preferable because of their short-circuit evaluation behavior.
However, these expressions are not always interchangeable, particularly regarding datatype conversion and return types. Choose the appropriate function based on the required semantics and test its behavior in your Oracle Database environment.
Hope it helps. See you on the next post : Part 2: Scalar Subquery Caching Behavior in a SQL Statement.
Also my other Common SQL/PLSQL Pitfalls – Blog Posts:
Common SQL and PL/SQL Pitfalls: Insights from Real-World Experience
NVL and DECODE: Lazy vs Eager Evaluation (Part 1)
Scalar Subquery Caching Behavior in a SQL Statement (Part 2)
Why NO_DATA_FOUND Behavior Differs in SQL and PL/SQL (Part 3)
Oracle SUM() Returns NULL or No Rows? GROUP BY Explained (Part 4)


Leave your comment