Handling NULL with NULLIF and ISNULL

Handling NULL values can be a challenge and may lead to unexpected query results when mixed in with non-NULL values. Thankfully, there are a few SQL Server functions devoted to handling NULL values. Let’s look at two of them. The first, NULLIF, will help you to return a NULL value. The second, ISNULL, will help you with an alternative to a NULL value if you need another value in its place.

NULLIF Usage

NULLIF can be used to evaluate two values and return NULL if they are equal. If the values are not equal, SQL Server will return the value of the first expression.

If we run the following, we’ll see “1” get returned because it’s the first expression used, and it does not match the second expression “2”:

SELECT NULLIF(1, 2) AS 'NULLIF_DIFFERENT';

If we change our NULLIF(1, 2) to NULLIF(1, 1), we will see NULL returned because the two expressions match:

SELECT NULLIF(1, 1) AS 'NULLIF_SAME';

Another scenario where you may want NULL is if you’re working with empty strings. Let’s say you want to check a value for an empty string and return NULL instead. Pass in the value that may be an empty string as the first expression, an empty string as the second expression, and you’ll get back a NULL result:

DECLARE @String AS VARCHAR(50) = '';
 
SELECT NULLIF(@String, '') AS 'NULLIF_Empty_String';

ISNULL Usage

Let’s explore ISNULL. ISNULL will check the first expression for a NULL value. If that first expression is NULL, the second expression will be returned in its place. If the first expression is not null, then that first expression is returned.

Imagine we’re querying data for comic book creators. Maybe the artist for some comics is unknown and stored as NULL in the database.

SELECT Id, Artist_Last
FROM Comic;
GO

When we query the Comic table, we don’t want to see NULL values. Maybe we don’t want to see it in the application. Maybe NULL values will break something in an application. Whatever the reason, we can use ISNULL to return a value besides NULL:

SELECT Id, ISNULL(Artist_Last, 'Artist Unknown') AS 'Artist_Last'
FROM Comic;
GO

Using ISNULL, we returned “Artist Unknown” instead of NULL.

NULLify NULL Problems

The examples above show a couple of options for managing values. If you want to check data and return a NULL value, you can use the NULLIF function. If you want to check for a NULL value and return a different value, use the ISNULL function.

Thanks for reading!