Topic 41 of 52 · Beginner
Why does a comparison silently exclude NULL rows?
NULL represents an unknown or missing value; comparisons such as NULL = 4 evaluate to UNKNOWN, not FALSE or TRUE. WHERE keeps only TRUE rows.
MySQL 8-compatible SELECT syntax; sample queries also execute in SQLite 3. Window functions require MySQL 8+.
Detailed answer
NULL represents an unknown or missing value; comparisons such as NULL = 4 evaluate to UNKNOWN, not FALSE or TRUE. WHERE keeps only TRUE rows.
Use IS NULL to test absence. If missing values should remain in an exclusion query, combine the comparison with OR column IS NULL. Decide explicitly whether missing data belongs in the result.
COALESCE supplies a fallback value, but replacing NULL with a sentinel can merge distinct business meanings. COUNT(column) omits missing values whereas COUNT(*) counts rows.
Internal working
SQL predicate evaluation uses three-valued logic. NOT UNKNOWN remains UNKNOWN; this explains why negating a comparison does not bring missing rows back.
Practical scenario
A placement eligibility report excludes students with missing city data when using city <> Chennai. Keep them for a separate data-cleanup workflow.
SELECT 1 WHERE NULL IS NULL;
SELECT COALESCE(NULL, 'missing') AS label;Common mistakes
- Writing column = NULL.
- Assuming NOT IN is safe when its subquery can return NULL.
Key takeaways
- WHERE retains only TRUE.
- Test NULL with IS NULL.
- Specify missing-data semantics.
Follow-up challenges
- How does COUNT differ from COUNT(column)?
- When would a NOT NULL constraint be preferable?