Rajendra Gupta
SQL Server ISNULL with IS NULL

SQL ISNULL function

May 10, 2019 by

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

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

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

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:

SQL ISNULL function in an argument

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.

SQL ISNULL function

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.

replace a value in existing column values

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

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.

replace a value in existing column values

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.

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.

SQL ISNULL in aggregate functions

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

SQL Server ISNULL in aggregate functions

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.

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.

SQL Server ISNULL with IS NULL

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.

replace the NULL value with existing column values

You can see the message in DeliveryAddressLine2 column.

replace the NULL value with custom message

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:

  1. user-defined data types (highest)
  2. sql_variantXML
  3. datetimeoffset
  4. datetime2
  5. DateTime
  6. smalldatetime
  7. date
  8. time
  9. float
  10. real
  11. decimal
  12. money
  13. smallmoney
  14. bigint
  15. int
  16. smallint
  17. tinyint
  18. bit
  19. ntext
  20. text
  21. image
  22. timestamp
  23. uniqueidentifier
  24. nvarchar (including nvarchar(max) )
  25. nchar
  26. varchar (including varchar(max) )
  27. char
  28. varbinary (including varbinary(max) )
  29. binary (lowest)

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

SQL Server Data type conversion table

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.

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.

Implicit conversion of data types in SQL Server

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

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
168 Views