Okay! Another nice function, albeit less frequently applied, is
NULL if both
y are equal. Otherwise, it returns the first argument. For example,
NULLIF(5,4) will return 5 while
NULLIF(5,5) will return
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 10000 USD 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:
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 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.