the coalesce function returns the first non-null expression in the list. If all the expressions evaluate to null, then the coalesce function will return null.
coalesce(expr1, expr2,...expr_n).
In most cases, nvl is enough for the requirement when there are only two expressions. If there are more than two expressions, I think we can adopt the coalesce function to achieve the same effect.
COALESCE function is a SQL-92 standard and more modern, while nvl is oracle specific. In case of two values, they are synonyms.
However, they are implemented differently. NVL always evaluates both arguments, while coalesce stops evaluation whenever it finds first non-NULL.
TWO Examples:
coalesce(expr1, expr2,...expr_n).
In most cases, nvl is enough for the requirement when there are only two expressions. If there are more than two expressions, I think we can adopt the coalesce function to achieve the same effect.
COALESCE function is a SQL-92 standard and more modern, while nvl is oracle specific. In case of two values, they are synonyms.
However, they are implemented differently. NVL always evaluates both arguments, while coalesce stops evaluation whenever it finds first non-NULL.
TWO Examples:
<code><span class="kwd">SELECT</span><span class="pln"> SUM</span><span class="pun">(</span><span class="pln">val</span><span class="pun">)</span><span class="pln">
</span><span class="kwd">FROM</span><span class="pln"> </span><span class="pun">(</span><span class="pln"></span><span class="kwd">SELECT</span><span class="pln"> NVL</span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="pln"> LENGTH</span><span class="pun">(</span><span class="pln">RAWTOHEX</span><span class="pun">(</span><span class="pln">SYS_GUID</span><span class="pun">())))</span><span class="pln"> </span><span class="kwd">AS</span><span class="pln"> val
</span><span class="kwd">FROM</span><span class="pln"> dual
CONNECT </span><span class="kwd">BY </span><span class="pln">level </span><span class="pun"><=</span><span class="pln"> </span><span class="lit">10000</span><span class="pln">
</span><span class="pun">);
costs more time than
</span></code><code><span class="kwd">SELECT</span><span class="pln"> SUM</span><span class="pun">(</span><span class="pln">val</span><span class="pun">)</span><span class="pln">
</span><span class="kwd">FROM</span><span class="pln"> </span><span class="pun">(</span><span class="pln"></span><span class="kwd">SELECT</span><span class="pln"> </span><span class="kwd">COALESCE</span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="pln"> LENGTH</span><span class="pun">(</span><span class="pln">RAWTOHEX</span><span class="pun">(</span><span class="pln">SYS_GUID</span><span class="pun">())))</span><span class="pln"> </span><span class="kwd">AS</span><span class="pln"> val
</span><span class="kwd">FROM</span><span class="pln"> dual
CONNECT </span><span class="kwd">BY </span><span class="pln">level </span><span class="pun"><=</span><span class="pln"> </span><span class="lit">10000</span><span class="pln">
</span><span class="pun">);
which returns instantly.
The performance data is not correct, but the NVL() is supposed to cost more time.
the connect by keyword is of one oracle row generator techniques.
The concept is known as a row generator.
</span><span class="pln"></span></code>