Coalesce example

Question

hi frnds, can any 1 tell me what is coalesce and how we can use it in our Sql Query. pls give an example. Thanks & Regards, Aamir

Satyapriya Nayak · Answer

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

Coalesce example

Answers (1)