IS NULL

NULL means 'no value' - not zero, not blank, not an empty string. Testing for NULL has its own operator, because normal comparisons cannot see it.

Why = NULL never works

• NULL is the absence of a value, so no comparison with it can be true.
• WHERE COMM = NULL is never true - not even for rows where COMM is NULL.
• WHERE COMM <> NULL is never true either. Both are 'unknown'.
• Use IS NULL and IS NOT NULL instead - they are the only reliable null tests.

IS NULL and IS NOT NULL

• WHERE COMM IS NULL finds employees with no commission recorded.
• WHERE COMM IS NOT NULL finds employees with a commission value, even 0.
• IS NULL works in WHERE, in CASE expressions, and in CHECK constraints.
• COUNT(*) counts NULL rows; COUNT(COMM) skips them - a direct consequence of this rule.

Writing NULL-safe conditions

• COALESCE(COMM, 0) replaces NULL with 0 for calculations.
• NULLIF lets you turn a sentinel value back into NULL.
• In outer joins, unmatched rows produce NULLs - test the join key with IS NULL to find them.
• Document which columns allow NULL - it decides half your WHERE logic.

Example