Publishers of technology books, eBooks, and videos for creative people

Home > Blogs > Avoiding division by zero with NULLIF, Five SQL Tips in Five Days, Part 5

Avoiding division by zero with NULLIF, Five SQL Tips in Five Days, Part 5

By  Jul 25, 2008

Topics: Productivity

Suppose you want to calculate the male–female ratios for various school clubs, but you discover that the following query fails and issues a divide-by-zero error when it tries to calculate ratio for the Lord of the Rings Club, which has no women:
SELECT club_id, males, females, males/females AS ratio
  FROM school_clubs;

You can use the function NULLIF to avoid division by zero. NULLIF compares two expressions and returns null if they are equal or the first expression otherwise.

Rewrite the query as:

SELECT club_id, males, females, males/NULLIF(females, 0) AS ratio
  FROM school_clubs;

Any number divided by NULL gives NULL, and no error is generated.

Be sure to look back over my posts this week for more SQL tips.

And for more on SQL, be sure to check out my book SQL: Visual QuickStart Guide, 3rd Edition.