Level 8 · Price tags when a cost is missing

The starter almost works. Run it and scroll to product 101, the Vale Velvet Loveseat: its price tag is fine, but its cost note is NULL. Nine products have no cost on file, and each of them loses the whole note.

One NULL blanks the string

|| follows the same rule as arithmetic: anything combined with NULL is NULL. “cost $ plus an unknown number” is unknown, so the database returns NULL rather than guessing. COALESCE takes a list of values and returns the first one that is not NULL, which makes it the standard way to supply a fallback:

SELECT
  product_id,
  COALESCE('cost $' || unit_cost, 'cost unknown') AS cost_note
FROM products;

Product 102 gets ‘cost $503.50’ and product 101 gets ‘cost unknown’. Wrapping the whole concatenation in COALESCE uses the NULL rule on purpose: the label is either complete or replaced, never half-built.

unit_price and unit_cost are decimals, and || turns them into text with both decimal places, so the coffee table’s tag reads ‘Alder Oak Coffee Table - $919.00’.

CONCAT behaves differently

CONCAT(a, b, c) treats a NULL argument as empty text. CONCAT('cost $', unit_cost) therefore gives ‘cost $’ with nothing after it, which looks like a formatting glitch rather than missing data. That is handy for optional parts, such as a middle name, and wrong when the reader needs to know a value is missing.

Common mistakes

• COALESCE(unit_cost, 0). It runs, but it states a cost of zero, and anyone computing margin from your sheet gets 100%. Unknown should stay visibly unknown.
• Forgetting the spaces inside the quotes. ' - $' has a space on each side of the dash; without them the tag reads ‘Alder Oak Coffee Table-$919.00’.
• COALESCE(unit_cost, 'unknown') on its own. The two values have different types (a decimal and a text), so the database complains. Convert first, or wrap the concatenation as above.

In SQL Server

+ joins text there, a NULL also blanks the result, and ISNULL(a, b) is the two-argument cousin of COALESCE. A decimal must be converted before it is joined to text:

SELECT COALESCE('cost $' + CAST(StandardCost AS varchar(20)), 'cost unknown') AS CostNote
FROM Production.Product;