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:

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:

<br />SELECT COALESCE(employee_id<br /><%%KEEPWHITESPACE%%>       ,salary<br /><%%KEEPWHITESPACE%%>       ,commission)<br />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:

<br />IF employee_id is NOT NULL THEN<br /><%%KEEPWHITESPACE%%>  Result = employee_id<br />ELSE IF salary is NOT NULL THEN<br /><%%KEEPWHITESPACE%%>  Result = salary<br />ELSE IF commission is NOT NULL THEN<br /><%%KEEPWHITESPACE%%>  Result = commission<br />ELSE<br /><%%KEEPWHITESPACE%%>  Result = NULL<br />END IF<br />

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:

<br />SELECT COALESCE(commission<br /><%%KEEPWHITESPACE%%>       ,salary<br /><%%KEEPWHITESPACE%%>       ,employee_id)<br />FROM employee;

The output of the above statement will be:

COALESCE(COMMISSION,SALARY,EMPLOYEE_ID)
10
20
28000 20
104
105 10

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:

<br />IF commission is NOT NULL THEN<br /><%%KEEPWHITESPACE%%>  Result  = commission<br />ELSE IF salary is NOT NULL THEN<br /><%%KEEPWHITESPACE%%>  Result  = salary<br />ELSE IF employee_id is NOT NULL THEN<br /><%%KEEPWHITESPACE%%>  Result = employee_id<br />ELSE<br /><%%KEEPWHITESPACE%%>  Result  = NULL<br />END IF<br />

Tagged , , , , , , , , , , , . Bookmark the permalink.
  • Farooq Omar

    how did you get 28000 and 105 in the second example?

  • Farooq Omar

    How did you get 28000 and 105 in the second example?

    • techhoney

      Hi Farooq,

      Thanks for pointing out, that was typo. We’ve corrected that.

      Good to know that someone’s reading diligently.

      Thanks,
      Admin