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

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;

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);

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);

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;

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;

Works but only if you understand how NULLs are treated.

ROUND

SELECT ROUND(AVG(score), 2)

FROM results;

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

MOD

SELECT MOD(10, 3);

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 & Database Enthusiast