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.
Today, the customers at Highland Furniture get a special offer: the price of each product is decreased by 1.99! This means that some products may even be free!
Our customer has $1,000.00 and would like to know how many products of each kind they could buy.
Show the name of each product and a second column named quantity: Divide the sum of 1000.00 by the new price of each product. Watch out for division by 0 and return a NULL instead.
Most quantities will have a decimal part. That's okay for this exercise.
The new price is calculated as follows: price-1.99. Use it as the first argument of NULLIF.
