In MySQL, NULLIF() operator is used to return a NULL value when our specified expressions are equals. We are going to use it to return NULL values when we encounter zero values in our divisor column.

Syntax:

SELECT column_01 / NULLIF(column_02, 0) 
FROM table_name;

NOTE: If column_02 is 0 for any row then the NULLIF operator will return NULL value.

In this example, we are going to use the NULLIF operator to tackle our diving by zero error. We will return NULL for all those rows which have zero in their monthly score column.

Query:

SELECT name, total_score / NULLIF(montly_score, 0) AS average_score
FROM w3wiki;

Output:

Explanation: Likewise in the previous example, we can see that there are NULL values for Aayush and Vivek. This is because we have used the NULLIF() operator to return NULL if there is zero in the monthly score column. As we have seen those rows have zero values in their monthly score column.

MySQL is an open-source relational database management system in short RDBMS. We use it to store, retrieve, and manipulate data very efficiently. In MySQL “Divide by zero error” is encountered when we try to divide one column with another column that contains some zero values.

“Diving by zero error” is a common problem while performing division in most of the data. It can eventually solve some basic step-by-step approaches.

In this article, we are going to demonstrate various methods through which we can tackle “Divide by zero error” very easily. We are going to see various approaches with clear and concise examples along with their explanations.

How to Avoid Dividing by Zero in SQL

MySQL allows us several approaches through which we can ignore dividing by zero error. There are certain built-in functions and some queries along with the where clause, can help us to achieve our goal. Below are some mentioned methods through which we can avoid dividing by zero error.

1. Using the CASE statement

2. Using NULLIF()

3. Using WHERE Clause