There are many ways we can compare the equivalency of two values in Microsoft SQL Server. We can use the regular equals ( = ) operator in an IF statement, for example, or we can use the handy CASE statement within the column list of a SELECT statement.
But there is another very handy tool we can use to compare two values: The SQL Server NULLIF system function.
In this very brief tutorial, we’ll discuss the SQL Server NULLIF system function and provide examples of how to use it. We’ll discuss these topics:
- What is the SQL Server NULLIF system function?
- The syntax of NULLIF
- Examples of NULLIF
- Tips, tricks, and links
Let’s get into it.
1. What is the SQL Server NULLIF system function?
The SQL Server NULLIF system function takes two expressions as input values. If the two expression are equal, NULL is returned. If the two expressions are not equal, the first expression is returned.
The NULLIF function is great when you need to compare two values to see if they are equivalent.
2. The syntax of NULLIF
The syntax for NULLIF is very simple. It basically follows this layout:
NULLIF(<expression1>, <expression2>)
You would normally use NULLIF in a regular SELECT statement. It would normally appear as an expression amongst all the columns in the column list.
The <expressions> you use can be any scalar value, like a hard-coded string literal, or a column name, or even a call to a Scalar Valued Function.
3. Examples of NULLIF
We’ll take a look at some very simple examples:
A simple example of NULLIF:
We’ll compare two string literal values to see if they are the same:
Obviously, the word ‘Simple‘ is not the same as the word ‘SQL‘. Since the two are not equal, the first expression gets returned.
We’ll change one of the words so that they match:
Superb!
Seeing a result of NULL will sometimes play tricks on my mind. Normally the sight of NULL is a bad thing, but in this case, it’s a valid value!
A more complex example:
I’ll go ahead and show you a more complex example, but understand the idea is the same! NULLIF simply returns NULL if the first expression is equal to the second expression.
Take a look at this crazy query against an Orders table:
This query uses a great tool called a Window Function. We use the MAX aggregate function in a window to basically tell us the most recent order that was placed for each customer.
The column ‘Most Recent Order From Customer’Â tells us the date that the most recent order was placed for that specific customer (I put all the orders for each customer in their own separate boxes so you can see them easier). For example, it looks like the most recent order for Customer # 50 was on 7/24/2021. That value is repeated for each row for that customer.
The most recent order for the next customer, Customer # 55, was on 7/18/2021. Again, that value is repeated for each row for that customer.
This pattern continues for all customers. Each customer’s most recent order is seen in the ‘Most Recent Order From Customer’ column.
Window functions are fun. Take a look at the full beginner-friendly tutorial:
SQL Server Window Functions: An introduction for beginners
The last column uses NULLIF
Our query uses that same window function as the second expression in the NULLIF function:
For each record for a customer, we want to know if it’s OrderDate value is actually the most recent order for that customer.
If the OrderDate matches the ‘Most Recent Order From Customer’ value for that customer, the NULLIF function returns NULL.
If we plug this query into a simple CTE (Common Table Expression), we can very simply see the most recent order for each customer by filtering our results to return rows where the NullIfColumn value is NULL:
Nice! This final result set simply shows us the order details for the most recent order placed by each customer.
(By the way, in the real world, I would have excluded the ‘Most Recent Order From Customer’ column completely. There is no need to perform the window function twice. I just included it to show you how it works)
Again folks, at the end of the day, we simply use NULLIF to tell us if two values are equal!
4. Tips, tricks, and links
There is only one tip you should know about the NULLIF system function:
Tip # 1: Queries using NULLIF can be re-written using a CASE statement instead
If you think about it, you can use the searched version of the CASE statement instead of using NULLIF. Personally, I think it doesn’t read as well, but you can certainly do it. Here’s what our same query would look like using a CASE statement:
Same result, but more code. I just wanted to point out that there is more than one way to achieve your querying goals!
Links:
Here is the official Microsoft documentation on the NULLIF function: NULLIF (Transact-SQL)
Next Steps:
Leave a comment if you found this tutorial helpful!
Do you know why we had to use “IS NULL” and not simply “= NULL”? Check out the tutorial to find out!
Also, NULL can be a tricky thing.
Are you making these 7 mistakes with NULL?
Finally, we talked a lot about Window Functions. You should download the following FREE E-book:
FREE E-book on SQL Server Window Functions!
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, or if you are struggling with a different topic related to SQL Server, I’d be happy to discuss it. Leave a comment or visit my contact page and send me an email!
