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: