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) returns 5 while NULLIF(5, 5) returns NULL.
You might wonder how you can use this function in your queries. The answer is: it helps you avoid division 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 AllPeople. 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 RichPeople. Now, if we want to decide how much a single 'not-rich' person should get, we could write:
10000 / (AllPeople - RichPeople)
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 zero, so the database will return an error.
Luckily, we can use NULLIF():
10000 / NULLIF((AllPeople - RichPeople), 0)
NULLIF() checks if the subtraction equals 0. If it does equal 0, it returns a NULL, otherwise it just gives the result of the subtraction. Dividing by zero 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, Hjem for deg has a special offer: the Price of each product is decreased by 1.99 USD. This means that some products may even come free! In the Product table all prices that are not NULL are not less than 1.99.
Our customer has 1000.00 USD and he would like to know how many products of each kind he could buy.
Show the Name of each product and the Quantity counted in the following way:
Divide the sum of 1000.00 by the new price of each product.In case of division by zero return a
NULL. Most products will have a decimal part, but don't worry about it.
The new price is calculated as follows: Price - 1.99. Use it as the first argument of NULLIF().
