Handling NULLs in Analytical Queries

What is a NULL Value?

First, let's be precise about what NULL means. A NULL value in a database column represents a missing or unknown value. It's crucial to understand that NULL is not the same as:

• The number 0.
• An empty string ''.
• A blank space ' '.

Think of it as a placeholder for "we don't know". This distinction has significant implications for our queries. For a quick introduction, please watch the first minute of this video.

The Problem: How NULLs Affect Calculations

When performing analysis, NULL values can cause two major problems:

1. Inaccurate Aggregations: Most aggregate functions like AVG(), SUM(), and COUNT(column_name) simply ignore rows with NULL values. If you're calculating the average order value and some orders have a NULL amount, those orders won't be part of the calculation at all. If the business assumes a missing value should be treated as zero, your reported average will be inflated.
2. Broken Mathematical Operations: Any arithmetic operation involving a NULL results in NULL. For example, 100 + NULL is NULL. Similarly, concatenating strings like 'John' + ' ' + NULL also results in NULL. This can lead to unexpected empty fields in your reports.

Let's watch a practical demonstration of these issues.

The Solution Part 1: Replacing NULLs with COALESCE

The most common and versatile function for handling NULLs is COALESCE. It takes a list of arguments and returns the first non-NULL value it encounters, reading from left to right. It is an ANSI SQL standard, meaning it works across most database systems (PostgreSQL, SQL Server, BigQuery, etc.).

Syntax: COALESCE(value1, value2, ..., fallback_value)

Simple Replacement

The simplest use is to provide a single fallback value. In the video example, to treat NULL scores as 0 before averaging, you would use AVG(COALESCE(score, 0)).

Creating Fallback Hierarchies

COALESCE's real power comes from its ability to handle multiple arguments, creating a "fallback hierarchy." This is perfect for business logic. Imagine you need to find the best contact method for a customer, prioritizing their mobile number, then their work phone, then their email.

SELECT
    customer_id,
    COALESCE(
        mobile_phone,
        work_phone,
        email,
        'No contact info'
    ) AS primary_contact
FROM customers;

This query checks mobile_phone first. If it's NULL, it moves to work_phone. If that's also NULL, it tries email. If all are NULL, it returns the default string 'No contact info'.

This article provides another great example of this business use case.

COALESCE vs. ISNULL/IFNULL

You might also encounter ISNULL (specific to SQL Server) and IFNULL (specific to MySQL and others). These functions work similarly but are limited to only two arguments and are not universally supported.

For portability and flexibility, COALESCE is almost always the better choice.

SELECT
    product_id,
    COALESCE(sale_price, msrp, 0) AS final_price
FROM products;

The Solution Part 2: Replacing Values with NULLIF

Sometimes, you need to do the opposite: turn a specific value into a NULL. This is where NULLIF comes in. It takes two arguments and returns NULL if they are equal; otherwise, it returns the first argument.

Syntax: NULLIF(value_to_check, value_if_equal)

This is extremely useful for two main scenarios:

1. Data Cleaning: Datasets often use placeholder values like -1, 'N/A', or even empty strings '' to represent missing data. NULLIF allows you to convert these into proper NULLs for consistent handling.

   For example, if a department column uses an empty string for unknown departments, you could clean it like this before joining: NULLIF(department, ''). The ByteScout article (resource LINK) shows a great example of this when COALESCE alone isn't enough.

2. Preventing Division by Zero Errors: This is a classic use case. A query like SELECT revenue / number_of_orders will fail if number_of_orders is 0. You can prevent this gracefully with NULLIF.

   SELECT revenue / NULLIF(number_of_orders, 0)
   

   If number_of_orders is 0, NULLIF turns it into NULL. Since any math operation with NULL results in NULL (not an error), the query runs successfully, producing a NULL for that row.

Let's watch a clear demonstration of NULLIF, focusing on the division-by-zero scenario.

Filtering with IS NULL and IS NOT NULL

Finally, to filter rows based on NULL values, you must use the IS NULL or IS NOT NULL operators in your WHERE clause.

You cannot use = NULL or != NULL. Because NULL represents an unknown state, it is not considered equal (or unequal) to anything, including itself.

-- Correct: Find all customers without a recorded first name
SELECT *
FROM customers
WHERE first_name IS NULL;

-- Incorrect: This will not return any rows
SELECT *
FROM customers
WHERE first_name = NULL;

This short video provides a good review of how to use IS NULL and IS NOT NULL for filtering.

Quick Reference

Here is a handy image summarizing the functions we've discussed today. It's a great cheat sheet to keep available as you write your queries.

Conclusion

You've now learned the essential techniques for managing NULL values in SQL. This is a fundamental skill that moves you from simply writing queries to writing robust, reliable analytical code. Incorrectly handled NULLs can lead to flawed reports and bad business decisions, and as a data analyst, it's your job to prevent that.

Key Takeaways:

• NULL means "unknown" and is different from 0 or ''.
• Aggregate functions typically ignore NULLs, which can skew results like AVG().
• Use COALESCE(value, fallback) to replace NULLs and ensure accurate calculations. It is the flexible, industry-standard choice.
• Use NULLIF(value, value_if_equal) to turn specific values (like 0 or placeholder strings) into NULL for cleaning or to prevent errors like division-by-zero.
• Always use IS NULL or IS NOT NULL in your WHERE clause to filter for NULL values.

Next Lesson Preview:

In this module, you've used Common Table Expressions (CTEs) extensively to structure complex logic like cohort analysis. In our next and final lesson of this module, we will formalize this practice. You will learn how to rewrite subqueries as CTEs or JOINs to improve query readability and maintainability, a key skill for collaborating with other analysts and building professional-grade analytical scripts.