COALESCE Function in SQL TO Avoid Null in its arguments.

The COALESCE function in SQL returns the first non-NULL expression among its given columns

 Examples, say we have the following table,
Table Personel_Contact
Name Business_Num Cell_Num         Home_Num
Vinoth        9684637284 9674736743 9685847364
Ravi           NULL                  9583525487     7859493859
Suresh       NULL                  NULL              7675847393
and we want to find out the best way to contact each person according to the following rules:

1. If a person has a business phone, it will return the Business phone number.

2. If a person does not have a business phone and has a cell phone, it will return the cell phone number.

3. If a person does not have a business phone, does not have a cell phone, and has a home phone, it will return the home phone number.

We can use the COALESCE function to achieve our goal:

SELECT Name, COALESCE(Business_Num,Cell_Num,Home_Num) Cont_Num
FROM Personel_Contact;

Result:

Name Cont_Num
vinoth 9684637284
Ravi         9583525487
suresh      7675847393