SQL nullif

The NULLIF function takes two arguments and returns NULL if both are equal, else returns the first argument.

---

Basic Syntax

SELECT NULLIF(10,10) -- returns null
SELECT NULLIF(2, 3) -- returns 2

---

Example

Suppose we have the following table:

NAME	DEPARTMENT
Lauren	A
Vinton	A
Claire	B
If we want to calculate the ratio of people in Department A to Department B. The simplest way to do it could be with the following query:

SELECT (
SUM(CASE WHEN department = 'A' THEN 1 ELSE 0 END)
/
SUM(CASE WHEN department = 'B' THEN 1 ELSE 0 END)
) AS department_ratio
FROM depts

The query, which divides the total count of people in A by the total count of people in B should return 2.

Consider the case where there is nobody in department B. This would create an error because the query would result in a division by zero. To catch the error we can wrap the denominator in a NULLIF call, such that if the denominator is 0, we get null - this means that any query would be doing mathematical operations with null, which itself simply returns null:

SELECT (
SUM(CASE WHEN department = 'A' THEN 1 ELSE 0 END)
/
NULLIF(SUM(CASE WHEN department = 'B' THEN 1 ELSE 0 END), 0)
) AS department_ratio
FROM depts