How to handle divide by zero in SQL

Problem

You need to perform a division operation within your SQL query, but there is a possibility that the denominator could be zero, leading to a "divide by zero" error. How can you prevent this error from occurring?

Sample Data

Let's consider a sample table Orders:

You want to calculate the price per item (Price / Quantity) for each order.

MySQL Solution

SELECT ID, Quantity, Price,
       IF(Quantity = 0, NULL, Price / Quantity) AS PricePerItem
FROM Orders;

Explanation (MySQL)

In MySQL, the IF() function is used to handle the divide by zero error. The IF() function takes three parameters: the condition, the value to return if the condition is true, and the value to return if the condition is false. In this case, the condition is Quantity = 0. If this is true, the function returns NULL. If it is false, it performs the division operation Price / Quantity.

PostgreSQL Solution

SELECT ID, Quantity, Price,
       CASE WHEN Quantity = 0 THEN NULL ELSE Price / Quantity END AS PricePerItem
FROM Orders;

Explanation (PostgreSQL)

PostgreSQL doesn't have an IF() function like MySQL. Instead, it uses the CASE statement. The CASE statement checks if the Quantity is zero. If it is, it returns NULL. If it isn't, it performs the division operation Price / Quantity.