What is ISNULL function in SQL?

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

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

SELECT ISNULL (col_name , NULL_replacement_value )

From the above syntax, it is obvious that the ISNULL function takes two arguments; The expression, that can be a table column name of any type.

The other is replacement value.

The next section shows you a few examples of using ISNULL SQL 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:

SELECT ISNULL(NULL, ‘Alternative Value’) AS “Replacement Value”;

SQL ISNULL

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 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;

SQL ISNULL string

Note: In MySQL database, the ISNULL function is different. This is used to test whether an expression is NULL.

It returns 1 for NULL and 0 for a NOT NULL value.

For example:

SELECT ISNULL(“Some Value”);

This should return 0.

An example of using datetime column with ISNULL

Similarly, you may use the datatime column for displaying 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;

SQL ISNULL date

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 queires:

All records from employees table:

SELECT emp_name, emp_salary, emp_age

FROM sto_employees;

AVG without using the ISNULL:

SELECT AVG(emp_salary) AS “AVG Without ISNULL” FROM sto_employees;

After using the ISNULL and providing a value:

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

FROM sto_employees;

The recordsets:

SQL ISNULL AVG

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 IS NULL condition. The difference is a space between the two, but “IS NULL” is used with the WHERE clause.

You cannot use ISNULL function in the WHERE clause. The IS NULL in WHERE clause can be used for returning the NULL records for the specified column. For example:

SELECT emp_name, emp_salary, emp_age, join_date

FROM sto_employees

WHERE join_date IS NULL;

The resultset:

SQL IS NULL

Author - Atiq Zia

Atiq is the writer at jquery-az.com, an online tutorial website started in 2014. With a passion for coding and solutions, I navigate through various languages and frameworks. Follow along as we unravel the mysteries of coding together!