Null Is Unknown
In SQL null does not mean zero or empty string, it means unknown. This single idea explains most surprising behavior. Any comparison with null yields unknown, not true or false, which is why null equals null is not true.
Three Valued Logic
SQL uses three truth values: true, false, and unknown:
- A WHERE clause keeps a row only when the condition is true, so unknown rows are dropped.
- To test for null you must use IS NULL or IS NOT NULL, never the equals operator.
- AND and OR follow special rules, for example true OR unknown is true.
Nulls In Aggregates And Joins
Most aggregates ignore nulls, so AVG skips them and may differ from a manual sum over count. COUNT of a column ignores nulls while COUNT star counts every row. In joins, null keys do not match, even another null.
Taming Nulls
Use COALESCE to replace null with a default, and NULLIF to turn a value back into null. Be careful in NOT IN with a subquery, since a single null in the list can make the whole condition return no rows.
Key idea
Null means unknown, so comparisons yield unknown, WHERE keeps only true rows, aggregates ignore nulls, and you test with IS NULL rather than equals.