Only this week, get the SQL Complete Track of 9 courses in a special prize of $330 $89!
Comparisons with NULL
Functions with NULL
11. NULL with other functions
COALESCE to the rescue
Revision and practice


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.


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:


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.