Revision
Comparisons with NULL
Functions with NULL
COALESCE to the rescue
14. New keyword: COALESCE
NULLIF
Revision and practice

Instruction

Perfect answer! Well, as you can see, NULLs can be troublesome. This is why SQL features a function to help you tackle them: COALESCE(x,y).

COALESCE(x,y) returns the first of the two values (x or y) which is not a NULL. Check out the example:

SELECT name, COALESCE(category, 'none')
FROM product;

If a product has a category in the above query, it will be shown. If it has a NULL category, the query will show 'none' instead.

If you're a non-native English speaker, then you may wonder what the word coalesce means. It is a verb which can be explained as 'to come together to form one group or mass'. You can use it literally (ingredients can coalesce) or figuratively (ideas can coalesce, too). Now you understand the choice of word in SQL.

Exercise

Select the names and categories for all products. If any of the columns is a NULL, write 'n/a' instead.

Stuck? Here's a hint!

Nothing horrible here. Study the explanation in detail and write your version of COALESCE.

Console

Code editor

Result

TableConsole