How to Avoid the “Divide by Zero” Error in SQLite?

In SQLite, performing division operations where the divisor is zero can lead to the infamous “divide by zero” error. This error occurs when attempting to divide a number by zero, which is mathematically undefined. Fortunately,

SQLite provides several methods to handle and prevent this error. In this article, we will explore these methods and learn how to avoid the “divide by zero” error in SQLite queries.

How to Avoid the “Divide by Zero” Error in SQLite?

Let’s understand why the “divide by zero” error occurs. In mathematics, division by zero is undefined because it leads to infinite or undefined results. Similarly, SQLite throws an error when attempting to divide by zero to prevent unexpected or incorrect query results. Below are the methods that help us to Avoid the “Divide by Zero” Error in SQLite are as follows:

1. Using NULLIF() Operator
2. Using CASE Statement

Setting up the environment

To understand How to avoid the “divide by zero” error in SQLite we need a table on which we will perform various operations and queries. Here we will consider a table called geeksforgeeks which contains user_id, name, total_score, and contest_questions as Columns.

Table – geeksforgeeks

1. Using NULLIF() Operator

In SQLite, the NULLIF() operator compares to operators passed in its parameter. It returns NULL if they are equal. We are going to use this with our divisor column. We are going to match divisor column values with zero. If they are equal, we are going to return NULL. Eventually, this will prevent us from facing divide by zero error.

Syntax:

NULLIF (val_01, val_02)

NOTE: If val_01 is equal to val_02, then our NULLIF() operator will result a NULL value.

Example of NULLIF() Operator to Prevent “divide by zero” Error

In this example, we are going to use NULLIF() operator and prevent “divide by error”. We will use NULLIF() operator with our divisor part. We will compare our divisor column with zero, if they are equal then NULLIF() operator will return a NULL value.

Query :

SELECT user_id, name, total_score / NULLIF(contest_questions, 0) AS result
FROM geeksforgeeks;

Output :

Using NULLIF() operator

Explanation: In this image, we can clearly notice that some of the fields in result column has NULL value in it. As we can see our main table “geeksforgeeks“, id’s 102, 104 and 105 has zero in ‘contest_question’ column. As contest_question column is our divisor in this case, NULLIF operator will result NULL wherever it encounters any zero value in the divisor column.

2. Using CASE Statement

A CASE statement in SQLite is used to perform condition based operations. It gives us flexibility to perform some expression only when some condition is fulfilled. We will use this to perform division only when divisor column is not zero.

Syntax:

CASE (value)

WHEN condition01 THEN result01

WHEN condition02 THEN result02

WHEN condition03 THEN result03

................................

Else (default result)

END CASE;

Example of CASE Statement to Prevent “divide by zero” Error

In this example, we are going to demonstrate that how we can prevent “divide by zero” error with the help of CASE statement. We will set the case that if the divisor column has a zero values than, we will return NULL. Otherwise we will perform the divide operation of two columns.

Query:

SELECT user_id, name,
CASE WHEN total_score = 0 THEN NULL 
ELSE total_score / contest_questions END AS result 
FROM geeksforgeeks;

Output:

Using CASE statement

Explanation: In the above image, we can clearly notice that some of the fields in the result has NULL values. This is because we have set the condition in the CASE statement that if the divisor column has zero as value then set the value of result column as NULL. We can refer to the ‘geeksforgeeks’ table for reference. As in previous example, we can clearly notice that id’s 102, 104 and 105 have zero in their ‘contest_questions’ column.

Conclusion

Overall, ‘dividing by zero’ is a very common error and it can be prevented very easily with some in-built functions or with the help of CASE statement. In this article, we have covered briefly on how to prevent this error with the help of “NULLIF() operator” and CASE statement. We have covered their basic concepts along with their syntax followed by an example on how we use them to prevent our “dividing by zero” error. We have explained all the mentioned methods with clear explanations along with a brief example.