syntax and example of coalesce function in oracle database query

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:

Employee_Id	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Example 1:

If we write our query as:

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

The output of the above statement will be:

COALESCE(EMPLOYEE_ID,SALARY,COMMISSION)

101

102

103

104

105

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:

Employee_Id	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D		Support
105	Emp E		Sales	10

If we write our query as:

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

The output of the above statement will be:

COALESCE(COMMISSION,SALARY,EMPLOYEE_ID)

28000

104

105

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