ISNULL – Microsoft (T-SQL)

ISNULL function defined

ISNULL is a Microsoft T-SQL function created to recognize NULL values and respond with a replacement value. It is designed to replace a NULL value with an actual value to preserve data or prevent calculated results from being skewed.  It is NOT standards based therefore it is not portable outside of a Microsoft SQL server environment.   The syntax is quite easily understood since there are only 2 arguments.

The syntax looks like this.

ISNULL ( check_expression , replacement_value )

The <check_expression> is what we are asking the ISNULL function to examine to see if the value “is null” and if it is NULL  then replace it with the specified <replacement_value> .

EXAMPLE:

Firstly note the presence of NULL placeholders in the “region” column of the following table

ISNUL Result1

 

The following example replaces the NULL placeholders with the string ‘No Region’

--
--Download DB install files. Click on this>> http://www.infinitelearningsolutions.net/downloads/
--
--ISNULL to replace the NULLS with the string 'No Region' 
--
USE ClassicModels2014;
GO
--
--
SELECT CustomerName, Addressline1, city, ISNULL(region, 'No Region')AS Region, postalcode
FROM dbo.Customers;
GO
--

ISNULL results 2

 

The following example replaces the NULL placeholder with the number 0 before the AVG() function (average), performs its calculation on the discount column. It also illustrates how the AVG() function calculates a different result because it ignores the rows with NULL value (row count), in its calculation of (SUM ÷ row count).

--
-- Download DB install files. Click on this>> http://www.infinitelearningsolutions.net/downloads/
--
  SELECT AVG(discount) AS "Without NULLs Replaced", AVG(ISNULL(discount,0)) AS "With NULLS Replaced"
  FROM [ClassicModels2014].[dbo].[OrderDetails];
  GO
--

isnull-results3

 

Remarks:

It should be noted that the MySQL database management system also has an ISNULL function but it serves a different purpose. The MySQL equivalent is called IFNULL. Also if you wish to write code that is more portable you should consider using the standards based COALESCE function instead.

Parting Shot

It was a lot of fun for me writing this. I hope you enjoyed the quick read and learned something as well and here is something to think about.

~Its so windy outside we’re using a log chain instead of a wind sock.

 

Share the Knowledge

Leave a Reply