Okay! Another nice function, albeit less frequently applied, is NULLIF
. NULLIF(x, y)
returns NULL
if both x
and y
are equal. Otherwise, it returns the first argument. For example, NULLIF(5, 4)
will return 5 while NULLIF(5, 5)
will return NULL
.
You might wonder how you can use this function in your queries. The answer is: it helps you avoid dividing by zero. Take a look at this theoretical example:
Let's say we have $10,000 and we want to give equal portions of this amount to some people. We know that the number of these people is stored in the column all_people
. But we also know that there are some people who are rich already, so they don't need the money from us. Their number is stored in the column rich_people
. Now, if we want to decide how much a single "not-rich" person should get, we could write:
10000 / (all_people - rich_people)
And the query works fine… unless all the people are rich. We will then get a 0 in the denominator. As you know, we can't divide by 0, so the database will return an error.
Luckily, we can use NULLIF
:
10000 / NULLIF((all_people - rich_people), 0)
NULLIF
checks if the subtraction equals 0. If it doesn't, it just gives the result of the subtraction. If it does, it returns a NULL
. Dividing by 0 is illegal, but dividing by NULL
will just give NULL
– in this way, we can at least prevent our database from producing an error.