It's Autumn! Prices fall like leaves! - 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.