Methods to avoid the SQL divide by zero error

In SQL, performing division operation sometimes leads to errors when divided by zero . This error is " divide by zero " which can disrupt your query execution . To avoid this situation, we can use the following methods:

• Use NULL IF function
• Use CASE statement
• Use SET ARITHABORT OFF
• Use WHERE

Now we will discuss these methods one by one with the help of examples.

Use NULLIF function

In this method , if both the arguments are equal then it will return NULL value while if both the arguments are not equal, it will return first value.

Example 1

Select NULLIF(2,2)

Output

NULL 

Example 2

Select NULLIF(2,0)

Output

2

Using CASE statement

The CASE statement is used to check the condition and return a value. It checks the conditions until it is true and if no conditions are true , it returns the value in the else part.

Example

Declare @num1 INT;
Declare @num2 INT;
Set 
  @num1 = 2;
Set 
  @num2 = 0;
Select 
  CASE when @num2 = 0 then NULL else @num1 / @num2 end as division;

Output

NULL 

Using SET ARITHABORT OFF

By default, ARITHABORT is set as ON. It terminates the query and returns an error message . If we set it OFF,it will terminate and returns a NULL value.

Example

SET 
  ARITHABORT OFF;
SET 
  ANSI_WARNING OFF;
declare @num1 INT;
declare @num2 INT;
set 
  @num1 = 2;
set 
  @num2 = 0;
select 
  @num1 / @num2;

Output

NULL

Using WHERE

We can avoid division by zero by using WHERE with the comparison operator <>.

Example

Select Name, Salary from Employee where (salary)<>0;

Conclusion

Handling the " divide by zero " error in SQL is important to ensure smooth query execution. Each of the above methods have a different approach allowing you to choose the one that best fits your needs.