COALESCE Function in Oracle SQL – PLSQL

The COALESCE function in Oracle SQL / PLSQL is used to return the first NOT NULL expression in the list.

Syntax for the COALESCE function in Oracle SQL / PLSQL is:

SELECT COALESCE(expresion1, expression2, expression3, . . , expressionN)
FROM table_name;

• expression1 to expressionN are expressions to be tested for NULL values

Let’s take an example for understanding:
Suppose we have a table named ‘employee’ as shown below:

Example 1:

If we write our query as:

SELECT COALESCE(employee_id
       ,salary
       ,commission)
FROM employee;

The output of the above statement will be:

The above coalesce query is equivalent to writing:

IF employee_id is NOT NULL THEN
  Result = employee_id
ELSE IF salary is NOT NULL THEN
  Result = salary
ELSE IF commission is NOT NULL THEN
  Result = commission
ELSE
  Result = NULL
END IF

Example 2:
Suppose we have employee table as:

If we write our query as:

SELECT COALESCE(commission
       ,salary
       ,employee_id)
FROM employee;

The output of the above statement will be:

Here we can see that for employee_id = 104’ and ‘105’, the coalesce query has fetched ‘employee_id’ because ‘commission’ and ‘salary’ records for both these employees are having NULL values.

The above coalesce query is equivalent to writing:

IF commission is NOT NULL THEN
  Result  = commission
ELSE IF salary is NOT NULL THEN
  Result  = salary
ELSE IF employee_id is NOT NULL THEN
  Result = employee_id
ELSE
  Result  = NULL
END IF