SQL ISNULL function

This article explores the SQL ISNULL function to replace NULL values in expressions or table records with examples.

Introduction

We define the following parameters while designing a table in SQL Server

Data types for a particular column
Allow NULL or Not Null values in SQL Server

CREATE TABLE table_name

(

column1 datatype [ NULL],

column2 datatype [NOT NULL ],

...

);

If we do not provide any value for column allow NULL values, SQL Server assumes NULL as default value.

CREATE TABLE Employee

(EmployeeID INT IDENTITY(1, 1) NOT NULL,

EmployeeName VARCHAR(50) NOT NULL,

EmployeeSalary INT NULL

);

Let’s insert a few records in the Employee table.

INSERT INTO Employee

(EmployeeName,

EmployeeSalary

)

VALUES

('Rajendra',

55645

);

INSERT INTO Employee(EmployeeName)

VALUES('Rajendra');

View the records in the table, and we can see a NULL value against EmployeeID 2 because we did not insert any value for this column.

We might have a requirement to replace NULL values with a particular value while viewing the records. We do not want to update values in the table. We can do this using SQL ISNULL Function. Let’s explore this in the upcoming section.

SQL Server ISNULL Function overview

We can replace NULL values with a specific value using the SQL Server ISNULL Function. The syntax for the SQL ISNULL function is as follow.

SQL Server ISNULL (expression, replacement)

Expression: In this parameter, we specify the expression in which we need to check NULL values
Replacement: We want to replace the NULL with a specific value. We need to specify replacement value here

The SQL Server ISNULL function returns the replacement value if the first parameter expression evaluates to NULL. SQL Server converts the data type of replacement to data type of expression. Let’s explore SQL ISNULL with examples.

Example 1: SQL Server ISNULL function in an argument

In this example, SQL ISNULL function returns the second argument value because the first argument is NULL:

1	SELECT ISNULL(NULL, 100) result;

In the following examples, we can see the following.

If the first argument is NULL, it returns the value of the second argument.
If the first argument is NOT NULL, it returns the first argument value as output.

1 2	SELECT ISNULL(NULL, 'SQLServer') result; SELECT ISNULL('SQLShack', 'SQLServer') result;

Example 2: SQL Server ISNULL to replace a value in existing column values

At the beginning of this article, we created the Employee table and inserted NULL values in it. We can use SQL ISNULL to replace existing NULL values with a specific value.

For example, we want to return Employee salary 10,000 if it is NULL in the Employee table. In the following query, we used SQL ISNULL function to replace the value.

SELECT Employeeid,

ISNULL(EmployeeSalary, 10000) EmployeeSalary

FROM Employee;

Let’s update the NULL value in the Employee table using the following update statement.

1	Update Employee set EmployeeSalary =65656 where EmployeeID =2

We do not have any NULL value in the table now; therefore if we run the query with SQL Server ISNULL, it returns actual values of the rows.

Example 3: SQL Server ISNULL with aggregate functions

We can use SQL ISNULL with aggregate functions such as SUM, AVG as well. Suppose we want to perform sum of EmployeeSalary present in Employee table. If EmployeeSalary is NULL, it should be replaced with 10000 before adding the salaries.

Before we move, update the EmployeeSalary as NULL for EmployeeID 2 using the following query.

1	Update Employee set EmployeeSalary =NULL where EmployeeID =2

In the following query, we replaced the NULL value with value 10000 first and then performed SUM on it. You can visualize it with the following screenshot.

1 2	SELECT SUM(ISNULL(EmployeeSalary, 10000)) FROM Employee;

Similarly, we can use SQL ISNULL function to replace NULL values and calculate the average value with AVG() function.

1 2	SELECT AVG(ISNULL(EmployeeSalary, 10000)) FROM Employee;

Example 4: Difference between SQL Server ISNULL with IS NULL

You might confuse between SQL Server ISNULL and IS NULL. We use IS NULL to identify NULL values in a table.

For example, if we want to identify records in the employee table with NULL values in the Salary column, we can use IS NULL in where clause.

SELECT *

FROM Employee

WHERE EmployeeSalary IS NULL;

In the following screenshot, we cannot use SQL Server ISNULL to find NULL values. We use it to replace NULL values with a specific value. In the second part, we can see the employee having a NULL salary.

Example 5: SQL Server ISNULL to replace the NULL value with a custom message

Consider the example in which we have NULL values in DeliveryAddressLine2 column. We want to print a message in [DeliveryAddressLine2] if the actual values from DeliveryAddressLine2 have NULL values.

Select

[CustomerName],

[AccountOpenedDate],

[DeliveryAddressLine1],

[DeliveryAddressLine2],

ISNULL([DeliveryAddressLine2], 'Address Same as DeliveryAddressLine1') AS DeliveryAddressLine2,

[DeliveryPostalCode]

from customers

You can see the message in DeliveryAddressLine2 column.

Implicit conversion of data type in SQL Server ISNULL

If we have multiple data types in an expression, SQL Server converts the lower precedence data types into a higher precedence data type. SQL Server ISNULL also performs similarly. If SQL ISNULL is not able to escalate the data type, it returns an error message.

As per MSDN, SQL Server uses the following precedence order for data types:

user-defined data types (highest)
sql_variantXML
datetimeoffset
datetime2
DateTime
smalldatetime
date
time
float
real
decimal
money
smallmoney
bigint
int
smallint
tinyint
bit
ntext
text
image
timestamp
uniqueidentifier
nvarchar (including nvarchar(max) )
nchar
varchar (including varchar(max) )
char
varbinary (including varbinary(max) )
binary (lowest)

In the following image, we can all possible implicit and explicit allowed data type conversions. ( Image Reference: Microsoft Docs)

Let’s understand data type precedence with SQL NULL using the following example. In the above table, we can see that the precedence of INT is higher than the timestamp data type.

Execute the following query. In this query, we defined a variable @ID of integer type. We try to replace the NULL value in this parameter with Current timestamp.

1	DECLARE @ID int

We get the following error message. It cannot convert data type from datetime to integer. We can use SQL Convert functions to convert appropriate data type.

Msg 257, Level 16, State 3, Line 2
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

Now, let’s replace data type of parameter @ID from integer to datatime2. In the data precedence table, the precedence of datatime2 data type is higher than the timestamp data type.

1 2	DECLARE @ID DateTime2 SELECT ISNULL(@ID, CURRENT_TIMESTAMP);

Conclusion

In this article, we explored the SQL ISNULL function and its usage in replacing NULL values with a specified value or string. Feel free to ask question or provide comments in the feedback below

Author
Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

Understanding PostgreSQL SUBSTRING function - September 21, 2024
How to install PostgreSQL on Ubuntu - July 13, 2023
How to use the CROSSTAB function in PostgreSQL - February 17, 2023