# How to Avoid Dividing by Zero in MySQL

Database:

Operators:

Table of Contents

## Problem:

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

## Example:

Our database has a table named

with data in the columns **numbers**`id`

, `number_a`

, and `number_b`

.

id | number_a | number_b |
---|---|---|

1 | 4 | 0 |

2 | 57 | -5 |

3 | -7 | 56 |

4 | -67 | 0 |

5 | 23 | 55 |

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:

id | number_a | number_b | divided |
---|---|---|---|

1 | 4 | 0 | NULL |

2 | 57 | -5 | -11.4000 |

3 | -7 | 56 | -0.1250 |

4 | -67 | 0 | NULL |

5 | 23 | 55 | 0.4182 |

6 | -8 | -4 | 2.0000 |

## Solution 3:

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

The result is:

id | number_a | number_b | divided |
---|---|---|---|

2 | 57 | -5 | -11.4000 |

3 | -7 | 56 | -0.1250 |

5 | 23 | 55 | 0.4182 |

6 | -8 | -4 | 2.0000 |

## 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.