How to use NULLIF() Operator In SQL
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 w3wiki;
Output :
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 “w3wiki“, 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.
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.
Contact Us