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

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.

Priyanka Gupta

Updated on: 2025-03-17T16:55:19+05:30

120 Views

Kickstart Your Career

Get certified by completing the course

Get Started