How to Avoid Dividing by Zero in MySQL

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.

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:

Solution 3

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

The result is:

Discussion

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.