Introduction
The ISNULL() function
The COALESCE() function
7. New function: COALESCE()
The NULLIF() function
Summary

Instruction

As you can see, NULLs can be troublesome, and ISNULL() can help. However, it is not the only option available; there's also the COALESCE() function! It is similar to ISNULL(), but it's a standard SQL function. (ISNULL() is a T-SQL function.)

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

SELECT
Name,
COALESCE(Category, 'none') AS Category
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.

Non-native English speakers may wonder what the word 'coalesce' means. Basically, it means "to come together, to form one group or mass". You can use coalesce literally (ingredients can coalesce into bread) or figuratively (ideas can coalesce, too).

Exercise

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

Stuck? Here's a hint!

You'll need to use the COALESCE() function twice.