coalesce function in sql

Introduction

When we have multi-value attribute with single or more null values in a Table, the Coalesce() function is very useful.

Using the Code

If you consider the below facts placed in a employee table with Id, Name, ph_no, Alt_no, Office no.

id	Name	Ph_ no	Alt_ no	Office no
101	Albert	999999	456453	321333
102	khan	null	null	123455
103	victor	112121	null	null
104	lovely	null	null	1897321

The above Employee table may have single value or three values. If it has single value, then it fills null values with remaining attributes.

When we retrieve the number from employee table, that number Should Not be Null value. To get not nullvalue from employee table, we use Coalesce() function. It returns the first encountered Not Null Value from employee table.

Hide   Copy Code

select id , name ,coalesce(Ph_no,Alt_no,Office_no) as contact number from employee 

It returns:

id	Name	Contactnumber
101	Albert	999999
102	khan	123455
103	victor	112121
104	lovely	1897321