Preventing Division by Zero Errors in SQL

1. Overview

In this quick tutorial, we’ll learn some techniques for avoiding the divide-by-zero error when writing SQL queries.

Though it’s very common, this error can fail our queries and heavily impact the stability of our applications, as we might get unwanted crashes or incorrect results.

Let’s explore some strategies to handle this error effectively.

2. Using NULLIF

First, our university database has a Course and an Exam table. Now, we want to calculate the average marks scored in each course. To keep things simple, we’re only saving the grades of the students who passed the test. To pass the test, students need to get at least a D. So, we want to avoid the divide-by-zero error for courses where there is no marking.

The easiest way to avoid the division by zero is to check whether the denominator is 0. In our example, the denominator is just the number of exams. To accomplish this, we can use the NULLIF function:

SELECT
    course.name AS course_name,
    SUM(CASE
        WHEN e.grade = 'A+' THEN 95
        WHEN e.grade = 'A' THEN 90
        WHEN e.grade = 'B+' THEN 85
        WHEN e.grade = 'B' THEN 80
        WHEN e.grade = 'C+' THEN 75
        WHEN e.grade = 'C' THEN 70
        WHEN e.grade = 'D+' THEN 65
        WHEN e.grade = 'D' THEN 60
        ELSE 0
    END) / NULLIF(COUNT(exam.id), 0) AS average_marks
FROM
    Course course
    LEFT JOIN Exam exam ON course.id = exam.course_id
GROUP BY
    course.name;

Above, we use the COUNT function to count the occurrences of grades. Then, we use NULLIFF, which returns a NULL value if both its arguments are equal. Consequently, if the count of the marks is 0, the result is NULL:

| course_name                    | average_marks|
|---------------------------------|-------|
| Advanced Reinforcement Learning | 0     |
| Signal and Systems              | 30    |
| Non-Planar Graphs               | null  |
| Discrete Maths-III              | 95    |
| Calculus: Intermediate          | 45    |

Even though the NULLIF method is very straightforward to implement and helps prevent an SQL error, our application logic must handle the NULL results. As the NULLIF method hides this detail, let’s check another option that is more explicit about what is returned.

3. Using CASE

We can use a CASE statement to perform conditional logic in SQL queries where we check if the number of markings is 0 and return NULL if it is. Alternatively, we return the average marks for a certain course:

SELECT
    course.name AS course_name,
    CASE
        WHEN COUNT(exam.id) = 0 THEN NULL
        ELSE SUM(CASE
            WHEN exam.grade = 'A+' THEN 95
            WHEN exam.grade = 'A' THEN 90
            WHEN exam.grade = 'B+' THEN 85
            WHEN exam.grade = 'B' THEN 80
            WHEN exam.grade = 'C+' THEN 75
            WHEN exam.grade = 'C' THEN 70
            WHEN exam.grade = 'D+' THEN 65
            WHEN exam.grade = 'D' THEN 60
            ELSE 0
        END) / COUNT(exam.id)
    END AS average_marks
FROM
    Course course
    LEFT JOIN Exam exam ON course.id = exam.course_id
GROUP BY
    course.name;

If the outcome of COUNT is 0, we explicitly return NULL. Alternatively, we could return a default value, for example, 0, instead of NULL:

| course_name                    | average_marks|
|---------------------------------|-------|
| Advanced Reinforcement Learning | 0     |
| Signal and Systems              | 30    |
| Non-Planar Graphs               | null  |
| Discrete Maths-III              | 95    |
| Calculus: Intermediate          | 45    |

Moreover, we can implement complex logic using the CASE statement and handle multiple outcomes based on our requirements. However, complex logic can become hard to maintain and impact the query’s performance.

4. Using COALESCE With NULLIF

Another way to handle the divide-by-zero error is to use the COALESCE function:

SELECT
    course.name AS course_name,
    COALESCE(
        SUM(CASE
            WHEN exam.grade = 'A+' THEN 95
            WHEN exam.grade = 'A' THEN 90
            WHEN exam.grade = 'B+' THEN 85
            WHEN exam.grade = 'B' THEN 80
            WHEN exam.grade = 'C+' THEN 75
            WHEN exam.grade = 'C' THEN 70
            WHEN exam.grade = 'D+' THEN 65
            WHEN exam.grade = 'D' THEN 60
            ELSE 0
        END) / NULLIF(COUNT(exam.id), 0),
        0
    ) AS average_marks
FROM
    Course course
    LEFT JOIN Exam exam ON course.id = exam.course_id
GROUP BY
    course.name;

COALESCE returns the first non-null value in a list of arguments. In our example, if the COUNT is 0, the NULLIF function returns NULL, and the COALESCE function returns 0 instead of NULL:

| course_name                    | average_marks|
|---------------------------------|-------|
| Advanced Reinforcement Learning | 0     |
| Signal and Systems              | 30    |
| Non-Planar Graphs               | 0     |
| Discrete Maths-III              | 95    |
| Calculus: Intermediate          | 45    |

In short, it enhances the NULLIF function by providing a default value for a NULL result.

Again, we must be careful when providing default values, as they might be misleading if not properly documented.

If we’re using SQL Server, there is also an option to suppress the error altogether. Let’s see how to do this next.

5. Configuring SQL Server With Options

We can configure SQL Server to handle the divide-by-zero error by setting the ARITHABORT and ANSI_WARNINGS to OFF:

SET ARITHABORT OFF;
SET ANSI_WARNINGS OFF;

These settings must be applied when starting the session and persist throughout the session. Even though configuring these options looks simple, as we don’t have to change our queries, it has some drawbacks and is generally considered a bad practice. Moreover, we’re also hiding other critical errors, which might make debugging some issues painful.

We must remember that turning off ARITHABORT and ANSI_WARNINGS negatively impacts query optimization and eventually leads to performance issues.

6. Conclusion

In this article, we learned various methods to deal with the divide-by-zero error in SQL queries.

First, we used the NULLIF function to return NULL when the denominator is 0. Then, we learned about the CASE statement to handle the error more explicitly. Next, we used the COALESCE function to provide a default value in case of a NULL result. Finally, we discussed setting the ARITHABORT and ANSI_WARNINGS options to suppress the error at the server level in SQL Server.