Working with NULLs and Expressions
Learn how SQL handles missing data using NULL, filter and replace NULL values with COALESCE, and create new insights through calculated expressions in your queries.
We'll cover the following...
Imagine we’re trying to calculate the average number of loyalty points for all our customers. Some new customers haven't made a purchase yet, so their LoyaltyPoints field might be empty. If we just run a standard average function, what should the database do with these empty values? Should it treat them as zero, or ignore them? This is a common problem in data analysis. How we handle missing information and perform calculations directly impacts the accuracy of our results. In this lesson, we’ll dive into how SQL deals with these exact scenarios.
By the end of this lesson, we will be able to:
Understand what a
NULLvalue represents and why it’s special.Correctly filter for rows that contain
NULLvalues.Use the
COALESCEfunction to handleNULLs in our query results.Create and use expressions to perform calculations and derive new data in our queries.
Understanding NULL
In the world of databases, data isn’t always complete. A customer might not have provided a phone number, or an order might not have a ShippedDate yet because it’s still being processed. To represent this “absence of a value”, SQL uses a special marker called NULL. It’s crucial to understand that NULL is not the same as zero (0 ...