How to Stop NULLs From Breaking Your SQL Reports

Hello ,

You’ve built a clean customer report. Locations, booking history, revenue by region. You send it to your director.

Ten minutes later: “Why does this report show blank cells in the location column? And why do the totals not add up?”

Welcome to the NULL problem. And it’s more common than you’d think.

In the Summit Adventures database (the fake adventure tourism company I created to help people learn business analytics), 78.2% of customers don’t have dietary restriction data. 29.1% are missing state information. These aren’t errors — they’re just fields that weren’t required when customers signed up.

But if you don’t handle them, your reports will have blank cells, your aggregations might return unexpected results, and your executives will question the quality of your work.

The fix is a function called COALESCE.

What COALESCE Does

COALESCE takes a list of values and returns the first one that isn’t NULL.

COALESCE(value1, value2, value3, ...)

Think of it as a chain of fallbacks:
Try the first value. Got something? Use it.
NULL? Try the second value.
Still NULL? Try the third.
All NULL? Return the last value (which should be your default).

The Business Problem: A Clean Location Report

Your marketing team wants a customer report broken down by location. But 29.1% of customers are international — they have a country but no state. If you just use the state column, nearly a third of your customers show up as blank.

Here’s how COALESCE fixes this:

-- Clean location report using COALESCE
SELECT 
    COALESCE(c.state, c.country, 'Unknown') AS location,
    COUNT(*) AS customer_count,
    COUNT(b.booking_id) AS total_bookings,
    ROUND(COALESCE(SUM(p.amount), 0)::numeric, 2) AS total_revenue
FROM customers c
    LEFT JOIN bookings b ON c.customer_id = b.customer_id
        AND b.status IN ('completed', 'confirmed')
    LEFT JOIN payments p ON b.booking_id = p.booking_id
        AND p.payment_status = 'completed'
GROUP BY COALESCE(c.state, c.country, 'Unknown')
ORDER BY total_revenue DESC
LIMIT 10;

Results:

| Location | Customers | Bookings | Revenue |
|———-|———–|———-|———|
| CA | 141 | 102 | $156,791 |
| TX | 69 | 49 | $106,919 |
| AU | 81 | 53 | $85,492 |
| GB | 65 | 44 | $74,970 |
| DE | 52 | 30 | $67,088 |
| OH | 56 | 41 | $64,691 |
| NY | 55 | 41 | $62,509 |
| WY | 53 | 43 | $60,958 |
| FL | 53 | 31 | $52,665 |
| FR | 59 | 41 | $51,556 |

No blank cells. No missing data. Every customer accounted for.

Line 3 is doing the work: `COALESCE(c.state, c.country, ‘Unknown’)`. For US customers, it uses the state. For international customers (no state), it falls back to the country code. If both are somehow missing, it shows “Unknown.”

Notice Line 6 also uses COALESCE: `COALESCE(SUM(p.amount), 0)`. This handles customers who have no payments — instead of showing NULL in the revenue column, it shows $0.

Three COALESCE Patterns You’ll Use Constantly

Pattern 1: Display defaults for reports

Replace NULLs with readable values:

-- Clean display for customer profiles
SELECT 
    first_name || ' ' || last_name AS customer_name,
    email,
    COALESCE(phone, 'Not provided') AS phone,
    COALESCE(dietary_restrictions, 'None listed') AS dietary_needs,
    COALESCE(state, country) AS location
FROM customers
ORDER BY customer_id
LIMIT 5;

This is the most common pattern. Every NULL becomes something meaningful, and your report looks professional instead of incomplete.

Pattern 2: Safe aggregations

Prevent NULLs from breaking your math:

-- Revenue per customer (including non-buyers)
SELECT 
    c.first_name || ' ' || c.last_name AS customer_name,
    COUNT(b.booking_id) AS bookings,
    COALESCE(SUM(p.amount), 0) AS total_revenue,
    COALESCE(ROUND(AVG(p.amount)::numeric, 2), 0) AS avg_booking_value
FROM customers c
    LEFT JOIN bookings b ON c.customer_id = b.customer_id
        AND b.status IN ('completed', 'confirmed')
    LEFT JOIN payments p ON b.booking_id = p.booking_id
        AND p.payment_status = 'completed'
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY total_revenue DESC
LIMIT 10;

Without COALESCE, customers with no payments would show NULL instead of $0 — and if you try to do further calculations with that NULL, the whole expression becomes NULL.

Pattern 3: Dietary restrictions audit

Understand what proportion of your data is actually populated:

-- Dietary restrictions summary with COALESCE
SELECT 
    COALESCE(dietary_restrictions, 'No restrictions listed') 
        AS dietary_status,
    COUNT(*) AS customer_count,
    ROUND(
        COUNT(*)::numeric / (SELECT COUNT(*) FROM customers) * 100, 1
    ) AS pct_of_total
FROM customers
GROUP BY COALESCE(dietary_restrictions, 'No restrictions listed')
ORDER BY customer_count DESC
LIMIT 10;

Results:

No restrictions listed: 782 customers (78.2%)
Nut allergy: 25 (2.5%)
Dairy-free: 24 (2.4%)
Vegan: 22 (2.2%)
Gluten-free: 21 (2.1%)
Kosher: 20 (2.0%)
Halal: 19 (1.9%)
Vegetarian: 18 (1.8%)
Pescatarian: 15 (1.5%)

Now operations knows exactly what they’re working with. 218 customers (21.8%) have dietary needs that require planning. That’s not a data quality issue — it’s operational intelligence.

The NULL Rules That Trip People Up

Understanding three rules about NULL behavior will save you hours of debugging:

Rule 1: NULL is not zero, and NULL is not empty string

-- These all return FALSE:
NULL = 0       -- FALSE
NULL = ''      -- FALSE  
NULL = NULL    -- FALSE (this one surprises people)

You can’t compare NULL with `=`. Use `IS NULL` or `IS NOT NULL` instead.

Rule 2: NULL breaks arithmetic

-- Any math with NULL returns NULL:
100 + NULL     -- NULL
NULL * 5       -- NULL

This is why `COALESCE(SUM(amount), 0)` matters. If there are no payments to sum, SUM returns NULL, and any downstream calculation collapses.

Rule 3: COUNT behaves differently

COUNT(*)              -- Counts all rows (including NULLs)
COUNT(column_name)    -- Counts only non-NULL values

This subtle difference matters when you’re counting things like dietary restrictions or phone numbers where NULLs are expected.

Common Mistakes to Avoid

Mistake 1: Using COALESCE when you should be filtering

If NULL rows shouldn’t be in your results at all, use `WHERE column IS NOT NULL` — don’t replace them with defaults. COALESCE is for display and calculations, not for hiding data quality problems.

Mistake 2: Defaulting to misleading values

`COALESCE(revenue, 0)` makes sense. `COALESCE(age, 25)` is dangerous — you’re making up data. Choose defaults that are clearly “no data” rather than plausible values.

Mistake 3: Forgetting COALESCE in GROUP BY

If you use `COALESCE(state, country)` in your SELECT, you need the same expression in GROUP BY — or you’ll get unexpected grouping.

Try This at Your Job

Look at any report you’ve built recently:

1. Find the blank cells. Which columns have NULLs that confuse the reader?
2. Add COALESCE with a sensible default value (“Not provided”, “N/A”, or 0 for numeric fields).
3. Check your aggregations. Are any SUM or AVG calculations returning NULL because of missing data? Wrap them in COALESCE.

These are small changes that make your reports look significantly more professional.

Until next time,
Brian ([say hi on twitter!](https://twitter.com/briangraves))

—

P.S. Handling messy data is one of the core skills in SQL for Business Impact. Module 2 (The Marie Kondo Blueprint) teaches you how to organize, filter, and clean real-world data systematically. Check it out at [sqlforbusinessimpact.com](https://sqlforbusinessimpact.com).

P.P.S. What’s the messiest data you deal with at work? Hit reply and tell me — I might use your scenario (anonymized) in a future newsletter. I read every response.