The Null Trap: Handling Defaults with COALESCE

The Broken Total

The "Black Hole" of NULL (from yesterday) finally bit me. I was calculating the "Total Compensation" for the staff. It was `Salary + Bonus`.

For the sales team, it worked. For the developers (who don't get bonuses), the result was `NULL`. My final report made it look like the developers earned $0.

The Quest: The Safety Net

When a value is missing, you often want a "Default." If there is no bonus, the bonus should be `0`. If there is no phone number, it should say `'N/A'`.

We use the `COALESCE` function to provide this fallback. It takes a list of values and returns the **first one that isn't NULL**.

The Implementation: Filling the Gaps

I fixed my salary report using `COALESCE` to turn the empty bonuses into zeros.

-- Providing a safety net for missing data

SELECT

name,

salary + COALESCE(bonus, 0) AS total_comp

FROM

employees;

Another common example:

-- Handle missing contact info

SELECT name, COALESCE(phone, 'No Phone on File') as contact

FROM customers;

The "Oops" Moment

I once tried to `COALESCE` a Text column with a Number: `COALESCE(category, 0)`. SQL threw an error.

**Pro Tip**: Your fallback value MUST be the same data type as the original column. If the column is text, the fallback must be text.

The Victory

The payroll clerk got an accurate report where the developers' salaries were correctly displayed. I had successfully "Healed" the broken data. Dealing with NULLs is what separates a novice from an analyst who can be trusted with the "Official" numbers.

Your Task for Today

Find a column with NULLs. Use `COALESCE` to provide a friendly default message (like 'Unknown') in your report.

*Day 15: The Final Exam—Phase 1 Project.*