Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Review
Comparisons with NULL
Functions with NULL
COALESCE to the rescue
14. New keyword: COALESCE
NULLIF
Review 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 understood 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 the word in SQL.

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!

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