ISNULL Function in SQL

In your database tables, the values can be NULL for the columns with the Allow NULL constraint. While retrieving records from that table, these are displayed as NULL.

The SQL ISNULL function enables you to replace the NULL values with the specified value when retrieving data from the SQL Server database. For example:

From the above syntax, it is obvious that the ISNULL function takes two arguments;

• First is the expression, which can be a table column name of any type.
• The other is the replacement value.

The next section shows a few examples of using the SQL ISNULL function with different data types.

A simple example of ISNULL function

Before going into the SQL Server database table, let me show a simple example of using the ISNULL function.

The query with replacement value:

The example of using ISNULL with the character column

We have a sto_employees table that stores employee information. Apart from other information, the phone number is also stored in the table.

Most of the entries are NULL for this column in our example table.

The example below uses two queries;

• One with the simple SELECT statement to retrieve complete table data.
• The other query fetches the records with ISNULL function and “Not Available” is used as an alternative text.

See the queries and results of both queries:

Simple query:

SELECT emp_name, emp_salary, emp_age, phone

FROM sto_employees;

With ISNULL:

SELECT emp_name, emp_salary, emp_age, ISNULL(phone, 'Not Available') As Phone

FROM sto_employees;

For example:

This should return 0.

An example of using datetime column with ISNULL

Similarly, you may use the datatime column to display some alternate value for the NULL entries.

The same table as used in the above example has the join_date column. A few entries are kept NULL for the demo.

Just like the string, the date alternate value is also enclosed in single quotes.

Without ISNULL:

SELECT emp_name, emp_salary, emp_age, join_date 

FROM sto_employees

WHERE id BETWEEN 1 AND 8;

With ISNULL function:

SELECT emp_name, emp_salary, emp_age, ISNULL(join_date,'2018/01/01') AS "Join Date"

FROM sto_employees

WHERE id BETWEEN 1 AND 8;

The example with the numeric column

For the numeric column demo, I will use the AVG aggregate function in a numeric column.

We will calculate the average salary from the employees’ table. In the table, a few records contain NULL entries for demo only:

Three queries:

All records from the employees table:

AVG without using the ISNULL:

After using the ISNULL and providing a value:

SELECT AVG(ISNULL(emp_salary,4500)) AS "AVG With ISNULL" 

FROM sto_employees;

The recordsets:

In that case, all NULL values in the emp_salary column are replaced by 4500.

Difference between ISNULL and IS NULL

Do not mix up the ISNULL function with the IS NULL condition.

For example:

SELECT emp_name, emp_salary, emp_age, join_date

FROM sto_employees

WHERE join_date IS NULL;

The resultset: