The Mystery of NULL: Unknown vs. Empty

The Invisible Problem

I was counting our users when I noticed something strange. Some users didn't have a "Last Login" date. It wasn't '2000-01-01', and it wasn't empty text. It was just... missing.

This was my first encounter with the **NULL**.

The Quest: Understanding the "Void"

In SQL, `NULL` is special. It doesn't mean "Zero" (a value) and it doesn't mean "Space" (a value). It means **"Unknown"** or **"Missing."**

Because it's a "State of being" and not a "Value," you cannot use `=` to find it. Writing `WHERE date = NULL` will always return zero rows.

The Implementation: Checking for Presence

To handle NULLs, we use `IS NULL` and `IS NOT NULL`.

-- Finding all users who have NEVER logged in

SELECT name

FROM users

WHERE last_login IS NULL;

Alternatively, to find everyone who *has* data:

SELECT name

FROM users

WHERE last_login IS NOT NULL;

The "Oops" Moment

Early on, I tried to do math with a NULL: `NULL + 10`. The result was... `NULL`.

**Pro Tip**: Anything combined with a NULL becomes NULL. It’s like a black hole for data. If you have a NULL in your price list, your total revenue calculation will fail! Tomorrow, we'll learn how to fill those holes.

The Victory

I was able to identify the users who had signed up but never actually entered the platform. We sent them a "Welcome Back" email. Handling the "Missing" data is often more important than handling the data you actually have.

Your Task for Today

Find a column in your database that has "Empty" looking cells. Try to filter for them using `IS NULL`. Notice that `IS NOT NULL` is the best way to clean up your reports.

*Day 14: The Null Trap—Handling Defaults with COALESCE.*