Working with NULL

The Billion Dollar Mistake

NULL is one of the most misunderstood concepts in SQL. It causes bugs, returns unexpected results, and trips up even experienced developers. But once you understand how it works, you can avoid the pitfalls.

What NULL Actually Means

NULL means "unknown" or "missing." It's not zero. It's not an empty string. It's the absence of a value.

Think of it this way:

• 0 — means "the value is zero"
• '' (empty string) — means "the value is blank text"
• NULL — means "we don't know what the value is"

This distinction matters because NULL behaves differently from actual values.

A Real Example

Let's look at our fintech_app_users table:

Some users haven't provided their phone number — those values show up as NULL. We don't know their phone numbers. Maybe they never entered one. Maybe the data was lost. The point is: we don't have a value.

Finding NULL Values: IS NULL

Here's the first surprise: you can't use = to check for NULL.

-- This does NOT work:
SELECT *
FROM fintech_app_users
WHERE phone_number = NULL;

This query returns nothing, even though we have rows with NULL phone numbers. Why? Because in SQL, NULL = NULL evaluates to... NULL (which is treated as false). Instead, use IS NULL. 

Users Missing Phone Numbers

Excluding NULL Values: IS NOT NULL

To find rows where a value exists, use IS NOT NULL.

This gives you only rows where phone_number has an actual value.

Wine Varieties Tasted by Roger Voss

NULL in Comparisons

NULL makes comparisons tricky. Any comparison with NULL returns NULL (treated as false):

NULL = NULL  -- Returns NULL (not true!)
NULL <> NULL -- Returns NULL (not true!)
NULL > 5     -- Returns NULL
NULL = 'text'-- Returns NULL

This means rows with NULL values are excluded from most WHERE clauses, even when you might not expect it.

NULL Gets Excluded Silently

-- This misses NULL rows!
SELECT * FROM fintech_app_users
WHERE status != 'active';

-- This catches them:
SELECT * FROM fintech_app_users
WHERE phone_number IS NULL;

Users with NULL phone numbers are also excluded — because NULL <> '555-1234' evaluates to NULL, not true.

To include NULLs, explicitly check for them. Fix the query by adding OR phone_number IS NULL:

NULL with AND and OR

NULL follows three-valued logic (true, false, NULL). This affects how AND and OR behave:

The practical takeaway: if any part of your condition might be NULL, the whole condition might not behave as expected.

Handling NULL with COALESCE

COALESCE returns the first non-NULL value from a list. It's useful for replacing NULLs with default values:

The original data doesn't change — COALESCE just controls what appears in your results.

COALESCE can take multiple arguments. It returns the first one that isn't NULL:

COALESCE(phone_number, mobile, email, 'No contact')

This checks phone number first, then mobile, then email, and falls back to 'No contact' if all are NULL.

Common Mistakes

Using = NULL Instead of IS NULL

-- Wrong:
WHERE department = NULL

-- Correct:
WHERE department IS NULL

Forgetting that NOT IN Excludes NULLs

-- If the list contains any NULLs, NOT IN returns nothing:
WHERE department NOT IN ('HR', NULL, 'Admin')

-- Correct: remove the NULL from the list, check separately if needed
WHERE department NOT IN ('HR', 'Admin')

Assuming Empty String Equals NULL

-- These are different:
WHERE name = ''  -- Empty string
WHERE name IS NULL  -- Unknown value

Key Takeaways

• NULL means unknown or missing — not zero, not empty string.
• Use IS NULL and IS NOT NULL, never = NULL.
• Comparisons with NULL return NULL (treated as false).
• Be explicit about whether NULL rows should be included in your results.
• Use COALESCE to replace NULLs with default values.

What's Next

You now know how to filter data in all the ways that matter. In the next lesson, you'll learn to control the order of your results with ORDER BY — essential for any "top N" question.