NULL represents a missing, unavailable or unknown value—not zero and not an empty string. Understanding SQL's three-valued logic is essential for writing correct filters, aggregates and anti-joins.
Key Takeaways at a Glance:
- •NULL represents missing, unknown, or inapplicable information; it is NOT zero, empty string, or false.
- •Equality comparisons (col = NULL) always evaluate to UNKNOWN; use 'IS NULL' or 'IS NOT NULL'.
- •Three-valued logic rules: TRUE AND UNKNOWN = UNKNOWN, TRUE OR UNKNOWN = TRUE, NOT UNKNOWN = UNKNOWN.
- •COALESCE(val1, val2, ...) returns the first non-NULL expression from its argument list.
In Simple Words (The Mental Model)
In SQL, NULL means 'I don't know'. If you ask 'Is this mystery box equal to 100?', the only logical answer is 'I don't know' (UNKNOWN). If you ask 'Is mystery box A equal to mystery box B?', the answer is still 'I don't know'. That's why two NULLs are never equal to each other in SQL.
Real-World Analogy
Think of an anonymous wrapped gift box. You don't know what is inside (NULL). If someone asks 'Does this box contain a watch?', you cannot answer Yes or No—you answer 'I don't know'. If you have two wrapped boxes, you cannot say they have the same gift. To test if the box is wrapped, you don't look inside, you simply check 'Is the wrapping paper present?' (IS NULL).