How to Avoid Dividing by Zero in MySQL

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 WHEN number_b = 0 THEN NULL ELSE number_a / number_b END AS divided FROM numbers;

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.