There are two seemingly similar codes:

select coalesce(1,(select 100/0 from dual)) from dual;

and

 select nvl(1,(select 100/0 from dual)) from dual;

These are two functions for which you must go through all the parameters, but coalesce works without error, and nvl gives an error. Why is that?

  • I recommend putting the Oracle tag, because the function NVL is not supported by any other DBMS - Mike

1 answer 1

In fact, COALESCE is "smarter." NVL always calculates both arguments before issuing the result, and COALESCE calculates the arguments in order of verification. So, when an error occurs in nvl, it will issue it. What you need to know for this: when we push a subquery in nvl, it will definitely work, while coalesce can skip it. Source: here