Exploring the ISNULL() function in SQL Server

When (and when not) to use it

Photo by Kelly Sikkema on Unsplash

Working with data

I’ve been working in data professionally for almost a decade and the first thing I tell all new-comers 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.

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 6 columns. Then I inserted 3 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