Problem
There are many date, string, numeric and aggregate functions built in to SQL Server that make our lives easier when writing queries. These SQL functions are broken up into different categories and in this tutorial we will look at the system function ISNULL().
Solution
Note: Some examples below are based on the AdventureWorks2017 database schema, those that are not have complete DDL noted so you can try the examples yourself (these must be run in a separate database, not in AdventureWorks2017). All examples were tested using SQL Server 2019 (15.0.2070.41).
The built in system functions in Microsoft SQL Server are used to perform system operations and return information about objects or settings in SQL Server. The ISNULL() function is used to check if a value is null and if it is will return the replacement value specified when calling the function.
Understanding NULL Values
Before we get into the details of the function it is important to make sure we understand what a NULL value is in SQL Server. A NULL value is a special marker in column to denote that a value does not exist. It is important to understand that a NULL column value is different than having a blank (empty string) or 0 value in a column. eg. ‘ ‘ or (empty string) <> NULL, 0 <> NULL.
Let’s take a look at a few examples to illustrate these points. The following TSQL will create a demo table and load it with some sample data for both integer and varchar typed columns.
CREATE TABLE [dbo].[ISNULLExample] (
[id] [int] NOT NULL IDENTITY(1,1),
[comment] [varchar](50) NOT NULL,
[intdata] [int] NULL,
[varchardata] [varchar](15) NULL);
INSERT INTO [dbo].[ISNULLExample] (comment) VALUES ('null in int and varchar');
INSERT INTO [dbo].[ISNULLExample] (comment,intdata) VALUES ('0 in int',0);
INSERT INTO [dbo].[ISNULLExample] (comment,varchardata) VALUES ('empty string in varchar','');
INSERT INTO [dbo].[ISNULLExample] (comment,intdata,varchardata) VALUES ('real values in in and varchar',25,'valid string');
Now that we have some data let’s take a look at how the ISNULL function interprets each of these values and how equality operator and IS behave differently as described above.
SELECT comment, intdata, ISNULL(intdata,-1),varchardata, ISNULL(varchardata,'N/A') FROM [dbo].[ISNULLExample];
The query above will replace any NULL values in the intdata column with a -1. It will also replace any NULL values in the varchardata column with ‘N/A’.
SELECT * FROM [dbo].[ISNULLExample] WHERE intdata IS NULL;
SELECT * FROM [dbo].[ISNULLExample] WHERE intdata = NULL;
We can also see how SQL Server handles IS NULL differently than equals NULL in the results below for the int data type.
SELECT * FROM [dbo].[ISNULLExample] WHERE varchardata IS NULL;
SELECT * FROM [dbo].[ISNULLExample] WHERE varchardata = NULL;
We can also see how SQL Server handles IS NULL differently than equals NULL in the results below for the varchar data type.
SQL Server ISNULL Syntax
The syntax for the ISNULL() function is very straightforward. The first argument is the expression to be checked. In most cases this check_expression parameter is a simple column value but can be a literal value or any valid SQL expression. The second argument is the value that will be returned from the function if the check_expression is NULL. It is important to note that the replacement value must be of a data type that is implicitly convertible to the same type as the check expression. So for example, if the column is an int data type, the replacement value must be an integer and not characters.
ISNULL ( check_expression, replacement_value )
When to (and not to) use ISNULL()
The ISNULL() function can be used anywhere that the SQL syntax allows for the use of a function but the main use case for this function is in the SELECT list of a SQL query when you want to convert any NULL values being returned to something more descriptive.
The following example displays the string ‘NO TRACKING’ when the row does not have any CarrierTrackingNumber defined. In the first query we do not use the function and we can see that there is no value for SalesOrderID 43697. In the second query we add the ISNULL function and get a much more meaningful result.
SELECT SalesOrderID,CarrierTrackingNumber
FROM [Sales].[SalesOrderDetail]
WHERE SalesOrderID in (43696,43697);
SELECT SalesOrderID, ISNULL(CarrierTrackingNumber,'NO TRACKING')
FROM [Sales].[SalesOrderDetail]
WHERE SalesOrderID in (43696,43697);
Another good use case for this function is when inserting data into a table. Generally speaking, all data should be sanitized by the application but there could be cases in scripting when you want to covert a value before inserting into a table. The following example will insert a default date far in the future if a NULL date is passed in for the DiscontinuedDate column.
DECLARE @DiscontinuedDate datetime
INSERT INTO [Production].[Product]
([Name],[ProductNumber],[MakeFlag],[FinishedGoodsFlag],[SafetyStockLevel],[ReorderPoint],
[StandardCost],[ListPrice],[DaysToManufacture], [SellStartDate],[DiscontinuedDate],
[rowguid],[ModifiedDate])
VALUES
('Adjustable Race 2','AR-5382',0,0,1000,500,
0.00,0.00,0,’2008-04-30',ISNULL(@DiscontinuedDate,'1999-01-01'),
'694215B7-08F7-4C0D-ACB1-D734BA44C0C9',GETDATE());
SELECT [Name],[ProductNumber],[DiscontinuedDate],[rowguid],[ModifiedDate]
FROM [Production].[Product]
WHERE rowguid='694215B7-08F7-4C0D-ACB1-D734BA44C0C9';
The above examples showed cases where it was beneficial to use the ISNULL() function. When it comes to SQL DELETE and UPDATE statements, this is one area that you want to avoid using the function. In both of these cases you are most concerned with the performance of finding the records to be updated and using functions in a WHERE clause will limit the query engine’s ability to use an index. This will in almost all cases slow down the performance of the update/delete statement.
Using an UPDATE statement as an example (SQL DELETE statements would exhibit very similar behavior), let’s create the following index on the Product.Color column and use two different methods for updating NULL values in this column.
CREATE NONCLUSTERED INDEX [IX_Product_Color] ON [Production].[Product] ([Color] ASC);
Using the ISNULL() function we could write the following query which will update any row in the Product table that has a NULL value in the Color column to an empty string.
UPDATE [Production].[Product] SET Color = ISNULL(Color,'');
The drawback to this method as you can see from the query plan and output below is that it actually updated every record in the table.
A better option in this case would be to use the special IS operator in the WHERE clause of the update statement which would allow the query engine to use an index to perform the update.
UPDATE [Production].[Product] SET Color = '' WHERE Color IS NULL;
You can see from the query plan and output below that it is now performing an index seek rather than the scan performed when using the ISNULL function.
As said above, a good rule of thumb is to try and avoid using functions in a WHERE clause as it limits the query engine’s ability to use an index effectively. But this then begs the question: What do we do if we want to search for some particular value as well as NULL? Using the following example, let’s write a query to find all products that are black or have no value. This could be written as follows using the ISNULL() function.
SELECT COUNT(1) FROM [Production].[Product] WHERE ISNULL(Color,'Black') = 'Black';
As you can see from the query plan below this will use the index but has to perform a scan of the entire index as was happening in the update example.
If we rewrite this query using IS NULL and an OR condition it will now perform an index seek and be much faster. (Note: I’ve excluded the rightmost portion of the plan in the interest of space as it was irrelevant anyway).
SELECT COUNT(1) FROM [Production].[Product] WHERE Color IS NULL or Color = 'Black';
Using ISNULL() to Join Tables
Using the ISNULL() function when joining two tables can also be very helpful. While you can use LEFT or RIGHT join to include NULL values in the join predicate when using those clauses the row does not actually join to another row in the corresponding table, you just get NULL values for all columns. You can use the ISNULL() function to specify a hard-coded value or even use another column in the table for the join condition.
Below is an example that shows how you can return the personal address of a person and if that does not exist return their business address.
CREATE TABLE [dbo].[Person] (
[id] [int] NOT NULL IDENTITY(1,1),
[name] [varchar](25),
[addressid] [int],
[businessaddressid] [int]);
CREATE TABLE [dbo].[Address] (
[id] [int] NOT NULL IDENTITY(1,1),
[streetnumber] [int],
[streetname] [varchar](30),
[city] [varchar](30));
INSERT INTO [dbo].[Address] ([streetnumber],[streetname],[city]) VALUES (23,'Main St','Seattle');
INSERT INTO [dbo].[Address] ([streetnumber],[streetname],[city]) VALUES (2,'Big St','New York');
INSERT INTO [dbo].[Address] ([streetnumber],[streetname],[city]) VALUES (2453,'This Rd','Chicago');
INSERT INTO [dbo].[Address] ([streetnumber],[streetname],[city]) VALUES (973,'Testing Ave','Toronto');
INSERT INTO [dbo].[Person] ([name],[addressid],[businessaddressid]) VALUES ('Jim',1,2);
INSERT INTO [dbo].[Person] ([name],[addressid],[businessaddressid]) VALUES ('Jennifer',NULL,2);
INSERT INTO [dbo].[Person] ([name],[addressid],[businessaddressid]) VALUES ('Bob',3,NULL);
SELECT P.[name],A.[streetnumber],A.[streetname],A.[city]
FROM [dbo].[Person] P INNER JOIN [dbo].[Address] A
ON ISNULL(P.[addressid],P.[businessaddressid]) = A.[id];
Using ISNULL() in Stored Procedures
Another handy use case for the ISNULL() function is for sanitizing input parameters inside a stored procedure. At the start of your stored procedure you can check if any parameters are NULL and if so, assign a default value so you don’t have to make the same call many times throughout the stored procedure. Below is a simple example which sets the Quantity to be checked to 0 if the value passed in is NULL.
CREATE PROCEDURE CheckInventory
@p1 int
AS
BEGIN
SET NOCOUNT ON;
SET @p1=ISNULL(@p1,0);
SELECT * FROM [Production].[ProductInventory] WHERE Quantity > @p1;
END
GO
Using ISNULL() in a View
Since a view is really just a stored SQL SELECT statement, any of the examples demonstrated above that involved a SELECT statement can be applied within a VIEW definition. Below is a simple example of a view that displays the string “None” if the AdditionalContactInfo is NULL.
CREATE VIEW [HumanResources].[vEmployee_ContactInfo]
AS
SELECT
p.[FirstName]
,p.[LastName]
,pp.[PhoneNumber]
,pnt.[Name] AS [PhoneNumberType]
,ea.[EmailAddress]
,ISNULL(cast(p.[AdditionalContactInfo] as varchar(4000)),'None') [AdditionalContactInfo]
FROM [Person].[Person] p
LEFT OUTER JOIN [Person].[PersonPhone] pp
ON pp.BusinessEntityID = p.[BusinessEntityID]
LEFT OUTER JOIN [Person].[PhoneNumberType] pnt
ON pp.[PhoneNumberTypeID] = pnt.[PhoneNumberTypeID]
LEFT OUTER JOIN [Person].[EmailAddress] ea
ON p.[BusinessEntityID] = ea.[BusinessEntityID];
Using ISNULL() in a Trigger
Using the ISNULL() function in a trigger can let you update column values if they are not specified in the INSERT statement. A good example of this is using our Person table structure from above. We know from before that you do not have to specify a businessaddressid as shown below.
SELECT * FROM [dbo].[Person] WHERE id=3;
We could create a trigger on this table as follows that will use the ISNULL() function and replace the NULL value for businessaddressid with the value specified for the addressid.
CREATE TRIGGER TR_Person_BusinessAdressID ON [dbo].[Person]
INSTEAD OF INSERT
AS
SET NOCOUNT ON
INSERT INTO [dbo].[Person] ([name],[addressid],[businessaddressid])
SELECT I.[name],I.[addressid],ISNULL(I.[businessaddressid],I.[addressid])
FROM inserted I
GO
Now when the following INSERT statement is called with a NULL businessaddressid the addressid is copied into this column.
INSERT INTO [dbo].[Person] ([name],[addressid]) VALUES ('Sheila',3);
SELECT * FROM [dbo].[Person] WHERE name = 'Sheila';
Using ISNULL() in a Computed Column
Like any other function (as long as it’s deterministic) the ISNULL() can be used in a computed column. Let’s take a look at a simple example from the Person table where we can use this function to store a person’s full name. If you were to write a query to concatenate all the name related columns to display the full name you could do this but it would require manually adding the ISNULL() function around every column that is nullable as follows.
SELECT [Title],[FirstName],[MiddleName],[LastName],[Suffix]
,ISNULL([Title] + ' ','') + [FirstName] + ' ' + ISNULL([MiddleName] + ' ','') + [LastName] + ISNULL([Suffix],'') AS FullName
FROM [AdventureWorks2017].[Person].[Person];
As you can see this is quite cumbersome and if you are doing the operation a lot a much simpler approach would be to add the following persisted computed column to the table.
ALTER TABLE [Person].[Person] ADD FullName AS ISNULL([Title] + ' ','') + [FirstName] + ' ' + ISNULL([MiddleName] + ' ','') + [LastName] + ISNULL([Suffix],'') PERSISTED;
With the new computed column available the above query can be rewritten and it’s much easier to read.
SELECT [Title],[FirstName],[MiddleName],[LastName],[Suffix],[FullName]
FROM [AdventureWorks2017].[Person].[Person];
Next Steps
- Read more on SQL Server System Functions
- Read more tips about NULL functions:
- Read more tips about SQL COALESCE:
- Read more tips about the CASE Expression
