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.
 syntax opf coalesce function
  • 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

Related Searches to COALESCE FUNCTION IN SQL

Adblocker detected! Please consider reading this notice.

We've detected that you are using AdBlock Plus or some other adblocking software which is preventing the page from fully loading.

We don't have any banner, Flash, animation, obnoxious sound, or popup ad. We do not implement these annoying types of ads!

We need money to operate the site, and almost all of it comes from our online advertising.

Please add wikitechy.com to your ad blocking whitelist or disable your adblocking software.

×