SQL COALESCE: Filling Gaps with Defaults
Nulls are honest but ugly in a report. COALESCE swaps them for a friendly default.
Back in Missing values with NULL you learned that a null means "unknown" and that it shows up as a blank in your results. That is accurate, but a report full of blanks is hard to read.
COALESCE takes a list of values and returns the first one that isn't null. Hand it a column and a fallback, and every gap fills with something readable.
select user_id,
coalesce(nickname, full_name, 'Guest') as display_name
from usersUse defaults for display, not guesswork
COALESCE is best when the fallback is honest about what happened. Text like "No phone on file" tells the reader the value is missing; it does not pretend the customer has a real phone number.
Be careful using COALESCE in calculations. Replacing a missing number with 0 can be useful for a display column, but it can also change totals or averages if the null really means "unknown."
Schema · Garden ShopTable · customers9 columns · 20 rows
One row per customer. Some customers have no phone on file.
List every customer with their name and aphone_display column. When a customer has no phone on file, show'No phone on file' instead of a blank. Sort bycustomer_id.
- Columns: customer_id, first_name, last_name, phone_display.
- Rows: 20 customers.
- Five customers have no phone, so they show 'No phone on file' instead of null.