In this article, we will learn how to use COALESCE function in Oracle.
COALESCE is a function that checks a list of values and returns the first non-null value it finds.
Syntax
The syntax of COALESCE function is as follows:
COALESCE( e1, e2, e3, e4.....)
Here,
e1, e2, e3 – These are values that can belong to any data type.
The COALESCE function returns the first non-NULL value from the list of values that we give. The minimum number of expressions that we can give to COALESCE function is two. If all the expressions are NULL, then the function returns NULL value in output.
Return Data type
If all the expressions belong to the same data type, then COALESCE function also returns the output in the same data type.
But, if the expressions belong to different data types then COALESCE function implicitly converts all the expressions to the data type of the first expression and in case it fails then it returns an error.
Examples
Lets use a simple query to see how COALESCE function works:
SELECT COALESCE(1, 2, 3) FROM dual;
Result:
The query returns the first non-null value from the list.
SELECT COALESCE(NULL, 1, 2, 3) FROM dual;
Result:
SELECT COALESCE(NULL,NULL) FROM dual;
Result:
Let’s see this example, where all the expressions belong to the string data type.
SELECT COALESCE(NULL,'X','Y','Z') output FROM dual;
Result:
The COALESCE function returns the first non-null value that is ‘X’ belonging to the string data type, since all the values are characters. Similarly, if all the expressions belong to the numeric data type then the output would also be a numeric value.
Now, lets see one example where both expressions belong to different data types.
SELECT COALESCE(NULL,1,'Z') FROM dual;
Result:
As we can see the query returns an ORA-00932 error stating that inconsistent data types were detected in the COALESCE function.
We need to use TO_CHAR function to convert numeric value to text, so that COALESCE function can compare and return the right result:
SELECT COALESCE(NULL,TO_CHAR(1),'Z') FROM dual;
Result:
This time COALESCE function returns 1 in output as we used TO_CHAR function to make data types of expressions within COALESCE function as consistent.
Short-Circuit Evaluation
Unlike some other functions in Oracle, COALESCE function does not evaluate all the expressions before returning the output.
Let’s see one query:
SELECT COALESCE(200 + 200, 1/0) FROM dual;
Result:
COALESCE function only evaluates and returns the first non-null value it finds. It does not evaluate the second expression, if it already found the non-null value. If it would have evaluated the second expression, it would have returned an error as Oracle is not able to evaluate numeric values divisible by zero.
Using COALESCE function with Columns in a Table
For this example. we will be using Employee Table from HR schema for this example
Since not every employee working in company gets a commission on top of salary, most of the slots are NULL in commission_pct column. We use the following expression, to find out salary and commission of all employees.
SELECT first_name || ' ' || last_name Employee_name, salary, COALESCE(salary * commission_pct, salary* commission_pct)Commission FROM employees;
Result:
The COALESCE specifically returns NULL if the commission_pct is NULL and it evaluates and returns the commission, if the employee is getting a commission.
Comparing COALESCE and CASE function
Using COALESCE function is a better way to evaluate multiple expressions then using a CASE function.
Suppose, we want to test these three expression and want the first non-null value, then we use this expressions.
COALESCE(e1, e2, e3)
Similarly, if we want to use CASE for performing the same operation, then the logical equivalent of the COALESCE function would be:
CASE WHEN e1 IS NOT NULL THEN e1 WHEN e2 IS NOT NULL THEN e2 WHEN e3 IS NOT NULL THEN e3 ELSE NULL END
As we can see, that using a CASE function is a more lengthy and verbose way of performing the same operation.
Comparing COALESCE and NVL function
COALESCE and NVL function might appear the same as they perform the same operation that is returning the first non-null value, but NVL function evaluates all the expressions whereas COALESCE evaluates expressions only until the point it finds the first non-null value.
Lets see the example that we used above:
SELECT COALESCE(1,10/0) FROM dual;
Result:
The query returns 1 as the coalesce function finds the first non-null value and gives the final output.
Lets, see what happens when we use NVL function:
SELECT NVL(1,10/0) FROM dual;
Result:
Even the NVL should have returned the first non-null value, but it returns an error. Because, NVL function evaluates all the expressions in the function before giving the final output.
In this article, we learned how to use COALESCE function to evaluate the first non-null value in a function.