Home » Oracle SQL » ORACLE COALESCE

ORACLE COALESCE

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:

Oracle COALESCE Function Example 1

The query returns the first non-null value from the list.

SELECT
COALESCE(NULL, 1, 2, 3)
FROM
dual;

Result:

Oracle COALESCE Function Example 2
SELECT
COALESCE(NULL,NULL)
FROM
dual;

Result:

Oracle COALESCE Function Example with NULL Values

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.

Oracle COALESCE Function Example with String Data Type

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.

Oracle COALESCE Function Example with Inconsistent Data Type

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.

Oracle COALESCE Function Example

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.

Oracle COALESCE Function Example with Expression

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.

Oracle COALESCE Function Example with Column

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.

Oracle COALESCE Function comparison with NVL

Lets, see what happens when we use NVL function:

SELECT
NVL(1,10/0)
FROM
dual;

Result:

Oracle COALESCE Function comparison with NVL Query

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.