Is Null, If Null, and Is Not Null in SQL: Lesson 12 Free Beginner SQL Course
This is lesson 12/31 so you're making SO MUCH progress!
Got BDE?⚡️
Hi BDEs! Welcome to Lesson 12 of the free Big SQL Energy Beginner Course⚡️ Today we’re learning about handling NULLs in SQL!
🚨 🚨🚨🚨🚨🚨🚨🚨🚨 Don’t forget to snag my intermediate course at 20% off w/ code Jan2025. This deal will end at the end of the month after my Beginner series is complete!
If you’re new here, 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.
Big SQL Energy Intermediate is an intermediate SQL course designed to solve real 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. 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!
Use code Jan2025 for 20% off for all of January!
But if you’re here for the free BEGINNER Big SQL Energy course, the next lesson for you is below! ⬇️
Lesson 0: Intro & Getting Started
Before you start, you’ll need access to any IDE (integrated development environment) for SQL of your choosing. I highly recommend using a 🔗Hex data science notebook because it’s easy to get started with and stored in the cloud— so no headaches to download and set up. However, you can alternatively use DBeaver, MySQL Workbench, SSMS, or any others if you’d prefer. Just import the dataset used in the lesson, and you’re ready to go! Remember, if you get stuck or experience errors, ChatGPT and Gemini are great resources to learn about syntax and help you debug. Alright, let’s get started!
🔗 Join Hex Data Science Notebooks: https://app.hex.tech/signup/big-data-energy
🔗 Datasets (Orders & Customers): https://github.com/jessramosdata/big-sql-energy-beginner
Lesson 12: Is Null, If Null, and Is Not Null
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. Nulls can be a bit tricky, so let's learn how to check for them and replace the NULLs with another value. Here are 3 of the most common tools to use when working with NULLs:
Is Null
Checks if a value in a column is NULL or not and returns with a Boolean True/False value. It’s often used in CASE statements (in my 🔗intermediate course) and in the WHERE clause to filter. The query below pulls all the rows (orders) that do not have a review submitted for them:
SELECT
order_id,
review IS NULL
FROM
orders;
And this query shows how to filter for NULLs in the WHERE clause:
SELECT
*
FROM
orders
WHERE
review IS NULL;Is Not Null
Checks if a value in a column is NOT NULL or not and returns with a Boolean True/False value. It’s the opposite of IS NULL and is also often used in CASE statements (in my 🔗intermediate course) and in the WHERE clause to filter. The query below pulls all the rows (orders) that DO have a coupon logged for them:
SELECT
order_id,
coupon IS NOT NULL
FROM
orders;
And this query shows how to filter for NULLs in the WHERE clause:
SELECT
*
FROM
orders
WHERE
coupon IS NULL;So now we’ve learned how to identify NULLs, but what do we do with them?! Sometimes we remove them (aka filter them out). Sometimes we replace them with another value— either the mean, median, a 0, or something that makes sense for the business scenario. Let’s learn how to replace NULLs with another value:
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;
A few other great ways to handle NULLs are with COALESCE & 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 for 20% off NOW because this deal expires February 1. 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!