Amey-Thakur Null semantics
Null is not a value but an absence, and SQL's three-valued logic follows from that: comparisons with null are unknown rather than false, and unknown is not true, so the row does not pass. Most null bugs are this one rule applied somewhere you did not expect.
Method
- Test with IS NULL, never with equals. column = NULL is unknown for every row including the null ones. This is the single most common null mistake.
- Expect NOT IN to collapse with nulls. If the list contains a null, NOT IN yields no rows at all, because the comparison can never be proven true. Use NOT EXISTS instead, which handles it correctly (see sql-joins).
- Know which aggregates skip nulls. Most aggregates ignore null inputs, so an average is over non-null rows only. That is often what you want and often not what you assumed.
- Watch nulls flowing from outer joins. A left join produces nulls for unmatched rows, and any later comparison on those columns is unknown, which turns filters into unintended inner joins.
- Decide what null means in each column. Not applicable, not yet known, and not provided are different, and a schema that conflates them produces queries nobody can write correctly (see schema-design).
- Use COALESCE at the edge, not everywhere. Substituting a default is right at presentation and wrong in the middle of logic, where it hides the distinction you may need.
Boundaries
- Uniqueness treatment of nulls differs between engines, so a unique constraint may or may not permit several nulls.
- Sort order for nulls also varies, which changes pagination and top-N results between databases.
- Avoiding nulls entirely by using sentinel values trades one problem for a worse one, since sentinels participate in arithmetic.