SQL Coalesce | Coalesce function in sql - sql - sql tutorial - learn sql
- Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to 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"
...
[ELSE "NULL"]
END
FROM "table_name";
- For examples, say we have the following table,
- Table Contact_Info
Name | Business_Phone | Cell_Phone | Home_Phone |
---|---|---|---|
Neff | 531-2531 | 622-7813 | 565-9901 |
Taura | NULL | 772-5588 | 312-4088 |
Arnold | NULL | NULL | 594-7477 |
- and we want to find out the best way to contact each person according to the following rules:
- If a person has a business phone, use the business phone number.
- If a person does not have a business phone and has a cell phone, use the cell phone number.
- 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 |
---|---|
Neff | 531-2531 |
Taura | 772-5588 |
Arnold | 594-7477 |