Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

NULLIF

Do you use NULLIF?  For me, this command has been seldom used.  Because of that, I have been dabbling with it a bit lately to make sure I have a better understanding of it.

What is it?

This is a function that compares two values.  If the two values are the same, then the result of the operation is a Null matching the datatype of the first expression.  If the two expressions are different, then the result is the value of the first expression.

Seems pretty simple.

In Action

If you look online at MSDN, you can see a couple of examples and a more complete description of what this function is and does.  The MSDN article can be found here.  I wanted something a little different and decided to visualize it differently for my learning.  So here is what I did.

WITH randnums AS (
	SELECT TOP 100
			RowNum = ROW_NUMBER()  OVER (ORDER BY (SELECT 1))
			,FirstVal = ABS(CHECKSUM(NEWID()))%10+1
			,SecVal = ABS(CHECKSUM(NEWID()))%10+1
		FROM Master.dbo.SysColumns t1
			CROSS JOIN Master.dbo.SysColumns t2
	)
 
SELECT RowNum,FirstVal,SecVal, NULLIF(FirstVal,SecVal) AS 'Null if Equal'
	FROM randnums
	ORDER BY RowNum

This will give me a nice random sampling of values to compare.  I simply compare the firstval column to the secondval column.  Both columns are populated with random numbers.  As a part of the result set, I am labeling the comparison field to something descriptive of the field.  I am returning all of the columns so I can see what the values are, and the result of the comparison.  This visualization can help to understand what is happening with the code.  Now I know that if I see a null value, then the two columns are equal.

Comments

Posted by Steve Jones on 1 April 2011

Interesting. I've never really used NULLIF in the real world. Just testing.

I'd be interested to know if you find a real use for this in production code.

Posted by gregocon.nell on 2 April 2011

An even more basic use is simply to avoid those pesky divide-by-zero problems.

Posted by david.blythner on 3 April 2011

Can you give an example with the divide by zero problem. how would you go about it

Posted by ivanarj on 3 April 2011

Perhaps something like A / NULLIF(B,0) which yields NULL instead of error.

Posted by gregocon.nell on 3 April 2011

If your denominator can be zero, nullif gives you the option of changing the zero to a null. Anything divided by null is null.

Posted by Wim Kok on 6 April 2011

I've used NULLIF to suppress high- and low-values (e.g. 1753-01-01 and 9999-12-31 date value) in reports and Excel-sheets.

Posted by Enrico on 6 April 2011

Combining NULLIF with ISNULL to remove the NULL value from divide by zero problem and replace it with another value (in this example with a zero):

ISNULL(A / NULLIF(B,0), 0)

Posted by Enrico on 6 April 2011

Ignoring perfomance issues, how about an using NULLIF and ISNULL as an alternative to the simple CASE statements:

select isnull( nullif(A,B), C)

select case when(A=B) then C else A end

Posted by Knut Boehnert on 6 April 2011

NULLIF makes a lot more sense if used within a COALESCE. For example I have to distinguish between 3 results from 3 tables linked as outer joins but the second result can be something I don't want at all.

So

COALESCE( table1.result, NULLIF(table2.result, <unwanted result), table3.result, 'Default value')

yields either

- table1.result if it is not null

- table2.result if it is not null and not equals the <unwanted result>

- table3.result if it is not null

- 'Default value' as a last resort

In my company we are using this frequently to verify user input to get to the result that is most likely correct. These business rules are all documented of course.

Posted by Phil Factor on 6 April 2011

I don't believe there is a use for it, since the CASE statement can do it and it is much more versatile, and easier to read anyway. I haven't noticed much in it as far as performance goes.

Posted by David McKinney on 6 April 2011

Can be used to replace a certain value by a null value.  In the case below an empty string (or in previous example a zero).

nullif(myfield,'')

I agree that the same can be achieved with a case statement, however there are occasions (and days) where I prefer the NULLIF syntax.  I accept though that it can be a wee bit confusing at times.

Above is equivalent to

case myfield when '' then null else myfield end

Posted by yuri-1153832 on 6 April 2011

it's useful when you are handling "dirty" data, for example, which comes through an import channel of some kind. Let's say you've built a handling procedure, which logic depends on a field's value being NULL or not.

.. AND PreviousConvictions IS NULL

and then you find out that, let's say, because of imperfect importing filter you have values like '' (empty string) and 'none' in this field, so, the quick fix is

.. AND NULLIF(NULLIF(PreviousConvictions, ''), 'none') IS NULL

CASE WHEN is by all means more versatile, but it is also a lot bulkier visually

Posted by aphillippe on 6 April 2011

as Knut Boehnert/David McKinney said above, I use NULLIF along with COALESCE as a nice and tidy way to clean up nulls and empty values in one go, e.g.:

COALESCE(NULLIF(Nominal_decimals, ''), 0)

See what that would look like in a case statement. Too messy for my liking, and only NULLIF is only confusing if you don't know what it does (isn't everything?)

Posted by preitano on 6 April 2011

I don't agree with you Phil. Perhaps the reason is that I've been using NULLIF and COALESCE since my first T-SQL programming days when I didn't know the CASE statement.

Now I use a mix of both methods but still I prefer NULLIF.

Here is at least an example in which NULLIF is better than CASE. Try setting @TotalAmount to 100, 0 and null (or not to set it at all)

DECLARE @TotalAmount int

SELECT COALESCE(CAST(NULLIF(@TotalAmount,0) as varchar(10)),'[no value]')

SELECT CASE @TotalAmount

WHEN NULL THEN '[no value]'

WHEN 0 THEN  '[no value]'

ELSE CAST(@TotalAmount as varchar(10))

END

Posted by Adam Belebczuk on 6 April 2011

Thanks for this article and all of the comments on it. I didn't even know that NULLIF existed until I read this, and it seems like a much simpler solution to divide by 0 errors than what I'm currently doing.

Posted by Jason Brimhall on 6 April 2011

re: the performance of nullif.

Brad Schulz did some performance comparisons on my mainstream blog for this article in the comments.  In short, the Nullif is translated to a coalesce by the optimizer and combining it with coalesce really messes with the optimizer.  Combining a case statement with coalesce ends up being cleaner.

jasonbrimhall.info/.../nullif

Posted by Phil Factor on 6 April 2011

--ok it's a bit longer, but not that much longer.

DECLARE @TotalAmount INT

SELECT @TotalAmount=-10

SELECT COALESCE(CAST(NULLIF(@TotalAmount,0) as varchar(10)),'[no value]')

SELECT COALESCE(CAST(CASE WHEN COALESCE( @TotalAmount,0)<>0 THEN @TotalAmount END AS VARCHAR(10)),'[no value]')

Posted by lvokoun on 6 April 2011

Okay, I just learned something new today.  Does that mean I get to go home already?

Posted by Jason Brimhall on 6 April 2011

Correction on my last comment substitute case for that first coalesce.

Posted by Dave Vroman on 6 April 2011

We use it on an old / poorly designed database where we have values that can have NULLs and the NULL is significant.

i.e. NULLIF(Visible, 'Y') IS NULL replaces the code

(Visible IS NULL OR Visible = 'Y')

Posted by tfifield on 6 April 2011

I know you can use a CASE statement to do the same thing and mostly I do.  However sometimes it's easier to use NULLIF because it will return null if the 2 arguments are the same OR the first argument is NULL.  The code is a little more compact.

All 3 of these return NULL:

DECLARE

 @Str VARCHAR(10)

SELECT NULLIF(@Str, '')

SELECT NULLIF(@Str, 'A')

SET @Str = 'A'

SELECT NULLIF(@Str, 'A')

Posted by Jason Brimhall on 6 April 2011

tfifield - thanks for the info.

Everybody else, thanks as well for great info and discussion.

Leave a Comment

Please register or log in to leave a comment.