Coalesce example

Hi Aamir,

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

It is the same as the following CASE statement:

SELECT CASE ("column_name")
  WHEN "expression 1 is not NULL" THEN "expression 1"
  WHEN "expression 2 is not NULL" THEN "expression 2"
  ...
  [ELSE "NULL"]
  END
FROM "table_name"

For examples, say we have the following table,

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


refer

http://www.1keydata.com/sql/sql-coalesce.html






Thanks