In this lesson, you will learn why databases treat "missing information" as a distinct third state beyond simple True or False, and how this impacts your queries.
The Gap in Binary Logic
In standard logic, a statement is either True or False. However, real-world data is often incomplete. Imagine you have a Customers table with a column for PhoneNumber. If a customer hasn't provided their number, we don't store "0" or "None"—we use NULL.
NULL represents the absence of a value. It is not the same as zero, an empty string, or a blank space; it simply means "we don't know."
Entering Three-Valued Logic (3VL)
When you write a query to filter data, SQL must evaluate conditions. Because of NULL, SQL uses Three-Valued Logic:
- True
- False
- Unknown (the result when comparing anything to
NULL)
Think of it like this: If you ask "Is the phone number greater than 555-0100?", and the value is NULL, the database cannot confirm or deny it. The answer becomes Unknown.
How Filtering Works
SQL is designed to return rows only when a condition evaluates to True. If a condition results in False or Unknown, that row is excluded from your results.
Consider this table:
| Name | Age |
|---|---|
| Alice | 25 |
| Bob | NULL |
If you run this query:
SELECT * FROM Users WHERE Age > 20;
- Alice (25): 25 > 20 is
True. Alice is included. - Bob (NULL): NULL > 20 is
Unknown. Bob is not included.
Even if you try to find him by saying WHERE Age = NULL, it still won't work! Comparing a value to NULL using = always results in Unknown. To handle missing data, SQL provides special operators like IS NULL or IS NOT NULL to specifically target that "unknown" state.
Understanding this is vital: NULL isn't a value you compare; it is a placeholder for information you do not yet have, and it requires specific handling to be retrieved.
Key Takeaway: Because SQL uses three-valued logic, NULL is not equal to anything—not even itself—meaning you must use IS NULL to find missing data rather than standard comparison operators.