Search code examples
sqloracle-databasenvl

What is the purpose of NVL having to evaluate both arguments?

I found out in this question - Oracle Differences between NVL and Coalesce - that NVL always evaluates both arguments.

NVL( string1, relace_with)

What is the point of evaluating both, if the first argument is not null ?

Won't we always return back the 1st argument in such a case?

thanks!

Solution

  • The point probably is that there might be side effects so that not only the return value of both arguments is important but also their side effects. NVL won't miss those side effects while COALESCE will miss the side effects of the second argument in some cases (due to the short-circuit behavior). So NVL would be very useful in those cases (when you don't want to miss any side effects).