Back to cookbooks list Articles Cookbook

How to Avoid Dividing by Zero in MySQL

  • NULLIF()
  • CASE

Problem:

You’d like to avoid the division-by-zero error.

Example:

Our database has a table named numbers with data in the columns id, number_a, and number_b.

idnumber_anumber_b
140
257-5
3-756
4-670
52355
6-8-4

Let’s divide number_a by number_b and show the table with a new column, divided, with the result of the division.

Solution 1:

SELECT
  *,
  number_a / NULLIF(number_b, 0) AS divided
FROM numbers;

Solution 2:

SELECT
  *,
  CASE
    WHEN number_b = 0 THEN NULL
    ELSE number_a / number_b
  END AS divided
FROM numbers;

The result is:

idnumber_anumber_bdivided
140NULL
257-5-11.4000
3-756-0.1250
4-670NULL
523550.4182
6-8-42.0000

Solution 3:

SELECT
  *,
  number_a / number_b AS divided
FROM numbers
WHERE number_b != 0;

The result is:

idnumber_anumber_bdivided
257-5-11.4000
3-756-0.1250
523550.4182
6-8-42.0000

Discussion:

Discover the best interactive MySQL courses

The first solution uses the NULLIF() function, which takes two numbers as arguments. When the first argument is equal to the other argument, the function returns NULL as a result. If number_b is equal to zero, the divisor is NULL, and the result of the division is NULL.

The second solution uses the CASE statement. If the condition after the WHEN keyword is true (in our case, the condition is number_b = 0), we specify that NULL be returned. Otherwise, the division happens as usual.

The third solution simply uses the WHERE condition to filter out the rows where number_b is zero. The rows with number_b equal to zero are missing from the result set.

Recommended courses:

Recommended articles:

See also: