Big SQL Energy Beginner Course: Lesson 24 Coalesce & Ifnull()

Jan 24, 2025

Got BDE?⚡️

Hi BDEs! Welcome to Lesson 24 of the free Big SQL Energy Beginner Course⚡️ Today we’re learning about Coalesce & IfNull() in SQL!

Return to Big SQL Energy Beginner homepage here to access the other lessons:

Return to Homepage 📚

If you’re new here:

💁🏽‍♀️ Who Am I?

I’m Jess Ramos, the founder of Big Data Energy and the creator of the BEST SQL course and community: Big SQL Energy⚡️. Check me out on socials: 🔗LinkedIn, 🔗Instagram, 🔗YouTube, and 🔗TikTok. And of course subscribe to my 🔗newsletter here for all my upcoming lessons and updates— all for free!

⚡️ What is Big SQL Energy?

🔗Big SQL Energy Intermediate is an intermediate SQL course designed to solve real-world business problems hands-on using realistic data and a modern tech stack. You’ll walk away with 2 SQL portfolio projects, portfolio building guidance, access to the Big Data Energy Discord community, and lots of confidence for you upcoming coding interviews! You also get access to all monthly events including masterclasses, office hours, and guest speakers. This course is all of the most important things I’ve learned on the job as a Senior Data Analyst in tech who grew from a $72K salary to over a $150K salary. And now I’m sharing it with YOU! It’s more than just another course— it’s a challenging program designed to upskill your SQL to the intermediate level, get ready to ace your live coding interviews, and build connections in the data community through the Discord and events.

JOIN now (pun intended 😉)

Lesson 23: Having vs. Group By

🎥 Video

One of the most challenging and ANNOYING parts of working with data is dealing with NULLs values. Nulls represent missing or unknown data, and they can sometimes cause unexpected results (and even incorrect results) in your analyses if not handled correctly. It might seem like the easiest thing to do is to simply remove them; however, this can have negative impacts to your analysis. NULLs aren’t always mistakes and nuisances — sometimes they’re actually very meaningful all in themselves. A null value in a column means that there’s no value. It's not the same as zero or an empty string, so we have to make decisions on how to handle them.

Here are 2 of my favorite ways to handle null values:

IFNULL():

IFNULL() lets you replace null values with a specified value. This is useful for cleaning data and preventing nulls from causing issues in calculations and further downstream in dashboards. Sometimes having NULLs will make you unable to perform a calculation, so you have to either remove them or replace them. The query below replaces all the NULL coupon values with 0 since a NULL coupon value is really $0 off from the order!

SELECT 
   order_id, 
   coupon,
   IFNULL(coupon, 0) AS coupon_clean
FROM 
   orders;

Coalesce():

Yes, it can be hard to spell, but it’s one of the MOST useful functions for data cleaning! Coalesce is like a fancy IF/THEN statement with multiple conditions but in a much more concise way. Coalesce returns the first non-null value in the list you give it, so it can be really useful for replacing null values with a prioritized list of substitutions.

You can input as many arguments or options into coalesce, and they can be other columns, fixed values, or even calculations! The basic syntax for N items is:

coalesce(option1, option2, ..., optionN)

We can use coalesce to replace null values with 0s like this:

SELECT 
   order_id, 
   coupon,
   coalesce(coupon, 0) AS coupon_clean,
   review,
   coalesce(review, 0) AS review_clean
FROM 
   orders;

Or we can even use it to substitute for a missing string value:

SELECT 
   first_name, 
   last_name,
   middle_name,
   coalesce(middle_name, 'Unknown') as middle_name,
   concat(first_name, middle_name, last_name) as full_name
FROM 
   user_list;

We haven’t learned left joins yet, and you don’t have a products table in this beginner course, but below we can see how we can replace the null total_sales values when left joining orders to products. This means that for the products without any orders, we can replace their null values with 0s. We learn about left joins (along with all the other joins) and use additional data like this products table in my 🔗intermediate course, so head on over there if you’re ready to step it up after this beginner course!


SELECT 
    product_name,
    coalesce(sum(quantity), 0) AS total_sales
FROM 
    products prod
LEFT JOIN
    orders ord
    on prod.product_id = ord.product_id
GROUP BY 
    1
;

Another great way to handle NULLs are with CASE statements which we’ll touch on a little bit in this course and learn SO IN DEPTH in my 🔗intermediate course. See you in the next lesson!

If you’ve enjoyed the free BEGINNER Big SQL Energy course, grab my intermediate course. There are no deadlines or timelines for this course— you start and take as long as you want. It will take your beginner skills to the next level and get you ready to ace your interviews. DM me or email me at courses@bdeanalytics.com if you have questions on the intermediate course!