Comparisons with NULL
Functions with NULL
COALESCE to the rescue
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
Name the last column category_type. 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.
