Kickstart 2020 with new opportunities! - hours only!Up to 80% off on all courses and bundles.-Close
Review
Comparisons with NULL
Functions with NULL
COALESCE to the rescue
NULLIF
20. New function NULLIF
Review and practice

## Instruction

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. ## Exercise 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.

### Stuck? Here's a hint!

The new price is calculated as follows: price-1.99. Use it as the first argument of NULLIF.