SQL Server ISNULL Function

It is a built-in function in SQL Server. It allows the user to replace the NULL values with a given replacement value. This article gives a complete overview of the ISNULL function to return an alternative value if the expression or table records have NULL values.

Syntax

The following is a syntax that illustrates the ISNULL function:

ISNULL ( Expression, Replacement )  

As we can see that this syntax accepts only two arguments:

• Expression: It is used to check for NULL. It can be of any type.
• Replacement: It is the value that will be returned when the expression is NULL. It must be implicitly convertible to a value of the expression type.

If the expression evaluates to NULL, this function replaces the NULL value with the replacement value. When the argument’s data types are different, the SQL server implicitly transforms the replacement value data type to the expression data type before returning a value. We will get the expression value when the expression is not NULL.

The ISNULL function can works in SQL Server (starting with 2008), Parallel Data Warehouse, Azure SQL Database, and Azure SQL Data Warehouse.

ISNULL Function Example

Let us explore the ISNULL function with some examples in SQL Server.

1. ISNULL function with the numeric value

The below example uses the ISNULL function. Here the first argument is NULL; therefore, it returns the value of a second argument in a result:

SELECT ISNULL(NULL, 25) AS Result;   

After execution, we will get the below output:

2. ISNULL function with character string data

The below example uses the ISNULL function. Here we will get the string value ‘Hello’ because it is the first argument. As of ISNULL() function definition, it returns the first argument value when it is NOT NULL:

SELECT ISNULL('Hello', 'Javatpoint') AS Result;  

After execution, we will get the below output:

3. ISNULL function with variables

The below example uses the ISNULL function and return the result using a variable:

DECLARE @expres VARCHAR(45);  

DECLARE @value VARCHAR(25);  

SET @expres = NULL;  

SET @value = 'Javatpoint';  

SELECT ISNULL(@expres, @value) AS Result;

After execution, we will get the below output:

4. ISNULL Function on Table

Here we are going to see how we can replace a column value with a meaningful value when it contains null values. Let us first create a table named ‘Employee’ using the below statement:

CREATE TABLE Employee  

(ID INT IDENTITY PRIMARY KEY,   

 Emp_Name VARCHAR(50) NOT NULL,   

 Age INT NULL,  

 Salary INT NULL  

);

Now, we will insert some values into this table using the below statement:

INSERT INTO Employee (Emp_Name, Age, Salary)  

VALUES ('Kevin Huges', 28, 45000),   

('Rose Bennet', NULL, 36000),   

('Peter Butler', 25, NULL);

When we verify the table, we can see that employees 2 and 3 have a NULL value.

Suppose we want to replace the NULL values of these columns without updating them permanently in the table. In that case, we can use the ISNULL function to replace the NULL values with the specific value.

For example, we want to return the age and salary of the employee with 22 and 25000, respectively, if their columns have NULL values in the Employee table. We can do this by using the following statement:

SELECT   

  ID, Emp_Name,  

  ISNULL(Age, 22) AS Age,   

  ISNULL(Salary, 25000) AS Salary   

 FROM Employee;

After execution, we will get the below output:

If we run the query with the ISNULL function for the column where no column has NULL values in the table, this query will return the actual values of the rows.

For example, we have updated the salary of the employee whose ID=2 as follows:

UPDATE Employee SET Salary = 65000 WHERE ID=2;  

Again, if we execute the ISNULL function, it does not change the salary column. See the below output:

5. ISNULL with aggregate functions

SQL Server also enables us to use the aggregate functions such as SUM, AVG with the ISNULL function. Suppose we might need to get the sum of a salary column present in the Employee table, and if any salary column has NULL, it will be replaced with 25000 before adding the salaries.

Before performing the aggregate methods, we will update the employee salary with NULL whose id is 2, using the below query.

UPDATE Employee SET Salary = NULL WHERE ID=2;  

The below example first replaces the NULL value with 25000 and then performed the SUM function on it. See the below output:

SELECT SUM(ISNULL(Salary, 25000)) AS Sum_of_Salaries FROM Employee;  

After execution, we will get the below output:

Similarly, the ISNULL function can be used to replace NULL values and then return the average value with AVG() function. See the below statement:

SELECT AVG(ISNULL(Salary, 25000)) AS Avg_of_Salaries FROM Employee;  

After execution, we will get the below output:

Difference between SQL Server ISNULL with IS NULL

The ISNULL and IS NULL functions are both different in SQL Server. We use the ISNULL function when we have a need to replace the NULL values with a specified value. On the other hand, we use the IS NULL function when we want to identify NULL values in a table.

Let us see the below example to illustrate their differences.

Suppose we want to get the employee data from the ‘Employee’ table that contains NULL values in the Salary column. To get this type of information, we should use the IS NULL function in the WHERE clause as follows:

SELECT * FROM Employee WHERE Salary IS NULL;  

It will return the employee detail whose salary is NULL:

Now, if we try to get this type of information with ISNULL function, SQL Server through the following error:

SELECT * FROM Employee WHERE Salary ISNULL(NULL, 0;  

Here is the error:

Thus, it’s clear that SQL Server does not allow us to use the ISNULL to find NULL values.