Division by Zero

Handling division by zero is a common need in SQL queries. Here is the standard way of performing a check if divisor is zero using the CASE function.

-- Sample table
CREATE TABLE Foo (
 keycol INT PRIMARY KEY,
 x INT,
 y INT);
 
INSERT INTO Foo 
VALUES(1, 15, 3), (2, 10, 0);
 
-- Using CASE to check divisor for zero
SELECT keycol,
       CASE WHEN y = 0
            THEN 0
            ELSE x / y
       END AS result
FROM Foo;

While this is a very natural way to handle this problem, here is another approach which is more compact and as effective. The method below uses the NULLIF function to set the divisor to NULL if it is zero, then since the result from the division will be NULL the COALESCE function is used to return 0.

-- Using NULLIF and COALESCE
SELECT keycol,
       COALESCE(x / NULLIF(y, 0), 0) AS result
FROM Foo;

Labels: