When querying a SQL Server database, there may be times where you don’t want null values to be returned in your result set. And there may be times where you do want them returned. But there may also be times where you do want them returned, but as a different value.
That’s what the ISNULL() function is for.
ISNULL() is a T-SQL function that allows you to replace NULL with a specified value of your choice.
Example
Here’s a basic query that returns a small result set:
SELECT TaskCode AS Result FROM Tasks;
Result:
Result ------ cat123 null null pnt456 rof789 null
We can see that there are three rows that contain null values.
If we didn’t want the null values to appear as such, we could use ISNULL() to replace null with a different value.
Like this:
SELECT ISNULL(TaskCode, 'N/A') AS Result FROM Tasks;
Result:
Result ------ cat123 N/A N/A pnt456 rof789 N/A
We could also replace it with the empty string:
SELECT ISNULL(TaskCode, '') AS Result FROM Tasks;
Result:
Result
------
cat123
pnt456
rof789
Note that ISNULL() requires that the second argument is of a type that can be implicitly converted to the data type of the first argument. That’s because it returns the result using the data type of the first argument.
Preventing Null Rows from Disappearing
There are some T-SQL functions where null values are eliminated from the result set. In such cases, null values won’t be returned at all.
While this might be a desirable outcome in some cases, in other cases it could be disastrous, depending on what you need to do with the data once it’s returned.
One example of such a function is STRING_AGG(). This function allows you to return the result set as a delimited list. However, it also eliminates null values from the result set. So if we use this function with the above sample data, we’d end up with three results instead of six:
SELECT STRING_AGG(TaskCode, ', ') AS Result FROM Tasks;
Result:
Result ---------------------- cat123, pnt456, rof789
So the three rows containing null values aren’t returned.
In many cases, this is a perfect result, as our result set isn’t cluttered up with null values. However, this could also cause issues, depending on what the data is going to be used for.
Therefore, if we want to retain the rows with null values, we can use ISNULL() to replace the null values with another value:
SELECT STRING_AGG(ISNULL(TaskCode, 'N/A'), ', ') AS Result FROM Tasks;
Result:
Result ------------------------------------- cat123, N/A, N/A, pnt456, rof789, N/A
The COALESCE() Function
The ISNULL() function works in a similar way to the COALESCE() function. So we could replace the above code with this:
SELECT STRING_AGG(COALESCE(TaskCode, 'N/A'), ', ') AS Result FROM Tasks;
And get the same result:
Result ------------------------------------- cat123, N/A, N/A, pnt456, rof789, N/A
However, there are some differences in the way the two functions behave. For more information on the differences, here’s a comparison of COALESCE() and ISNULL() on the Microsoft website.