The COALESCE() function
The GREATEST() & LEAST() functions
The NULLIF() function
Instruction
Nice! You can also use COALESCE() with other functions and operations, such as concatenation, multiplication, etc. Take a look:
SELECT
COALESCE(category, 'unknown category')
|| ': '
|| COALESCE(type, 'unknown type')
AS category_type
FROM product;
In the above query fragment, a NULL category or type value is replaced with an appropriate message, but we can still see the type when the category is NULL (and vice versa).
Exercise
Show the following sentence:
Product X is made in Y.
X is the product name and Y is the production area. If the product name is not provided, display 'unknown name' instead. If the production area is not provided, substitute 'unknown area'. Name the column origin.
Stuck? Here's a hint!
Use the operator || to create the sentence. Remember the period/full stop.
