SQL COALESCE: Choose a Fallback Without Hiding Missing Data

The practice task

A fictional workshop team records an optional preferred contact and a backup contact. Produce a usable contact label, treating an empty preferred value as missing. Keep the underlying stored values unchanged.

The data below is fictional. Run the example in a new SQLite database. It was checked with SQLite 3.51.0; other databases may use different syntax, functions or type rules.

Complete worked query

Create the small fixture and run the query together. The IDs, boundary values and missing entries make the result small enough to check by hand.

CREATE TABLE contacts(name TEXT, preferred TEXT, backup TEXT);
INSERT INTO contacts VALUES ('Ari',NULL,'ari@example.invalid'),('Bo','','bo@example.invalid'),('Cy','cy@example.invalid',NULL),('Dee',NULL,NULL);

SELECT name, COALESCE(NULLIF(preferred, ''), backup, 'Ask participant') AS contact
FROM contacts ORDER BY name;

Checked result

This is the result of the exact example above. NULL marks a missing database value rather than the text string "NULL".

Read the result

Ari uses the backup because the preferred contact is NULL. Bo needs NULLIF before COALESCE: without it, the empty string would win and the report would contain a blank. Cy keeps the preferred contact, while Dee receives an explicit follow-up label.

This is a reporting decision, not a data-cleaning update. The query does not write the fallback into the table. That distinction matters when an analyst must distinguish a supplied contact from an instruction to ask for one.

A fallback of zero can conceal missing measurements. For example, an unknown course attendance count is not necessarily zero attendance. Decide whether the report should show an unknown label, exclude an incomplete row, or use a numeric fallback before changing the expression.

Mistakes to check

Use these checks when adapting the example to a different dataset.

• COALESCE does not remove whitespace-only strings.
• Mixing numbers and text can produce surprising types across database products.
• Keep missing and measured-zero values distinguishable when that distinction affects a decision.