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:
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:
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.
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:
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:
I could use CASE, but which is easier to read?
I think NULLIF is, if you know how the function works.
SQLNewBlogger
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.