sql tutorial

SQL COALESCE Function


  SQL > SQL Commands > Coalesce Function

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

SQL NULLIF >>

Link to this page: If you find this page useful, we encourage you to link to this page. Simply copy and paste the code below to your website, blog, or profile.



Copyright 1999-2009 1keydata.com.   All Rights Reserved.     Privacy Policy  








SQL UNION
SQL UNION ALL
SQL INTERSECT
SQL MINUS
SQL LIMIT
SQL TOP
SQL Subquery
SQL EXISTS
SQL CASE
SQL NULL
SQL ISNULL
SQL IFNULL
SQL NVL
SQL COALESCE
SQL NULLIF
SQL Rank
SQL Median
SQL Running Totals
SQL Percent to Total
SQL Cumulative Percent to Total

SQL Jobs


CSS Tutorial

PHP Tutorial



Site Map
Resources



Data Warehousing & Business Intelligence