How to Handle Null Values with the COALESCE Function in SQL Server

This tutorial will show how to use the COALESCE function to handle null values at run time. The COALESCE() function is used to specify how we want to treat NULL values. It returns the first non-null expression among its arguments.

Setting Up

In this example we will create a table called Products with columns of P_Id, ProductName, UnitPrice, UnitsInStock, and UnitsOnOrder. To create a table we must use the CREATE TABLE statement.

 

 

Notice we added null to the UnitsOnOrder column, as we will need a null value to work with when using the COALESCE function.

Now that we have our table created, we need to insert data into it. This is done with the INSERT INTO, SELECT, and UNION statements. INSERT INTO specifies where we want to enter the values, SELECT are the values we want to insert, and UNION does not allow duplicate values.

 

 

COALESCE Function Query

So now that we have our table set up with data, we are now able to write query to manipulate the data. What we want to do is handle null values in the UnitsOnOrder column with the help of the COALESCE function at execution of the query.

In this query we will select the ProductName and UnitPrice columns, but we will set  unit price to multiply the total of UnitsInStock and UnitsOnOrder columns. Meaning we must first add UnitsInStock and UnitsOnOrder and then multiply that number with the UnitPrice to get the total we are looking for.  As you may have noticed, we intentionally put a null into the Bread entity so we can change it when called in the COALESCE function.

 

 

Output

Execute the query and the UnitPrice of Bread should be 748.88 when COALESCE returns zero. If you look in the argument list of the COALESCE function, when UnitsOnOrder is null, the second argument in the list is what will be calculated, in this case a zero.

Now lets change the zero to 15 and you will see the UnitPrice of Bread increase significantly.

Thanks for reading and make sure to download the source files to get a better understanding of how the code works.

Leave a Reply

Your email address will not be published. Required fields are marked *