In a previous post we discussed using functions within SQL Server. The SQL COALESCE function is used when you want to combine values from a series of columns or expressions when they might contain NULL values.

The goal of this demonstration is to use the COALESCE function to find the annual salary of each employee. The first section of code is creating a table with five columns to hold sample data.

CREATE TABLE dbo.Wages
(empID int,
 hourly_wage int,
 salary int,
 commission int,
 num_sales int)
GO

Next, we use an INSERT statement to add four rows of data to our demonstration table.

INSERT INTO dbo.Wages VALUES
(1, 10, NULL, NULL, NULL),
(2, NULL, 41600, NULL, NULL),
(3, 12, NULL, NULL, NULL),
(4, 10, NULL, 10000, 5)

Now that we have our data ready, it is time to demonstrate the COALESCE function. The purpose of this function is to look for and act on fields that may or may not have a NULL value. (A NULL value is an absence of a value or an unknown value.)

SELECT *,
	COALESCE(hourly_wage * 40 * 52, salary, 
	commission * num_sales)
FROM dbo.Wages

In the case of this statement, the COALESCE function will look to see if there is a value in the hourly_wage field. If there is a value, then the function will calculate hourly_wage * 40 * 52. If the hourly_wage field was NULL it would move on to the next argument, in this case the salary field.

If the salary field has a value, then that value is returned. If the salary field also has a NULL value, then the function would move to the next argument. In this case the commission * num_sales. This would continue until there were no more arguments to evaluate at which time the function would bring back a NULL response.