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 :

Quantity Price

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.
                Begin Your SQL, R & Python Odyssey
                Elevate Your Data Skills and Potential Earnings
                Master 230 SQL, R & Python Coding Challenges: Elevate Your Data Skills to Professional Levels with Targeted Practice and Our Premium Course Offerings
    🔥 Get My Dream Job Offer 
                        Land a dream position at Amazon!

                        Hi Leon, I hope you're doing well. I wanted to reach out and say thanks for your interview
                        prep—it really helped me land a position at Amazon.  It's been a fulfilling experience!
                        SQLPad transforms your data career by boosting your productivity tenfold as a data
                            scientist, data engineer, or data analyst. Master essential skills like R, SQL and Python,
                            confidently ace
                            your job interviews.  Join SQLPad Free today!