ISNULL Function in SQL Server

In this SQL Server tutorial, you will learn the ISNULL function in SQL Server, allowing us to handle the null values in our table.

Because handling null values is a very important aspect of data analysis and integrity, first, you will be introduced to the ‘What is ISNULL() function in SQL Server?’ with its syntax.

Through an example, you will see how it deals with null values or expressions containing null values.

Additionally, you will understand how to pass the variable to the ISNULL() function to handle null values. Finally, you will use the ISNULL() function on the table.

Table of Contents

The ISNULL Function in SQL Server replaces the NULL value in your table column with the specified value. The database table contains some missing or unknown values, represented by NULL values.

While generating results for making a decision, if your table contains null values, it can yield the wrong result, which may lead to a bad decision. So, before doing an operation, you must ensure this unwanted value does not exist in your table.

To handle those NULL values, use the ISNULL() function, which substitutes a new value in place of NULL values in your table or expression.

The syntax is given below.

ISNULL ( check_expression , replacement_value )

Where,

ISNULL(): This complete function replaces the NULL value with the new specified value.
check_expression: This is the expression you pass to the function to check whether the given expression value is NULL. It can be column or NUL values.
replacement_value: It is the value which is substituted at the place of the NULL value. If the given expression value is a NULL value, then the replace value replaces that NULL value. The data type of the replace value should match the data of the check_expression value.

Let’s begin with an example: suppose you have to replace the NULL value with integer value 25; for that, use the query below.

SELECT ISNULL(NULL,'25') AS NullReplacement;

From the output of the above query, the NULL value is replaced by the integer value 25, as you can see in the output.

Here, check_expression is the NULL value, the first value in the ISNULL() function, and the second value is the replacement_value, which is 25 in this case. So ISNULL() found the first check_expression as a NULL value, replacing the NULL value with 25.

Passing Variable to ISNULL Function in SQL Server

You can pass the variable as the check_expression to the ISNULL() function.

For example, declare a variable @value of type VARCHAR(30) and assign a NULL value to it, as shown below.

DECLARE @value VARCHAR(30) = NULL;

Now pass variable @value to the ISNULL() function with replacement_value equal to ‘Replacing Null Values’ as shown below.

SELECT ISNULL(@value,'Replacing Null values') AS NullReplacement;

Passing Variable to ISNULL Function in SQL Server

From the output of the above query, the NULL value in a variable @value is replaced by the new value ‘Replacing Null values’ that you can see in the output.

Here, the null value is in the @variable; then, this variable is passed to ISNULL() to verify whether it contains a NULL value. As a result, the ISNULL() function finds a NULL value, replacing it with the value ‘Replacing Null values’.

This is how to pass a variable to the ISNULL function in SQL Server.

Using ISNULL Function in SQL Server on Table

Let’s see how to use the ISNULL function in SQL Server on table columns, as you often interact with tables.

For example, look at the given Employees table below.

Using ISNULL Function in SQL Server on Employees Table

Using the above table, you must compute the average salary from the Employee’s table. Still, as you can see, the salary column contains null values, which will lead to an average salary that is too low, so to resolve that, use the ISNULL() function to replace the null value with 0.

For that, use the query below.

SELECT
	DepartmentID,
	AVG(ISNULL(Salary, 0)) as AverageSalary
FROM Employees
GROUP BY DepartmentID;

Using ISNULL Function in SQL Server on Table

After executing the above query, the result set contains the average salary of the employees. For example, the average salary for the department ID equal to 2 is 31000.

Suppose you compute the Employee’s average salary without using the ISNULL() function. In that case, you might find different average salaries for employees, which can be wrong due to null values.

Here, in the query part AVG(ISNULL(Salary, 0)), the ISNULL() function replaces all the null values in the Salary column with 0.

This is just one example of using the ISNULL function in SQL Server to handle while aggregating table data.

Conclusion

In this SQL Server tutorial, you used the ISNULL function in SQL Server to replace the null value with the specified value and the null value within the variable through an example.

In the end, you have applied the INULL() function on the table column to replace the null value with 0, where you have computed the average salary of the employees. Dealing with null values is very necessary. Otherwise, it can lead to wrong results or information.