Coalesce

COALESCE is a SQL function that returns the first non-NULL expression among its arguments

Nvl

`nvl` is a SQL function present in its vendor-specific implementation by Oracle



Modern portable ifnull

Example

"The ansi standard function coalesce is simpler than using nvl and decode which should be obsoleted anyway"

from question  

Oracle decode null date variable

"The coalesce function is used here because it is more portable than nvl or ifnull"

from question  

Set default value in select statement(not use UNION statement)

"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"

from question  

Oracle Differences between NVL and Coalesce

Others

Example

2 however coalesce requires all arguments to be of the same data type thus being stricter than nvl which will first attempt an implicit conversion

from question  

Can NVL Function be Cascaded?

Mureinik has shown you need to provide a default value to replace the nulls which you can do with the standard coalesce function or the nvl function do the same thing in this case but coalesce is more flexible

from question  

Possible to add multiple columns together and Alias in PL/SQL?

Nvl only takes 2 parameters whereas coalesce can take n parameters;when functions or equations are pass into them as parameters nvl will evaluate all of its parameters but coalesce will evaluate each in order stopping when it reaches a non-null value in other words coalesce will use short-circuit evaluation but nvl will not

from question  

Different table fields in one result set Oracle

Coalesce is more efficient than nvl as it only evaluates the second argument if the first is null whereas nvl evaluates both arguments every time

from question  

SQL Decode Null Values

P.s. don t use nvl;it has a lot of potential pitfalls coalesce is safer

from question  

Updating multiple columns sql

I prefer coalesce over nvl simply because coalesce is ansi iso standard

from question  

Using NVL with JOIN not returning the default value if null

Back to Home
Data comes from Stack Exchange with CC-BY-SA-4.0