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

This tutorial provides more the basic needs and informations on sql tutorial , pl sql tutorial , mysql tutorial , sql server , sqlcode , sql queries , sql , sql formatter , sql join , w3schools sql , oracle tutorial , mysql , pl sql , learn sql , sql tutorial for beginners , sql server tutorial , sql query tutorial , oracle sql tutorial , t sql tutorial , ms sql tutorial , database tutorial , sql tutorial point , oracle pl sql tutorial , oracle database tutorial , oracle tutorial for beginners , ms sql server tutorial , sql tutorial pdf