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
