Lesson 6.3

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.

Pattern

select user_id,
       coalesce(nickname, full_name, 'Guest') as display_name
from users

Use 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 doesn't 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."

Syntax reference

Need syntax details? See the SQL NULL handling reference

Schema · Garden Shop Table · customers 9 columns · 20 rows

Table · customers

One row per customer. Some customers have no phone on file.

9 columns · 20 rows

customer_id int first_name text last_name text email text phone text city text state text signup_date date is_active bool

Your task

List every customer with their name and a phone_display column. When a customer has no phone on file, show 'No phone on file' instead of a blank. Sort by customer_id.