How to Avoid Dividing by Zero in SQL

Division by zero is a common problem when working with data in SQL. It can lead to unexpected results and can even cause your query to fail.

The best way to avoid dividing by zero in SQL is to use the NULLIF function. This function takes two arguments, and returns NULL if the two arguments are equal. For example, if you wanted to divide two columns, a and b, you would use the following query:

SELECT a / NULLIF(b, 0) FROM table_name;

This query will return the result of a / b, unless b is equal to zero. In that case, it will return NULL instead.

Examples

Let's look at a few examples of how this function can be used. Suppose you have a table called sales with the following data:

If you wanted to calculate the total revenue for each product, you would use the following query:

SELECT product, quantity * NULLIF(price, 0) AS revenue FROM sales;

This query would return the following results:

‍
As you can see, the query correctly returns NULL for the Gizmo product, since its price is equal to zero.

Additional Info

The NULLIF function is supported by most major databases, including MySQL, PostgreSQL, and SQL Server. However, the syntax may vary slightly depending on the database you are using. For more information, check out the documentation for your particular database. 🤓