Filtering Missing Data with IS NULL

1. What is a NULL Value?

First, it's essential to understand what NULL truly means in a database. A NULL value represents missing or unknown data. It is not the same as the number 0, a blank space, or an empty string (''). Think of it as an empty cell in a spreadsheet or an unanswered optional question on a form.

To get a clear picture of this concept, let's watch a short video that uses a very relatable analogy.

As the video explains, NULL is a placeholder for absence. This distinction is critical because NULL behaves differently from other values in SQL, especially when it comes to filtering.

2. The Correct Way to Filter for NULL Values

You might intuitively think you could find missing values by writing WHERE column_name = NULL. However, this will not work as expected.

In SQL, NULL is not considered a value that can be compared with standard operators like =, !=, >, or <. Any comparison to NULL with these operators results in NULL (which is treated as 'unknown' or 'false' in a WHERE clause), so your query won't return the rows you're looking for.

This is a very common pitfall for beginners. Let's read a quick explanation to solidify this point.

So, if we can't use =, how do we filter for NULLs? SQL provides two special operators for this purpose:

• IS NULL: To find rows where a value is missing.
• IS NOT NULL: To find rows where a value is present.

Let's see the correct syntax in action.

Here is the syntax summarized for your notes:

To find rows with missing values:

SELECT column1, column2
FROM your_table
WHERE column_to_check IS NULL;

To find rows with present values:

SELECT column1, column2
FROM your_table
WHERE column_to_check IS NOT NULL;

3. Practical Applications in Product Analysis

Now, let's apply this to scenarios you might encounter in your work. Handling missing data is fundamental to understanding user behavior and ensuring the quality of your analysis.

Example 1: Identifying Incomplete User Profiles

Imagine you want to prompt users to add a job title to their profile to improve networking features on your platform. First, you need to identify which users are missing this information.

SELECT user_id, email
FROM users
WHERE job_title IS NULL;

This query gives you a clean list of all users who have not yet filled in their job_title.

Example 2: Analyzing Churned vs. Active Users

Suppose your users table has a cancellation_date column. This column is NULL for active subscribers and contains a date for users who have churned. To analyze the behavior of only your active users, you would filter your queries like this:

SELECT user_id, subscription_plan
FROM users
WHERE cancellation_date IS NULL;

Example 3: Combining with Other Filters

You can combine IS NULL and IS NOT NULL with other operators like AND, OR, and LIKE that we've already learned.

Let's say you want to find users on the 'premium' plan who have been active for a while (i.e., they have a last_login_date) but have never filled out their profile bio.

SELECT user_id, email
FROM users
WHERE 
    subscription_plan = 'premium'
    AND last_login_date IS NOT NULL
    AND profile_bio IS NULL;

This query combines equality checks, IS NOT NULL, and IS NULL to create a very specific user segment.

4. Practice Exercises

Let's practice with a hypothetical events table. This table tracks user actions in your product. It has the following columns: event_id, user_id, event_name, and error_message. The error_message column is only filled if an action fails; otherwise, it is NULL.

Problem 1: Write a query to retrieve all the event_ids for events that completed successfully (i.e., they have no error message).

SELECT event_id
FROM events
WHERE error_message IS NULL;

Problem 2: Write a query to find the user_id and event_name for all failed 'payment_attempt' events.

SELECT user_id, event_name
FROM events
WHERE 
  event_name = 'payment_attempt'
  AND error_message IS NOT NULL;

Problem 3 (Challenge): Sometimes, you don't need the full list, just a count. Write a query to count how many events in the table have an error_message. (Hint: You can use COUNT(*) with a WHERE clause).

SELECT COUNT(*)
FROM events
WHERE error_message IS NOT NULL;

Conclusion

Congratulations on completing the first module! You've built a solid foundation for retrieving and filtering data in SQL.

In this lesson, we focused on handling missing data, which is a constant in data analysis.

Key Takeaways:

• NULL is a special marker in SQL that represents the absence of a value.
• NULL is distinct from 0 or an empty string ''.
• To filter for NULL values, you must use the IS NULL and IS NOT NULL operators.
• Standard comparison operators like = and != do not work for NULL checks.

You are now equipped with a powerful set of filtering tools: comparison operators (=, >), logical operators (AND, OR), list operators (IN), pattern matching (LIKE), and now, operators for handling missing data (IS NULL).

In our next lesson, we will move on to Module 2: Organizing and Limiting Query Results. We'll start by learning how to sort your data using ORDER BY, allowing you to organize results to easily find the most recent events, top users, or other ranked information.