Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at the jQuery Conference 2010 (Boston, MA) with:

Using NULLIF() To Prevent Divide-By-Zero Errors In SQL

By Ben Nadel on
Tags: SQL

Boyan Kostadinov just sent me a cool link to an article that is the final part in a four part series that discusses the SQL NULL value. I haven't read the first three parts yet, but there is a really cool tip in the fourth part on using NULLIF() to prevent divide-by-zero errors in a SQL call.

The idea here is that, as with any other form of math that I know of, you cannot divide by zero in a SQL call. Therefore, running this code:

  • <!--- Do SQL division with no error protection. --->
  • <cfquery name="qDivision" datasource="#REQUEST.DSN.Source#">
  • SELECT
  • ( 45 / 0 ) AS value
  • ;
  • </cfquery>

... results in a SQL error being thrown:

Error Executing Database Query. [Macromedia] [SQLServer JDBC Driver] [SQLServer] Divide by zero error encountered.

To prevent this sort of error from being thrown, author Hugo Kornelis suggests using a NULLIF() in the divisor of the equation. NULLIF() takes two arguments and returns NULL if the two values are the same and can be used to turn the divisor from a zero into a NULL which, in turn, will force the entire equation to become NULL. Therefore, running this code:

  • <!--- Do SQL division with divide-by-zero protection. --->
  • <cfquery name="qDivision" datasource="#REQUEST.DSN.Source#">
  • SELECT
  • ( 45 / NULLIF( 0, 0 ) ) AS value
  • ;
  • </cfquery>
  •  
  • <!--- Output resulting value. --->
  • [ #qDivision.value# ]

... we get the following output:

[ ]

Here, the NULLIF( 0, 0 ) returns NULL since zero is equal to zero, which gets the SQL statement to return NULL, which gets ColdFusion to show an empty string. This is a seemingly pointless example since both zero values are hard coded, but imagine if this were a user-entered value, or even better yet, a SQL aggregate or other calculated value (such as might be used in a report or data mining exercise).

Now, let's say you want to take this one step further and provide a default value for the equation if NULL is encountered (A default value, though not entirely accurate might make your consuming code more compact as it won't have to deal with exception cases). To set a default value, we could use the ISNULL() or COALESCE() functions:

  • <!---
  • Do SQL division with divide-by-zero protection. But this,
  • time, let's provide a default value if the division is
  • not valid.
  • --->
  • <cfquery name="qDivision" datasource="#REQUEST.DSN.Source#">
  • SELECT
  • (
  • ISNULL(
  • (45 / NULLIF( 0, 0 )),
  • 0
  • )
  • ) AS value
  • ;
  • </cfquery>
  •  
  • <!--- Output resulting value. --->
  • [ #qDivision.value# ]

Here, we are performing the division as we were above, but then, if that equation returns NULL, our ISNULL() function is going to catch that and return zero as its default value. Therefore, running the above code, we get the following output:

[ 0 ]

As someone who runs a ton of reports on database table (albeit, not in any educated way), this is going to come in very handy. I find that in most cases, having a zero is graphically equivalent to NULL and a whole lot easier to deal with.



Reader Comments

Niiiiiiiiiiiice!

I've had this problem in strange statistics data for a long time and its always been a little bit tricky to over come, this is a nice solution, and its all done in SQL too.

In the past I've pulled the math out of SQL and had CF do it as I had more control over the devision by zero, this makes a hell of a lot more sense.

Nice, Ben!

Thanks,

Rob

Reply to this Comment

@Rob,

Yeah, I feel your pain. I used to use CASE statements in the divisor. It worked, but it was just wordy and distracting. I find this to be much more straight forward and readable.

Reply to this Comment

Is this function specific to SQL Server or will it work on other databases as well? (Oracle, MySQL, etc.)

Reply to this Comment

@Chad,

I think NULLIF() is standard. Not sure about ISNULL(). I think COALESCE() is more standard than ISNULL().

Reply to this Comment

Excellent find! Wish I would have known about this a long time ago - I've always just used a case statement:

case
when isNull(divisor, 0) = 0
then 0
else numerator/divisor end as value

But this seems much nicer!

Reply to this Comment

@Ivan,

I would guess that CASE would be faster only because it is inline rather than a method call. But, just a guess.

Reply to this Comment

Thanks!!! This helped with a very tough calculation. Wasn't even aware this function was out there.

Reply to this Comment

Hi,

Excellent find!

Using this I soved my problem. For e.g
sum(objid)/nullif(count(units_purch),0)

where count(units_purch) return 0 value.

However I've one question can I solve this problem using CASE statement. If yes, then how?

Thanks in advance!

Regards,
Aakansha

Reply to this Comment

@Aakansha,

Yeah, the nullif() is really just a short hand for the CASE statement. CASE statements are powerful and can be used just about anywhere:

SUM( objid ) / (
CASE
WHEN COUNT( units_purch ) = 0
THEN NULL
ELSE COUNT( units_purch )
END
)

As you can see, NULLIF() is a lot easier (and prevents you from having to calculate count() twice.

Reply to this Comment

I just updated my script with this code and it worked like a clock. This will save me gobs of time plus keep my code less complicated. Thanks a million! Rock On!

Reply to this Comment

VERY late to the party here, but I had occasion to work on a Divide By Zero error today, and came across your post.

With MSSQL (2005 anyway) you can add these two lines ahead of the query that could potentially fail with a DBZ error:

SET ARITHABORT OFF
SET ANSI_WARNINGS OFF

With both ARITHABORT and ANSI_WARNINGS set to OFF, SQL Server will return a NULL value in a calculation involving a divide-by-zero error. To return a 0 value instead of a NULL value, you could still put the division operation inside the ISNULL function:

SET ARITHABORT OFF
SET ANSI_WARNINGS OFF

SELECT ISNULL([Numerator] / [Denominator], 0)

Just one more way to skin the cat. I would assume one would want to use this solution with care, especially when dealing with multiple queries in one request... experimentation is certainly in order.

HTH

Marc

Reply to this Comment

@Marc,

Oh cool. I feel like with every SQL server release, they're just adding more cool stuff. I've been using MySQL a lot lately and there's even more stuff in there than I realize. I keep meaning to just read through the docs.

Reply to this Comment

Watch it. NULLIF in SQL SERVER 2000 is buggy!

SELECT ISNULL(NULLIF('', ''), 6)
gives: *

SELECT ISNULL(NULLIF('', ''), 'abc')
gives: empty string

Reply to this Comment

Thanks for this, over the last couple of years I find myself coming back to this page to look at how to avoid this problem.

Reply to this Comment

I'm attempting to use this feature when calculating the average for a value, but I'm not certain if my syntax is correct as CF throws an error:

avg (isNull((((docunitprice * orderqty)-((c.avglaborcost + c.avgburdencost + c.avgmaterialcost + c.avgsubcontcost + c.avgmtlburcost)*d.orderqty))/ nullif(docunitprice * orderqty),0))) as PercValueAdd

Does this work within a function?

Reply to this Comment

hi.. I am facing a problem. I have a online form. when I fill this form and submit. my form submited well without any error. but when I went to the C-Panel for to check and Grab out this form data. I am finding values 0,0,0,0 in all fields.for example.
I have 6 fields created in my form.
1 NAME 2.CITY NAME 3.E-mail id 4.Contact No 5.Pass and 6.Comments Area in my form. but when I go the C-panel for to get out the data from above field after completing form. I received values 0,0,0,0,0, in all fields. please help me out.

Reply to this Comment

You, sir, RULE!

This has been bugging me for soo long! A full hour of crunching stopped becuase of a 0 on a single row..

Excellent solution!

Reply to this Comment

Nice! I was looking for a solution solving divide by zero problem without using case (the query was already too complex) and this is THE solution!

Reply to this Comment

Nice article and useful tip.

However, slightly concerned about your concluding remark: ...in most cases, having a zero is graphically equivalent to NULL and a whole lot easier to deal with.

In general, it is statistical best practice to distinguish between zeros and nulls because very often they mean two very different things. Consider a situation in which 0 is bad and non-zero is good. Management is interested in the percentage of zeros out of the total number of cases. Typically, no one thinks to specify that nulls should be excluded. But encountering the nulls prompts the question. By automatically letting 0 be the default for nulls you are introducing potentially quite significant bias into your data sets.

So, without labouring the point, I recommend that you reconsider this last line, if only to remove it from an article that others might model their own data processing behaviour on.

Best Regards,
AKE

Reply to this Comment

Hi, I have a problem, the query below gives me zero value. please help.

ISNULL(table/NULLIF((table),0),0)*100

Reply to this Comment

Addendum:

In case when divisor could be NULL it's important to NVL it to ZERO. At least Oracle's implementation of NULLIF doesn't accept NULL value as valid parameter and returns error message about it.

So, in complete form it looks like:

nullif(nvl(divisor,0),0)

NVL will change NULL on 0 and NULLIF will change all zeroes to NULL without failures.

Reply to this Comment

good article...
colsec() method has better result than Isnull()..
so in place of Isnull() method use Colsec()method.
it is more standard and recommended by Microsoft Sql Server ....

Thanking you
--
Mohammad Shahnawaz
India

Reply to this Comment

@Mohammad,

I guess you meant COALESCE() which is mentioned in the article. Yes, it's more widely implemented while Oracle uses NVL instead of ISNULL but each function have own purpose. COALESCE is more complex function and capable to accept any number of parameters while isnull/nvl are tailored to replace NULL value from one single column with something different and do it as fast as possible. When you have to deal with billions of records in one query it could be important.

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.