Search⌘ K
AI Features
Home
Courses
Introduction to Modern Databases

Working with NULLs and Expressions

Explore how SQL treats NULL values and why they require special handling using IS NULL, IS NOT NULL, and COALESCE functions. Learn to create expressions for calculations and derive new data in queries, improving data accuracy and report clarity.

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 NULL value represents and why it’s special.

  • Correctly filter for rows that contain NULL values.

  • Use the COALESCE function to handle NULLs 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 ...