What is Coalesce Function
Coalesce Function is a useful function in oracle and it is very useful when we want to return the first non-null values in the list. This SQL tutorial provides explanations, examples for Coalesce Function in Oracle and the Difference between Coalesce & NVL
expr1: This is returned if it is not null
expr2: This is returned if it is not null and the first expression is null
exprn: This is returned if the preceding expression is null
So Coalesce in oracle will return the first expression if it is not null else it will do the coalesce the rest of the expression
Important point
(1)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.
(2)COALESCE in oracle is equivalent to Case function
So COALESCE (expr1, expr2) is equivalent to:
CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END
Similarly,
COALESCE (expr1, expr2, …, exprn), for n>=3 is equivalent to:
CASE WHEN expr1 IS NOT NULL THEN expr1 WHEN expr2 IS NOT NULL THEN expr2 WHEN expr3 IS NOT NULL THEN expr3 WHEN exprn IS NOT NULL THEN exprn END
Or this can be written as
CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE COALESCE (expr2, ..., exprn) END
(3) Usually all the expr of the coalesce statement should be of the same datatype else ORA-00932 will be returned.
SQL> select coalesce('a',1,2) from dual;
select coalesce('a',1,2) from dual
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected CHAR got NUMBER
If all occurrences of expression are numeric datatype or any non-numeric data type that can be implicitly converted to a numeric datatype, then Oracle Database determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype and returns that datatype.
How to use the Coalesce Function
SQL> select coalesce(1,2,3) from dual; COALESCE(1,2,3) --------------- 1 SQL> SELECT COALESCE(null, null, null, 'X', 'Y'); COALESCE(...) --------------- X
Coalesce Examples
Suppose we have an employee oracle table consisting of address1, address2, and address3 and we can want to print the first not-null address of the employees from the table in order of priority address1 > address2 > address3
select emp_name, COALESCE(address1,address2,address3) from employee;
Difference Between Coalesce and NVL function
- Coalesce is generalization of NVL function. NVL function can be used for two expressions ,while coalesce can be used for Many
- In case of two expression ,they are same but implementation is different.Oracle evaluates both the expression in case of NVL while it just evaluate the first expression in case of coalesce.So if the second expression has errors,NVL will throw error while coalesce will not.
- Coalesce is ANSI standard while NVL is Oracle specific
Related Articles
Oracle Sql tutorial
NULLIF function in Oracle
NVL function in Oracle
Oracle LISTAGG Function
https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions023.htm#SQLRF00617
