using 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:

1SELECT COALESCE(employee_id
2       ,salary
3       ,commission)
4FROM 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:

1IF employee_id is NOT NULL THEN
2  Result = employee_id
3ELSE IF salary is NOT NULL THEN
4  Result = salary
5ELSE IF commission is NOT NULL THEN
6  Result = commission
7ELSE
8  Result = NULL
9END 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:

1SELECT COALESCE(commission
2       ,salary
3       ,employee_id)
4FROM 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:

1IF commission is NOT NULL THEN
2  Result  = commission
3ELSE IF salary is NOT NULL THEN
4  Result  = salary
5ELSE IF employee_id is NOT NULL THEN
6  Result = employee_id
7ELSE
8  Result  = NULL
9END IF