SQL COALESCE Function
SQL > SQL NULL >
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"
For examples, say we have the following table,
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