Solving tips
- Test for missing values with IS NULL, never with = NULL, which is never true.
- Use COALESCE to substitute a fallback value when a column is NULL.
- COALESCE returns its first non-NULL argument, so put the preferred value first.
You are given a customers table where the phone number is optional. Produce a contact list that never shows a blank phone field.
Schema
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT,
phone TEXT
);
Sample data:
| id | name | phone |
|---|
| 1 | Alice | 555-0101 |
| 2 | Bob | NULL |
| 3 | Carol | 555-0199 |
| 4 | Dave | NULL |
| 5 | Eve | NULL |
| 6 | Frank | 555-0155 |
Task
Return the id, name, and a contact column for every customer, ordered by id ascending. The contact column should show the customer’s phone when it is present, and the literal text No phone on file when phone is NULL.
Expected output
| id | name | contact |
|---|
| 1 | Alice | 555-0101 |
| 2 | Bob | No phone on file |
| 3 | Carol | 555-0199 |
| 4 | Dave | No phone on file |
| 5 | Eve | No phone on file |
| 6 | Frank | 555-0155 |
Approach
Substituting a default for a missing value is the job of COALESCE. Wrap the phone column in COALESCE(phone, 'No phone on file'): it returns phone when that is non-NULL and otherwise falls through to the literal fallback. Alias the result as contact.
Query
SELECT
id,
name,
COALESCE(phone, 'No phone on file') AS contact
FROM customers
ORDER BY id ASC;
Walkthrough
COALESCE scans its arguments left to right and returns the first that is not NULL. Alice, Carol, and Frank have real phone values, so contact echoes those numbers. Bob, Dave, and Eve have NULL phone, so the first argument is skipped and the fallback No phone on file is returned. Ordering by id produces the six rows in sequence.
Complexity & notes
COALESCE is a per-row expression with no meaningful cost. The classic trap is trying to detect NULLs with phone = NULL: comparisons against NULL yield UNKNOWN, so that predicate matches nothing; use phone IS NULL instead. COALESCE is standard SQL and portable; the two-argument form is equivalent to Oracle’s NVL and MySQL’s IFNULL, but COALESCE also accepts more than two arguments and works everywhere.