Hack 28. Avoid Dividing by Zero
NULLIF is a little-known function that returns NULL if its arguments are equal. It is handy for sidestepping a divide-by-zero exception.
The expression NULLIF(x,y) will give you x when x and y are different. If x and y are the same you get NULL.
Suppose you need to get a ratio from a pair of numbers but the divisor may be zero, as shown in Table 5-6.
| page | impressions | clicks |
|---|---|---|
| index.htm | 1,000 | 10 |
| page1.htm | 0 | 0 |
| page2.htm | 500 | 10 |
To calculate the click-through ratio (CTR) for each page you divide the number of clicks by the number of page impressions and multiply by 100. Because page1.htm was offline, it received zero page impressions, so the CTR calculation will give a divide-by-zero error. This example is from SQL Server (other databases give a similar error):
1> SELECT page, 100.0*clicks/impressions FROM stats; 2> GO page -------------------- ---------------------------- index.htm 1.000000000000 Msg 8134, Level 16, State 1, Server TINYVAIO, Line 1 Divide by zero error encountered.
|
You can get around this problem with NULLIF to produce a NULL value when the number of impressions is zero:
1> SELECT page, 100.0*clicks/NULLIF(impressions,0) FROM stats; 2> GO page -------------------- ---------------------------- index.htm 1.000000000000 page1.htm NULL page2.htm 2.000000000000
In SQL, any number divided by NULL gives NULL and no error is generated.