DEV Community

Cover image for SQL Nulls Finally Made Sense: COALESCE, NULLIF, and Safer Calculations
Jessica Aki
Jessica Aki

Posted on

SQL Nulls Finally Made Sense: COALESCE, NULLIF, and Safer Calculations

#sql #postgres #beginners #database

This was one of those learning days where SQL didn’t look hard… but kept quietly breaking my queries.

Not with errors.

With NULLs.

Today I focused on understanding:

  • COALESCE
  • NULLIF
  • basic arithmetic operations
  • and how functions like SUM, ROUND, and MOD behave when NULL is involved

These are things I’ve seen mentioned casually in tutorials, but I didn’t really feel why they mattered until I started doing real calculations.

The Problem I Kept Running Into

I’d write what looked like a simple query:

  • calculate totals
  • divide one column by another
  • show numeric results

And suddenly…

  • values disappeared
  • calculations returned NULL
  • or worse everything broke because of a divide-by-zero

That’s when I realised:

SQL is extremely strict about missing or unsafe data.

And honestly? That’s a good thing.

COALESCE

COALESCE was the first function that really changed how I thought about NULLs.

What it does is simple:

Return the first non-null value in a list.

Example:

SELECT COALESCE(email, 'Email Unavailable')
FROM users;
Enter fullscreen mode Exit fullscreen mode

What this means in plain English:

  • If email exists → show it
  • If email is NULL → replace it with 'Email Unavailable'

This was the first time SQL felt… practical.

Instead of letting missing data break my output, I could control what users actually see.

That mental shift mattered.

NULLIF (The Function I Didn’t Understand at First)

At first, NULLIF felt pointless.

Why would I turn something into NULL on purpose?

Then I hit this problem:

Dividing by zero.

SQL doesn’t allow it. And it shouldn’t.

This is where NULLIF finally made sense.

SELECT 10 / NULLIF(0, 0);
Enter fullscreen mode Exit fullscreen mode

What’s happening here:

  • NULLIF(0, 0) → returns NULL
  • 10 / NULL → returns NULL

No crash. No error. Just a safe result.

Now combine it with COALESCE:

SELECT COALESCE(10 / NULLIF(0, 0), 0);
Enter fullscreen mode Exit fullscreen mode

This says:

  • Try the calculation
  • If it fails (returns NULL)
  • Fallback to 0

That’s when it clicked.

NULLIF prevents bad math. COALESCE cleans up the result.

They work together, not separately.

Arithmetic Operations

I also revisited basic arithmetic in SQL:

  • + addition
  • - subtraction
  • * multiplication
  • / division
  • % or MOD() for remainders
  • ! factorial

What stood out wasn’t the syntax — it was the behaviour.

Any arithmetic with NULL results in NULL.

That means:

SELECT price * quantity;
Enter fullscreen mode Exit fullscreen mode

Will quietly return NULL if either column is missing.

Which again reinforces why COALESCE matters so much.

SUM, ROUND, and MOD

Once I handled NULLs properly, aggregate and numeric functions finally behaved the way I expected.

SUM 

SELECT SUM(amount)
FROM payments;
Enter fullscreen mode Exit fullscreen mode

Works but only if you understand how NULLs are treated.

ROUND 

SELECT ROUND(AVG(score), 2)
FROM results;
Enter fullscreen mode Exit fullscreen mode

This was satisfying. Seeing clean, rounded values made the output feel presentation-ready.

MOD 

SELECT MOD(10, 3);
Enter fullscreen mode Exit fullscreen mode

Returns 1.

Simple, but useful when you actually need it.

Today's Takeaway

The big takeaway from today wasn’t memorising functions.

It was this:

SQL assumes missing data is dangerous unless you explicitly say otherwise.

  • COALESCE lets you decide what “missing” means
  • NULLIF lets you avoid invalid operations
  • arithmetic becomes predictable once NULLs are handled intentionally

What Still Feels Weird

I’m still adjusting to:

  • remembering that NULL isn’t zero
  • spotting where calculations might silently fail
  • knowing when to fix data vs fix the query

But compared to before? Huge progress.

Conclusion

NULL-handling is usually glossed over in beginner tutorials.

But in real databases?

It’s everywhere.

If you’re learning SQL and your queries sometimes technically work but return confusing results, this might be why.

What’s Next

Next up, I’m diving into dates and time:

  • DATE vs TIMESTAMP
  • AGE()
  • INTERVAL
  • EXTRACT

If you’re learning alongside me, feel free to follow the journey and check out the other posts in this series.

— Jessica Aki
Data and Database Engineering Enthusiast

I’m documenting my journey into data engineering learning SQL, databases, and the systems behind modern data platforms.

Top comments (0)