Handling NULLs in SQL: The Ultimate Guide

In SQL, NULL is the elephant in the room. It's everywhere, it's confusing, and if you don't handle it correctly, it will break your reports.

I've seen countless bugs caused by a misunderstanding of what NULL actually represents. Is it zero? Is it an empty string? Is it false?

No. NULL means "Unknown".

In this guide, we'll demystify NULL, explore why it behaves so strangely, and learn the tools you need to handle it like a pro.

The 3-Valued Logic: True, False, and... Unknown?

In most programming languages, a boolean expression is either True or False. In SQL, we have a third state: Unknown (which is what NULL results in).

This leads to some counter-intuitive behavior:

• NULL = NULL is Unknown (not True!)
• NULL != NULL is Unknown (not False!)
• NULL + 1 is NULL

Think about it: If I have an "unknown" value in my left pocket and an "unknown" value in my right pocket, are they equal? I don't know! They might be.

Interactive Example: The Filtering Trap

Let's see this in action. We want to find employees who are not in the 'Sales' department.

Wait, where is Charlie? Charlie's department is NULL. When SQL evaluates NULL != 'Sales', the result is Unknown. The WHERE clause only includes rows where the result is True. So Charlie is excluded, even though he is technically "not in Sales" (as far as we know).

How to Fix It

To handle NULL correctly, you must use the special operators IS NULL and IS NOT NULL.

-- Correct way to include NULLs
SELECT * 
FROM employees_null_basic
WHERE department != 'Sales' OR department IS NULL;

Essential Functions: COALESCE and Friends

Dealing with NULLs everywhere can be tedious. Thankfully, SQL provides functions to manage them.

1. COALESCE (The Life Saver)

COALESCE takes a list of arguments and returns the first non-NULL value. It's standard SQL and works in almost every database.

COALESCE(column_name, 'Default Value')

2. NULLIF

NULLIF(a, b) returns NULL if a equals b, otherwise it returns a. This is great for preventing division by zero errors!

price / NULLIF(quantity, 0) -- Returns NULL instead of error if quantity is 0

Interactive Example: Providing Defaults

Let's use COALESCE to make our data look cleaner.

Aggregation and NULLs

Here is a rule you must memorize: Aggregate functions (like SUM, AVG, MAX) ignore NULLs, with one major exception: COUNT(*).

• COUNT(column): Counts rows where column is NOT NULL.
• COUNT(*): Counts ALL rows, including NULLs.
• AVG(column): Calculates average of non-NULL values only.

Interactive Example: The Average Trap

See the difference? AVG(score) gives 75, but AVG(COALESCE(score, 0)) gives 50. Neither is "wrong", but you must choose the one that fits your business logic.

The "NOT IN" Pitfall

This is the most dangerous trap in SQL.

If you use NOT IN with a subquery that returns even a single NULL, the entire result will be empty!

-- If the subquery returns (1, 2, NULL)
SELECT * FROM table WHERE id NOT IN (1, 2, NULL);

Logic:

1. id != 1 AND id != 2 AND id != NULL
2. True AND True AND Unknown
3. Result: Unknown (so no rows are returned)

Best Practice: Always use NOT EXISTS or ensure your subquery filters out NULLs (WHERE col IS NOT NULL).

Interactive Challenge

Here is a table of tasks. Some tasks have a completed_date, others are NULL (pending).

Challenge: Write a query to find all tasks that are either pending OR completed after '2025-01-01'.

You have access to: tasks_null_challenge

• id (INTEGER)
• task_name (TEXT)
• completed_date (TEXT - YYYY-MM-DD)

SELECT * 
FROM tasks_null_challenge
WHERE completed_date > '2025-01-01' 
   OR completed_date IS NULL;

Conclusion

Handling NULLs correctly is what separates beginners from intermediate SQL users.

Key Takeaways:

1. NULL means "Unknown", not zero or empty.
2. Use IS NULL and IS NOT NULL for filtering.
3. Use COALESCE to provide default values.
4. Remember that COUNT(col) ignores NULLs, but COUNT(*) counts them.
5. Beware of NOT IN with NULLs!

Master these rules, and you'll never be surprised by a missing row again.