Revision
Comparisons with NULL
Functions with NULL
11. NULL with other functions
COALESCE to the rescue
NULLIF
Revision and practice

Instruction

Okay, let's move on. You should also watch out for NULLs when you work with calculations. Most of them will return a NULL if any of the arguments is a NULL. This may not be obvious.

For instance, you could think that the following expression:

firstname || ' ' || lastname

with firstname = 'Anne' and a NULL lastname will return 'Anne ' as a result. That's not true. You will get a NULL if any of the columns is NULL.

Exercise

Check it out for yourself. For all products, show their category, type and the following text: category and type separated with a colon (:), for example:

kitchen:fork

Note what happens with the row which doesn't have a category.

Stuck? Here's a hint!

Use the concatenation symbol || to join the two columns and to separate them by a colon.

Console

Code editor

Result

TableConsole