Revision
Comparisons with NULL
Functions with NULL
COALESCE to the rescue
NULLIF
20. New function NULLIF
Revision 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 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:

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 Hjem for deg get a special offer: the price of each product is decreased by 1.99! This means that some products may even come free!

Our customer has 1000.00 USD 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 products will have a decimal part, but don't worry about it.

Stuck? Here's a hint!

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

Console

Code editor

Result

TableConsole