Oracle Differences Between Nvl and Coalesce

Oracle Differences between NVL and Coalesce

COALESCE is more modern function that is a part of ANSI-92 standard.

NVL is Oracle specific, it was introduced in 80's before there were any standards.

In case of two values, they are synonyms.

However, they are implemented differently.

NVL always evaluates both arguments, while COALESCE usually stops evaluation whenever it finds the first non-NULL (there are some exceptions, such as sequence NEXTVAL):

SELECT  SUM(val)
FROM    (
        SELECT  NVL(1, LENGTH(RAWTOHEX(SYS_GUID()))) AS val
        FROM    dual
        CONNECT BY
                level <= 10000
        )

This runs for almost 0.5 seconds, since it generates SYS_GUID()'s, despite 1 being not a NULL.

SELECT  SUM(val)
FROM    (
        SELECT  COALESCE(1, LENGTH(RAWTOHEX(SYS_GUID()))) AS val
        FROM    dual
        CONNECT BY
                level <= 10000
        )

This understands that 1 is not a NULL and does not evaluate the second argument.

SYS_GUID's are not generated and the query is instant.

Why does NVL work over COALESCE in Oracle?

From the NVL documentation:

The arguments expr1 and expr2 can have any data type. If their data types are different, then Oracle Database implicitly converts one to the other. If they cannot be converted implicitly, then the database returns an error. The implicit conversion is implemented as follows:

• If expr1 is character data, then Oracle Database converts expr2 to the data type of expr1 before comparing them and returns VARCHAR2 in the character set of expr1.
• If expr1 is numeric, then Oracle Database determines which argument has the highest numeric precedence, implicitly converts the other argument to that data type, and returns that data type.

From the COALESCE documentation:

Oracle Database uses short-circuit evaluation. The database evaluates each expr value and determines whether it is NULL, rather than evaluating all of the expr values before determining whether any of them is NULL.

If all occurrences of expr are numeric data type or any non-numeric data type that can be implicitly converted to a numeric data type, then Oracle Database determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that data type, and returns that data type.

You will notice that NVL explicitly states it will perform an implicit conversion so that expr2 is the same data type as expr1 whereas COALESCE (although slightly confusingly worded) does not mention performing an implicit conversion (except for numeric data types) and will expect that all expressions in its argument list are the same data type.

Your query for NVL is effectively converted to:

UPDATE my_table
SET    some_column = CAST(
                       NVL(
                         :your_string_bind_variable,
                         CAST( some_column AS VARCHAR2 )
                       )
                       AS NUMBER
                     )
WHERE...

but your COALESCE function is:

UPDATE my_table
SET    some_column = COALESCE(
                       :your_string_bind_variable,
                       some_column
                     )
WHERE...

and expr1 and expr2 have different data types and the query raises an exception.

---

Assuming that there are no other columns being modified, you do not need to perform an UPDATE if the value is NULL as it is not going to change anything and could re-write your Java code as:

MyEnum enumValue = myClass.getMyEnum();
if ( enumValue != null )
{
  getJdbcTemplate().update(
    "UPDATE my_table SET some_column = ? WHERE...",
    enumValue.getStatus()
  );
}

Oracle COALESCE or NVL

nvl2 (yeah, "great" name, I know) will actually be much more convenient:

UPDATE OLCACT SET 
    ENDDATE    = pETime, 
    SYSENDDATE = NVL2(pETime, SYSDATE, NULL)
    GRD        = pGRD,
    PASS       = v_pass

What are the NVL and the NVL2 functions in Oracle? How do they differ?

NVL checks if first argument is null and returns second argument:

select nvl(null, 'arg2') from dual

in this example result will be: arg2;

select nvl('arg1', 'arg2') from dual

and in this one: arg1;

NVL2 has different logic. If first argument is not null then NVL2 returns second argument, but in other case it will return third argument:

select nvl2('arg1', 'arg2', 'arg3') from dual

Result: arg2

select nvl2(null, 'arg2', 'arg3') from dual

Result: arg3

Difference between COALESCE and CASE IS NOT NULL

Under most circumstances, the two are identical. I think the standard defines COALESCE(X, Y) as:

(CASE WHEN X IS NOT NULL THEN X ELSE Y END)

Under most circumstances, this does exactly what you expect.

There is one difference. One interpretation of the standard is the X is evaluated twice when the value is not NULL. This makes no difference under most circumstances (in most cases, the value would be cached anyway).

Postgres does not do this though. It implements a more reasonable interpretation of the standard. In other words, the two are not exactly identical in Postgres, because Postgres is smart enough to evaluate the first expression once, whether it is NULL or not.

You can see the effect if you have a volatile function (one whose value changes). In Postgres, the following returns "x" and "y" about the same number of times:

select v.x, coalesce(case when random() > 0.5 then 'x' end, 'y')
from (values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) v(x);

Equivalent code in SQL Server returns "y" half the time and "x" and NULL a quarter of the time each -- because SQL Server treats them the two constructs as exactly equivalent and evaluates the first argument twice under some circumstances.

You can see this in the db<>fiddles here and here.

However, SQL

In what scenario the second/third non-null parameters will be returned in COALESCE mysql?

Coalesce returns first non-null value in the list of arguments, if the first argument is null. These list of arguments could be constant value or derivation of other column value.

Primarily used for scenarios where you have to derive value from some other column or replace null with value for column where null is not an accepted for a given column.

Example : If a column is representing discount% then the value could be either a value or 0.0 but cannot be null.

coalesce(special_discount,base_discount_column,0.05) 

Here special discount if null consider base discount value , if base discount is also null, then take 5% discount by default.

coalesce, nvl and CASE not working for count(*) in the subquery

You are doing a LEFT join of dte to tmp, so you get nulls for the unmatched rows.

Maybe you think that by using COALESCE() and NVL() inside tmp the final results will show 0 instead of null, but this is not right.

COALESCE() and NVL() are internal in tmp and if you select from tmp only then you will not get nulls, but since you do that LEFT join and there are unmatched rows then the columns for these unmatched rows will be represented by nulls.

So you have to use COALESCE() and/or NVL() in your final SELECT statement if you want to remove these nulls.

---

Related Topics