In this lesson, you will learn how to navigate the "unknown" states in your data and why standard TRUE/FALSE logic falls short when NULL is involved.
The Tri-State Logic Challenge
In most programming, everything is either TRUE or FALSE. However, in SQL, we have a third state: UNKNOWN. This is represented by NULL. A NULL value doesn't mean "zero" or an "empty string"—it literally means the information is missing or unknown.
When you use a WHERE clause to filter data, SQL uses tri-state logic. If you ask the database, "Is this value equal to 10?", and the value is NULL, the database doesn't say "False." It says "Unknown." Because UNKNOWN is not TRUE, the row is excluded from your results.
A Concrete Illustration
Imagine a Users table with a column called FavoriteColor. Some users haven't picked a color yet, so their value is NULL.
If you run this query:
SELECT username
FROM Users
WHERE FavoriteColor = 'Blue';
If a user has NULL in their FavoriteColor column, the expression NULL = 'Blue' evaluates to UNKNOWN. Since the WHERE clause only returns rows where the expression is TRUE, that user will be missing from your results, even though they technically didn't say their favorite color was not blue.
If you want to find these "unknown" rows specifically, you cannot use the equals sign (=). You must use the special operator IS NULL:
SELECT username
FROM Users
WHERE FavoriteColor IS NULL;
Similarly, if you want to find everyone who has provided a color, you use IS NOT NULL. Understanding this is crucial because if you forget that NULL exists, you might write a query that silently ignores half your dataset, leading to incorrect reports or missing records in your application.
Key Takeaway: In SQL, NULL represents an unknown state, so standard comparisons like = will fail to find it; you must use IS NULL or IS NOT NULL to specifically target missing data.