Using ISNULL or NULLIF to prevent division by zero

How do I use the ISNULL or NULLIF to avoid division by zero?

ISNULL and NULLIF are different but related functions. ISNULL tests a value to see if it's null, and returns TRUE or FALSE accordingly. NULLIF compares two values and returns NULL if they're equal, otherwise it returns the first value. NULLIF(param1,param2) is equivalent to CASE WHEN param1 = param2 THEN NULL ELSE param1 END

Important Note: NULLIF is standard SQL-92; ISNULL isn't, but you can use the phrase IS NOT NULL in a WHERE clause instead.

Different database systems offer a range of non-standard functions that are often useful. In MySQL, you can use ISNULL, NULLIF, and even IFNULL. In Microsoft SQL/Server you can use ISNULL and NULLIF, but in Oracle you have to use NVL and DECODE respectively.

In any case, all you need for this problem is ISNULL. (NULLIF will have to wait for another question.)

When you do division in SQL, there are two things to watch out for -- nulls and zero. You should anticipate both situations in your query. If the column was defined as NOT NULL, you don't have to worry about nulls, but if the column is "nullable" (i.e. defined as NULL, or not NOT NULL), you must be careful about dividing by a null value because the answer will be null. Sometimes that's okay, but often you'd like to exclude those rows from the query. Also, you sure wouldn't want to divide by zero, since that will cause either a data exception or a null value. So the best strategy is to check both conditions --

select Hits / AtBats as HittingPct
  from yourTable
 where AtBats IS NOT NULL
   and AtBats > 0

If the column was defined NOT NULL, you can drop the first of the above WHERE clause conditions.

("But what if somebody hasn't had any at bats?" I can hear you asking. Well, they won't be in that result set. If they need to be, then you have to ask yourself what their hitting percentage should be. It can't be .000, can it? Would that be fair? One might say it was NULL, unknown, not applicable, indeterminate, or yet-to-be-decided. You can achieve this result, getting nulls wherever the divisor is zero, by removing the WHERE clause, but only if your database returns a null for an attempted division by zero (the proper behaviour, in my estimation), instead of hiccoughing an error message that informs you that you just tried to divide by zero. Then you wouldn't need to filter out the people who hadn't had any at bats yet. After all, filtering out rows based on zero conditions can get tiresome, especially since you have to do it in every query where you have a division calculation, such as hitting percentage, which you obviously want to calculate on the fly. You could build a view which has the filter condition built in -- this eliminates the problem of dividing by zero in SQL, but creates the situation where some people are missing from certain queries.)

For More Information

• The Best SQL Web Links: tips, tutorials, scripts, and more.
• Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL gurus are waiting to answer your toughest questions.