Handling NULL Values

In databases, you will often encounter missing data. A user might not provide a phone number when signing up, or an order might not have a shipping date yet because it hasn’t shipped.

In SQL, the concept of “missing” or “unknown” data is represented by the special keyword NULL.

NULL is not Zero, and it’s not Empty

It is crucial to understand that NULL is not the same as the number zero (0), and it is not the same as an empty string ('').

• 0 is a specific number.
• '' is a string with a length of zero characters.
• NULL means unknown or not applicable.

Because NULL is “unknown”, you cannot compare it using standard math operators like = or !=. Think about it logically: if I have two unknown values, are they equal? I don’t know! Therefore, in SQL, NULL = NULL actually evaluates to NULL (unknown), not TRUE!

IS NULL and IS NOT NULL

To check if a column contains missing data, you must use the special IS NULL operator.

SELECT * FROM users WHERE phone IS NULL;

This will return every row where the phone number is missing.

Conversely, to find rows where data does exist, use IS NOT NULL.

SELECT * FROM users WHERE phone IS NOT NULL;

This returns every user who successfully provided a phone number.

[!CAUTION] A very common beginner mistake is writing WHERE phone = NULL. This will fail silently and return zero rows, no matter what is in your table! Always use IS NULL.

NULLs and Other Operators

NULL values can cause unexpected behavior with other operators. For example, if you run WHERE age > 18, any row where age is NULL will be completely ignored. The database engine skips them because it cannot definitively say whether an unknown age is greater than 18.

Try using IS NULL in the interactive lab to track down users missing phone numbers!