Using NULLIF–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I ran across the NULLIF() function recently, and I realized I’d never used it in code. It’s an interesting function, one that I didn’t think would be useful, but I found a couple places.

NULLIF Behavior

This function is essentially short for “return a null if these two values are equal.” There are two parameters you pass in and if they are equal, you get a NULL back. Somewhat strange function, but here are a few examples:

2021-09-20 15_42_19-SQLQuery2.sql - ARISTOTLE_SQL2017.sandbox (ARISTOTLE_Steve (62))_ - Microsoft SQ

The interesting one is that 1 and NULL come back with the first value. We can’t determine if NULL is equal to 1, so we assume not.

Using This Function

When would you use this? As I said, I have never thought to use this, but I did find a couple interesting items. A mixture of NULL and a certain value is one place, if you can use the NULL. For example, let’s say I have some data in a table:

2021-09-20 15_44_46-SQLQuery2.sql - ARISTOTLE_SQL2017.sandbox (ARISTOTLE_Steve (62))_ - Microsoft SQ

I have some blanks and some NULL values. Suppose I want to query and show the category, but if that is a NULL or blank string, show the SubCat instead. I can do this with a CASE, but that get’s ugly. NULLIF makes this easy to read.

2021-09-20 15_45_56-SQLQuery2.sql - ARISTOTLE_SQL2017.sandbox (ARISTOTLE_Steve (62))_ - Microsoft SQ

The other interesting place I thought of here was with aggregates and potentially filtering out some values. Aggregates tend to ignore NULL, so what if I have this data:

2021-09-20 15_48_24-SQLQuery2.sql - ARISTOTLE_SQL2017.sandbox (ARISTOTLE_Steve (62))_ - Microsoft SQ

Suppose I want the average sale, but not with the zero values. Those might be returns, and we don’t want to skew our average. I could use NULLIF to make this easy to code. Notice the short code below and the difference from the straight average:

2021-09-20 15_49_03-SQLQuery2.sql - ARISTOTLE_SQL2017.sandbox (ARISTOTLE_Steve (62))_ - Microsoft SQ

I could use CASE, but which is easier to read?

2021-09-20 15_50_20-SQLQuery2.sql - ARISTOTLE_SQL2017.sandbox (ARISTOTLE_Steve (62))_ - Microsoft SQ

I think NULLIF is, if you know how the function works.


This was a function I stumbled on and wasn’t sure how to read. I spent about 10-15 minutes searching around the Internet looking for a reason to use this code. I saved the link for them and added it into the post. I spent about 10 minutes creating a little code example and then running it.

I then wrote this post, which was about 10 minutes, mostly because I used screen shots for code, which were quick to grab and paste in.

This is a nice example of learning something, understanding how it works, and then thinking where it could be useful.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.