NVL and COALESCE() functions in SQL

[Justin]

The NVL() function is available in Oracle, and not in MySQL or SQL Server. This function is used to replace NULL value with another value. It is similar to the IFNULL Function in MySQL and the ISNULL Function in SQL Server.

For example, if we have the following table:
Table Sales_Data
store_name Sales
Store A 300
Store B NULL
Store C 150

The following SQL code:

 SELECT SUM (NVL(Sales,  100))  FROM Sales_Data; 

returns 550.

This is because NULL has been replaced by 100 via the ISNULL function, hence the sum of the 3 rows is 300 + 100 + 150 = 550.

Please note: NVL function works with both character and numeric variables.

NVL(sales, 0);
*** Equivalent to this in SAS:  If  missing(sales) then sales= 0;

NVL(Name, “Not available”);
*** Equivalent to this in SAS: If missing(names) then names=”Not available”;

[Justin]

The COALESCE function in SQL returns the first non-NULL expression among its arguments. It is the same as the following CASE WHEN 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) as Contact_Phone
FROM Contact_Info; 

Result:
Name Contact_Phone
Jeff 531-2531
Laura 772-5588
Peter 594-7477

They do the same job as the below SAS code:

If Business_phone is not null then contact_phone=business_phone;
   Else if cell_phone is not null then contact_phone=cell_phone;
   Else if home_phone is not null then contact_phone=home_phone;
 Else contact_phone=”N/A”;

Isn’t it cool? Then practice and use it!