How does SQL treat NULL and how do you compare against it?

NULL means "unknown or missing", not zero and not an empty string. Any comparison with NULL, even NULL = NULL, gives UNKNOWN, and WHERE keeps only rows where the condition is TRUE. This is three-valued logic.

SELECT * FROM users WHERE phone = NULL;   -- never returns rows
SELECT * FROM users WHERE phone IS NULL;  -- correct
SELECT * FROM users WHERE phone IS NOT NULL;

Useful tools:

• COALESCE(phone, 'n/a') returns the first non-NULL argument.
• IS DISTINCT FROM compares values and treats two NULLs as equal. MySQL uses <=> for the same idea.
• Aggregates such as SUM, AVG and COUNT(column) ignore NULLs, while COUNT(*) counts every row.

Traps to know: x <> 5 does not return rows where x is NULL, and NOT IN with a NULL in the list returns nothing.

Treat every nullable column as a third state and decide explicitly how queries should handle it.