Coalesce Function in sql

The COALESCE function in SQL returns the first non-NULL expression among its arguments.

 

Table Contact_Info

Name	Business_Phone	Cell_Phone	Home_Phone
Jeff	531-2531	622-7813	565-9901
Laura	NULL	772-5588	312-4088
Peter	NULL	NULL	594-7477

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, use the business phone number.

2. If a person does not have a business phone and has a cell phone, use 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, use the home phone number.

We can use the COALESCE function to achieve our goal:

SELECT Name, COALESCE(Business_Phone, Cell_Phone, Home_Phone) Contact_Phone
FROM Contact_Info;

Result:

Name	Contact_Phone
Jeff	531-2531
Laura	772-5588
Peter	594-7477

Have a nice day... 'N happy Coding :)