I’ve been working with data professionally for almost a decade, and the first thing I tell all newcomers is to start mastering relational databases and Structured Query Language (SQL). Understanding how to query and manipulate data in databases is an important skill because every company has data and most want to understand it. Learning the basics of SQL is easy, and the skill can land a high-paying job if you’re good at it.
Whether you’re learning SQL to become a database administrator, data analyst or data scientist, it is important to understand how to handle NULL values. In this tutorial we’re going to cover how to use the ISNULL() function in SQL Server, as well as some tips and tricks to make sure you’re applying ISNULL() correctly.
You can use this clickable menu to skip ahead to any section you’re interested in:
- Before we get started: creating test data
- What is the ISNULL function?
- Using the ISNULL function
- When not to use the ISNULL function
- Data type conversion with ISNULL
- Wrapping up
1. Before we get started: creating test data
Before we explore the ISNULL() function, we need some data to play with. I spun up a little database named Test and created a table named people containing six columns. Then I inserted three records into the table. Use this script to replicate the people table and example records:
use Test
CREATE TABLE people
(personID INT IDENTITY(1, 1) NOT NULL,
firstName VARCHAR(50) NOT NULL,
homePhone VARCHAR(15) NULL,
workPhone VARCHAR(15) NULL,
cellPhone VARCHAR(15) NULL,
age INT NULL
);
insert into people(firstname, homephone, workphone, cellPhone, age)
values(‘Eric’, ‘555-555-6655’, ‘555-555-1234’, null, 34);
insert into people(firstname, homephone, workphone, cellPhone, age)
values(‘Tom’, null, ‘555-555-4457’, null, 43);
insert into people(firstname, homephone, workphone, cellPhone, age)
values(‘Lucy’, ‘555-555-7978’, null, ‘555-555-1212’, null);
As we can see, the table contains six fields, four of which can contain null values:
- homePhone
- workPhone
- cellPhone
- age
We can review the inserted records using a SELECT statement:
SELECT * FROM people
Notice the three records display NULL for the phone numbers and age that do not exist. Although it says NULL in the results, it is important to understand data is absent from that field. Null is the absence of data, meaning the NULL values are completely empty.
2. What is the ISNULL() function?
Although many consider ISNULL() to be an advanced SQL function, it is very easy to understand and use. Before getting into examples, let’s take a look at the syntax for the ISNULL() function:
ISNULL(expression, value)
The Expression corresponds to the field that will be checked by the function to see if it’s NULL or not. The Value corresponds to the specified value that will be returned if the expression is NULL.
When querying data and using ISNULL(), the function returns a specified value if the expression is found to be NULL. If the expression is not NULL, then the expression value is returned. For example, if we look at the cellPhone column in the example data, we see two of the three records show NULL values. If cellPhone is passed into ISNULL() as the expression and the word “N/A” is set as the value, the function will return the specified value, “N/A” for two of the three records.
3. Using the ISNULL() function
The ISNULL() function can be used in multiple situations. The most straightforward way is to use it whenever you want to output a value if a NULL is found. Using the example data, let’s write a SELECT statement that will return firstName and homePhone fields. Using ISNULL(), we can make the query return the text, “No Home Phone” if the homePhone field is NULL:
select firstName,
ISNULL(homePhone, ‘No Home Phone’) as HomePhoneNumber
from people
We can see in the results, Tom returns the text No Home Phone since his homePhone value is NULL in the table.
Another way to use the ISNULL() function is to have it return a different column in the data, like a different phone number, instead of the “No Home Phone” text. For example, this expression returns the workPhone value if the homePhone is NULL:
select firstName, ISNULL(homePhone, workPhone) as HomeOrWork
from people
Notice Tom’s workPhone value now outputs because it was specified in the function and the ISNULL expression is finding a NULL value for his homePhone.
The ISNULL() function can also be used in dynamic SQL or when using declared variables. For example, declared variables can be passed into the function just like we did with column names in the previous example’s SELECT statement.
DECLARE @expression VARCHAR(50);
DECLARE @value VARCHAR(50);
SET @expression = null;
SET @value = ‘This tutorial is great!’;
SELECT ISNULL(@expression, @value) AS example;
Notice the declared value returned since the declared expression was set to NULL.
It is also possible to use ISNULL() within aggregate functions like AVG() and SUM(). Let’s say we want the average age of the people in the dataset. We can use ISNULL() to supply a value to the nulls as a means for estimating the average age:
select avg(ISNULL(age, 30)) as averageAge from people
4. When not to use the ISNULL() function
While ISNULL() is a powerful function, there are some situations where it is not the best function for the job. Consider we have three phone number fields in the data. If we want to look at all three fields and return the first non-null value, the COALESCE() function should be used instead of the ISNULL() function.
COALESCE versus ISNULL
The COALESCE() function is used to return the first non-null value in a given list. For example, let’s say we want to query all three phone number fields, but only want one value returned. We decide we want to first check the cellPhone field. If the cellPhone value doesn’t exist, we want the homePhone value. If homePhone doesn’t exist, we want workPhone. Using COALESCE(), we can pass that list of phone number columns into the query and get a single non-null phone number back.
Here is an example of using COALESCE():
select firstName, COALESCE(cellPhone, homePhone, workPhone) as phoneNumber
from people
The ISNULL() function is limited to the two parameters, but the COALESCE() function can take a list of many parameters. The COALESCE() functionality can be replicated using the ISNULL() function, but we need to nest ISNULL() within ISNULL(), making the code harder to understand versus using COALESCE(). Nesting the ISNULL function inside itself to get the same results as COALESCE() would look like this:
select firstname, ISNULL(cellphone, ISNULL(homePhone, workPhone)) from people
Notice the output is the same whether we use COALESCE() or nested ISNULL() functions. As you can imagine, if you have many items in your list, nesting ISNULL() would become very cumbersome. While it works, coalescing the data is better because it makes the code easier to read.
5. Data type conversion with ISNULL
Another time ISNULL() will not work is if the data types are not compatible. In SQL Server, the ISNULL() function tries to convert the lower precedence data types into a higher precedence data types. If ISNULL() is not able to convert the data type, it returns an error message.
For example, if we use ISNULL() on personID an try to return the current datetime on a null value, an error will display:
select ISNULL(personID, getdate()) from people
As the error message explains, converting the datetime to an integer is not possible. Refer to the official documentation for more information on data types and conversions.
6. Wrapping up
Dealing with NULL values in SQL Server can be tricky, especially when preparing data for things like machine learning. Using built-in SQL Server functions like ISNULL() and COALESCE() give you options to work around NULL values in the data. In this article we not only explored the situations in which ISNULL() can be used to replace NULL values with a specified value, but we also covered a few situations in which it is not the correct function for the job.
Want to get a more in-depth look at SQL through a formal course? We’ve rounded up a list of the best SQL certifications in this post.
To learn more about data analytics, try out this free 5-day short course, or check out some of our other data analytics articles: