How To Use Isnull In Sql

I've been working with data professionally for most a decade, and the first thing I tell all newcomers is to start mastering relational databases and Structured Query Language (SQL). Agreement how to query and manipulate data in databases is an important skill because every company has data and nearly want to empathize it. Learning the basics of SQL is easy, and the skill can land a loftier-paying job if you lot're skilful at information technology.

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 nosotros'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 yous're interested in:

Before we become started: creating exam data
What is the ISNULL function?
Using the ISNULL office
When non to use the ISNULL function
Information type conversion with ISNULL
Wrapping upwards

1. Earlier we get started: creating test data

Before we explore the ISNULL() role, we demand some data to play with. I spun upwardly a picayune 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 Exam

CREATE Table people

(personID INT IDENTITY(i, one) NOT Zip,

firstName VARCHAR(l) Not NULL,

homePhone VARCHAR(fifteen) NULL,

workPhone VARCHAR(15) Zero,

cellPhone VARCHAR(15) NULL,

age INT Cipher

);

insert into people(firstname, homephone, workphone, cellPhone, age)

values('Eric', '555-555-6655', '555-555-1234', naught, 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', aught, '555-555-1212', null);

Equally we can encounter, the tabular array contains 6 fields, iv of which can contain null values:

homePhone
workPhone
cellPhone
historic period

Nosotros tin can review the inserted records using a SELECT statement:

SELECT * FROM people

ISNULL SQL tutorial

Notice the three records display NULL for the phone numbers and historic period that do not exist. Although it says NULL in the results, it is important to sympathize information is absent from that field. Zilch is the absenteeism of data, meaning the Goose egg values are completely empty.

ii. What is the ISNULL() office?

Although many consider ISNULL() to be an advanced SQL function, information technology is very like shooting fish in a barrel 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 volition be checked past the function to come across if it's Nada 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 institute to exist NULL. If the expression is non Zero, then the expression value is returned. For example, if nosotros wait at the cellPhone cavalcade in the example data, nosotros see two of the three records evidence NULL values. If cellPhone is passed into ISNULL() as the expression and the discussion "N/A" is set as the value , the function volition return the specified value, "Due north/A" for two of the three records.

3. Using the ISNULL() function

The ISNULL() function tin can be used in multiple situations. The near straightforward way is to use it whenever you want to output a value if a NULL is found. Using the example data, allow's write a SELECT statement that will return firstName and homePhone fields. Using ISNULL(), we can make the query return the text, "No Domicile Telephone" if the homePhone field is NULL:

select firstName,

ISNULL(homePhone, 'No Home Phone') as HomePhoneNumber

from people

ISNULL SQL tutorial

We can see in the results, Tom returns the text No Dwelling house Phone since his homePhone value is Nil in the table.

Another way to use the ISNULL() role is to have it return a different column in the data, similar a different telephone number, instead of the "No Dwelling Phone" text. For instance, this expression returns the workPhone value if the homePhone is NULL:

select firstName, ISNULL(homePhone, workPhone) as HomeOrWork

from people

ISNULL SQL tutorial

Observe Tom's workPhone value at present outputs because it was specified in the function and the ISNULL expression is finding a Nil value for his homePhone.

The ISNULL() function can also exist used in dynamic SQL or when using declared variables. For example, declared variables can be passed into the part simply like we did with column names in the previous example'due south SELECT statement.

DECLARE @expression VARCHAR(50);

DECLARE @value VARCHAR(50);

Ready @expression = null;

SET @value = 'This tutorial is great!';

SELECT ISNULL(@expression, @value) Equally case;

ISNULL SQL tutorial

Notice the declared value returned since the declared expression was set to Nothing.

It is also possible to use ISNULL() inside amass functions like AVG() and SUM(). Let's say we want the average historic period 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

ISNULL SQL tutorial

iv. When non to use the ISNULL() function

While ISNULL() is a powerful role, at that place 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 desire to wait at all three fields and return the first not-null value, the COALESCE() function should exist used instead of the ISNULL() role.

Coagulate versus ISNULL

The COALESCE() office is used to return the outset not-cypher value in a given listing. For example, let's say we want to query all three telephone number fields, but but desire one value returned. We make up one's mind nosotros 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(), nosotros can pass that listing of phone number columns into the query and get a single non-cipher phone number back.

Hither is an example of using COALESCE():

select firstName, Coalesce(cellPhone, homePhone, workPhone) as phoneNumber

from people

ISNULL SQL tutorial

The ISNULL() function is express to the ii parameters, merely the COALESCE() function tin can take a list of many parameters. The COALESCE() functionality tin can be replicated using the ISNULL() office, merely 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 aforementioned results equally Coalesce() would expect like this:

select firstname, ISNULL(cellphone, ISNULL(homePhone, workPhone)) from people

ISNULL SQL tutorial

Discover the output is the same whether we use Coagulate() or nested ISNULL() functions. As you tin can imagine, if you take many items in your list, nesting ISNULL() would become very cumbersome. While it works, coalescing the data is meliorate because it makes the code easier to read.

5. Data blazon conversion with ISNULL

Another time ISNULL() volition not work is if the information types are not compatible. In SQL Server, the ISNULL() function tries to catechumen the lower precedence data types into a higher precedence data types. If ISNULL() is non able to convert the data type, information technology returns an error bulletin.

For case, if we use ISNULL() on personID an effort to return the current datetime on a null value, an error will display:

select ISNULL(personID, getdate()) from people

ISNULL SQL tutorial

As the error bulletin explains, converting the datetime to an integer is not possible. Refer to the official documentation for more than information on data types and conversions.

six. Wrapping up

Dealing with Nil values in SQL Server tin can be catchy, especially when preparing data for things like machine learning. Using built-in SQL Server functions similar ISNULL() and Coagulate() give you lot options to work effectually Goose egg values in the data. In this article we non only explored the situations in which ISNULL() tin be used to replace Null values with a specified value, simply we also covered a few situations in which it is not the correct function for the chore.

To learn more about data analytics, try out this free v-twenty-four hours short course , or check out some of our other data analytics articles: