Why equals NULL fails in SQL - The Real Reasons

The Big Idea

Why does checking for 'nothing' in your data never work the way you expect?

The Scenario

Imagine you have a list of friends and you want to find those whose phone number you don't know yet. You try to check if their phone number is equal to nothing, but it doesn't work as expected.

The Problem

Using the usual equals sign (=) to check for unknown or missing values (NULL) doesn't work because NULL means 'unknown'. Comparing anything to unknown with = always fails or returns no results, making your search frustrating and incomplete.

The Solution

SQL provides a special way to check for NULL values using IS NULL or IS NOT NULL. This lets you correctly find records where data is missing or unknown, solving the problem elegantly.

Before vs After

✗ Before

SELECT * FROM friends WHERE phone = NULL;

✓ After

SELECT * FROM friends WHERE phone IS NULL;

What It Enables

This concept allows you to accurately find and handle missing or unknown data in your database queries.

Real Life Example

When managing customer records, you can find all customers who haven't provided their email address yet by checking if the email field IS NULL.

Key Takeaways

NULL means unknown, not just empty or zero.

Using = NULL doesn't work because comparisons with unknown always fail.

Use IS NULL or IS NOT NULL to check for missing data correctly.