top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

What is COALESCE?

+3 votes
97 views
What is COALESCE?
posted Jun 30, 2015 by Manikandan J

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

+1 vote

The COALESCE function in SQL returns the first non-NULL expression among its arguments. The syntax for COALESCE is as follows:

COALESCE ("expression 1", "expressions 2", ...)

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
answer Jul 1, 2015 by Shivaranjini
...