Autumn Offers - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
The COALESCE() function
4. The COALESCE() function
The GREATEST() & LEAST() functions
The NULLIF() function
Summary

Instruction

Next, let's look at how the COALESCE() function can help with NULLs. This is a standard SQL function also used in PostgreSQL.

The COALESCE(x, y) function returns the first value that is not NULL. Check out the example:

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

In this query, a product with an associated category will have its name and category shown. If there is a NULL in a product's category field, the query will show "none" instead of a category name.

If you're wondering what the word "coalesce" means, it basically 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).

Exercise

Select the names and categories for all products. If any of these columns has a NULL, show 'n/a' instead. Rename the new columns to name and category.