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.
ISNULL Function in SQL Server
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 )
- 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;
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 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.
AVG(ISNULL(Salary, 0)) as AverageSalary
GROUP BY DepartmentID;
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.
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.
You may like to read:
After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.