The 5 best ways to handle NULL values in SQL

Handling NULL values isn't the most difficult challenge a data engineer will face on any given week, but it is one of the most common—and most annoying.

There's no single answer for how to work with NULLs, either. Whether you're wrestling with customer data, debugging financial reports, or building automated dashboards, it's always going to look different based on context.

That said, we're going to cover five of the best ways to handle NULL values so that you're prepared for any scenario. We'll cover how each technique works, when to use it, and pro tips to keep in mind.

1. Using COALESCE for default values

Ever had stakeholders complain about "missing" values in their marketing attribution dashboard? Here's a scenario that might sound familiar:

```sql

-- Raw data might look like this

SELECT user_id, campaign_source, conversion_value

FROM user_interactions

LIMIT 3;

‍

-- Results:

-- user_id  | campaign_source | conversion_value

-- 101      | facebook_ads   | 150

-- 102      | NULL           | 75

-- 103      | NULL           | 200

```

Instead of letting those NULLs create confusion, use `COALESCE` to provide meaningful context:

```sql

SELECT 

    user_id,

    COALESCE(campaign_source, 'direct_traffic') as attribution_source,

    conversion_value

FROM user_interactions;

```

This query examines each row's `campaign_source`. When it finds a NULL, instead of passing that NULL downstream, it substitutes `direct_traffic` as the value. It's like having a safety net that catches missing values and replaces them with something meaningful for your analysis.

Watch out for type mismatches in `COALESCE` arguments. If you're working with numeric columns, ensure all your fallback values match the column type. I've seen many cases where someone writes `COALESCE(numeric_column, 'N/A')` and the query fails.

Pro tip: `COALESCE` really shines when you need to establish a fallback hierarchy. Say you're building a customer contact system that needs to prioritize different contact methods:

```sql

SELECT 

    customer_id,

    COALESCE(

        mobile_phone,

        work_phone,

        email,

        'No contact info available - requires update'

    ) AS primary_contact

FROM customer_profile;

```

Under the hood, this query creates a prioritized contact lookup system. It first checks for a mobile number. No mobile? It checks work phone. No work phone? It tries email. If all three are NULL, it falls back to our default message. This cascading check continues until it finds the first non-NULL value, making it perfect for implementing business logic around data hierarchies.

2. ISNULL and IFNULL for simple substitutions

Imagine you're building a real-time inventory management dashboard. Some products haven't had their reorder points set yet, but you need to make sure the system doesn't break:

```sql

-- SQL Server version

SELECT 

    product_name,

    current_stock,

    ISNULL(reorder_point, 10) as safe_reorder_point

FROM inventory_status;

‍

-- MySQL/Snowflake version

SELECT 

    product_name,

    current_stock,

    IFNULL(reorder_point, 10) as safe_reorder_point

FROM inventory_status;

```

These queries are your safety net for inventory management. They check each product's `reorder_point` value. Any time they encounter a NULL (meaning no reorder point has been set), they automatically substitute 10 as a safe default threshold.

Remember that `ISNULL` and `IFNULL`aren't universally supported. If you're writing cross-platform SQL, stick with `COALESCE`. Otherwise, if you're working with Snowflake and another warehouse, you might end up having to update hundreds of `ISNULL` calls.

Pro tip: When you only need a simple substitution and want to keep your code as readable as possible, `ISNULL`/`IFNULL` are your friends. They're often slightly more performant than `COALESCE` for simple replacements.

3. Handling NULLs in aggregations

Imagine you're calculating monthly revenue metrics, and a stakeholder asks why the numbers don't match their expectations. Before you fire back a message telling them to check their numbers, try this.

```sql

SELECT 

    date_trunc('month', order_date) as month,

    AVG(COALESCE(transaction_amount, 0)) as avg_order_value,

    COUNT(*) as total_orders,

    COUNT(*) - COUNT(transaction_amount) as failed_transactions,

    SUM(CASE WHEN transaction_amount IS NULL THEN 1 ELSE 0 END) as null_amount_count

FROM orders

GROUP BY 1;

```

Let's break this query down. First, it groups orders by month. Then, for the average calculation, it treats NULL transaction amounts as 0 instead of ignoring them. It also gives you a complete picture of your data quality by counting total orders, calculating how many transactions failed, and explicitly counting NULL transactions.

Be careful with NULL handling in window functions. Unlike regular aggregations, window functions can produce unexpected results with NULLs. For example, `FIRST_VALUE(column) OVER (...)` might return NULL even when you have non-NULL values in your partition if the first row happens to be NULL.

Pro tip: Always provide context around NULL counts in your aggregations. It helps stakeholders understand data quality issues and can surface important insights.

4. Using CASE statements for complex logic

`CASE` statements are another great way to work with NULL values—particularly when working with complex logic.

Let's say you're building a customer health scoring system that needs to handle multiple scenarios for missing data.

```sql

SELECT 

    customer_id,

    CASE 

        WHEN last_login IS NULL AND signup_date < CURRENT_DATE - 90 

            THEN 'Inactive - High Risk'

        WHEN last_login IS NULL AND signup_date >= CURRENT_DATE - 90 

            THEN 'New User - Monitoring'

        WHEN last_login > CURRENT_DATE - 30 

            THEN 'Active'

        ELSE 'Inactive - Low Risk'

    END as customer_status,

    CASE 

        WHEN subscription_end_date IS NULL AND trial_end_date IS NULL 

            THEN 'No Trial Started'

        WHEN subscription_end_date IS NULL AND trial_end_date > CURRENT_DATE 

            THEN 'Trial Active'

        WHEN subscription_end_date > CURRENT_DATE 

            THEN 'Subscribed'

        ELSE 'Churned'

    END as subscription_status

FROM customer_activity;

```

This query is implementing a customer health scoring system that considers multiple factors. The first `CASE` statement evaluates user activity patterns, distinguishing between new users who haven't logged in yet (potentially normal) and long-term users who haven't logged in (potentially concerning).

`CASE` statements evaluate conditions in order and stop at the first match. If your conditions overlap, you might not get the results you expect. Always test your `CASE` statements with edge cases, especially around NULL handling.

Pro tip: When your `CASE` logic gets complex, consider breaking it into CTEs or views. This makes your code more maintainable and easier to test. Here's how:

```sql

WITH user_activity AS (

    SELECT 

        customer_id,

        CASE 

            WHEN last_login IS NULL AND signup_date < CURRENT_DATE - 90 

                THEN 'Inactive - High Risk'

            WHEN last_login IS NULL AND signup_date >= CURRENT_DATE - 90 

                THEN 'New User - Monitoring'

            WHEN last_login > CURRENT_DATE - 30 

                THEN 'Active'

            ELSE 'Inactive - Low Risk'

        END as customer_status

    FROM customer_activity

)

-- Now you can test this CTE independently

SELECT customer_status, COUNT(*) 

FROM user_activity 

GROUP BY 1;

```

5. NULL-aware filtering and joins

Sometimes, you need to find a solution that goes beyond simply finding missing data. You need something that understands the pattern of nulls across related tables and determining whether they represent actual data gaps or valid business states.

Let's say you need to find orders that haven't been properly attributed to marketing channels:

```sql

SELECT 

    o.order_id,

    o.order_date,

    o.order_value,

    COALESCE(COUNT(a.attribution_id), 0) as attribution_touchpoints

FROM orders o

LEFT JOIN attribution_events a 

    ON o.order_id = a.order_id

WHERE a.attribution_id IS NULL

GROUP BY 1, 2, 3;

```

This query pattern does the heavy lifting for you in several ways. The `LEFT JOIN` ensures you keep all your base records (orders in this case), while the `WHERE` clause helps you zero in on the gaps. The `COALESCE` around the `COUNT` ensures you get clean zeros instead of NULLs in your output—trust me, your downstream consumers will thank you for this.

Something to look out for: If your `JOIN` includes multiple conditions with potentially NULL values, you might need to handle each one explicitly. Like this:

```sql

-- This might miss matches due to NULLs

ON a.order_id = b.order_id AND a.region = b.region

‍

-- Better approach

ON a.order_id = b.order_id 

AND COALESCE(a.region, 'UNKNOWN') = COALESCE(b.region, 'UNKNOWN')

```

Pro tip: Create a data quality monitoring suite using NULL-aware joins. Here's a template you can use:

```sql

WITH data_quality_checks AS (

    SELECT 

        'Missing Attribution' as check_name,

        COUNT(*) as failing_records,

        COUNT(*) * 100.0 / (SELECT COUNT(*) FROM orders) as failure_rate

    FROM orders o

    LEFT JOIN attribution_events a 

        ON o.order_id = a.order_id

    WHERE a.attribution_id IS NULL

‍

    UNION ALL

‍

    SELECT 

        'Missing Customer Info',

        COUNT(*),

        COUNT(*) * 100.0 / (SELECT COUNT(*) FROM orders)

    FROM orders o

    LEFT JOIN customers c 

        ON o.customer_id = c.customer_id

    WHERE c.customer_id IS NULL

)

SELECT * 

FROM data_quality_checks 

WHERE failure_rate > 5  -- Alert threshold

ORDER BY failure_rate DESC;

```

A quick comparison table for handling NULL values

NULL handling isn't just about cleaning up data–it's about building robust systems that gracefully handle incomplete information. Keep these techniques in your tool belt:

1. Use `COALESCE` for flexible fallback hierarchies

2. Reach for `ISNULL`/`IFNULL` when simplicity matters

3. Make NULL handling explicit in your aggregations

4. Leverage `CASE` statements for complex business logic

5. Build NULL-aware joins and filters for data quality analysis

Remember: The best NULL handling strategy is the one that makes your data's behavior transparent to your stakeholders. When in doubt, make your NULL handling explicit and document your choices!

Ready to level up your SQL game? Start by auditing your existing queries for NULL handling patterns–you might be surprised what you find!