The SQL Server ISNULL system function is one of the most useful functions you will use in your work as a data professional.
The ISNULL function is meant to help you deal with NULLs in SQL Server. It’s meant to be used as an error handling tool of sorts.
The syntax of ISNULL
The ISNULL function is meant to return a non-NULL value in places where NULL may occur. Here is the general syntax:
ISNULL(<expression>,<replaceer_value>)
The idea is that if the <expression> passed in evaluates to NULL, the <replacer_value> is returned. Otherwise, if the passed in <expression> does not evaluate to NULL, the expression value is simply returned.
A simple example
Take a look at the following Employees table:
Let’s say we want to write a query that simply returns each employee’s First, Middle, and Last name. We’ll start with this:
Notice there are two employees whose middle name contains NULL. Maybe we don’t know the middle names of those employees yet, so we used NULL as an appropriate placeholder.
If we don’t want to return an ugly “NULL” value for those rows, we could use the ISNULL function to return something different.
Here’s what the query would look like:
SELECT FirstName, ISNULL(MiddleName, '*unknown*') AS Middle, LastName FROM Employees
So SQL Server will examine the passed-in MiddleName value. If it’s NULL, the function will return “*unknown*” instead! But if the MiddleName value isn’t NULL, the MiddleName value itself is simply returned.
Here’s the output:
Simple, right?
A more complex example
Your “<expression>” can be many things. At the end of the day, it’s simply a value that could return NULL.
Take a look at the following Employees and Sales tables:
Maybe we want to identify the most recent sale that each employee has made. We could write a query like the following:
Notice there are two employees who haven’t had any sales at all. The “MostRecentSaleDate” column for those employees is NULL because there is simply nothing to derive.
For those employees who haven’t made any sales, maybe we don’t want to return NULL as the value of our “MostRecentSaleDate” column. Instead, for presentation purposes, maybe we want to return “1900-01-01” as our way of saying that the employee hasn’t made any sales at all. The query would look like this:
In the cases where the MAX(S.SaleDate) expression returns NULL because the employee hasn’t made any sales, the value “1900-01-01” is returned instead.
So simple!
A factoid you need to know
Understand that the replacer value needs to be compatible with the data type of the expression. For example, the SaleDate column is a DATETIME data type, so the replacer value in the ISNULL function needs to be compatible with DATETIME. A simple date value, like we outlined, is indeed compatible and will work fine.
We could not outline a string value, for example, as the replacer value because string values are not compatible with date/time data types:
This is something to keep in mind!
Next Steps:
Leave a comment if you found this tutorial helpful!
NULL can be a tricky boy in SQL Server. Make sure you’re not making these 7 mistakes with it!
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, or if you are struggling with a different topic related to SQL Server, I’d be happy to discuss it. Leave a comment or visit my contact page and send me an email!
