This article explores the SQL ISNULL function to replace NULL values in expressions or table records with examples.
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
INSERT INTO Employee(EmployeeName)
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:
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.
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.
ISNULL(EmployeeSalary, 10000) EmployeeSalary
Let’s update the NULL value in the Employee table using the following update statement.
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.
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.
SELECT SUM(ISNULL(EmployeeSalary, 10000))
Similarly, we can use SQL ISNULL function to replace NULL values and calculate the average value with AVG() function.
SELECT AVG(ISNULL(EmployeeSalary, 10000))
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.
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.
ISNULL([DeliveryAddressLine2], 'Address Same as DeliveryAddressLine1') AS DeliveryAddressLine2,
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)
- nvarchar (including nvarchar(max) )
- varchar (including varchar(max) )
- 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.
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.
DECLARE @ID DateTime2
SELECT ISNULL(@ID, CURRENT_TIMESTAMP);
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
While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at email@example.com
View all posts by Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
- Auditing in AWS RDS SQL Server - January 20, 2020
- Import JSON data into SQL Server - January 17, 2020
- Access PostgreSQL databases using the Azure Data Studio extension - January 16, 2020