2026-08-28 · 8 min read
- sql
- database
- query-design
SQL uses NULL to represent an absent or unknown value. Because an unknown value cannot be proven equal or unequal to another value, ordinary comparisons produce a third logical result: unknown. WHERE and JOIN filters keep rows only when a predicate is true, so both false and unknown disappear.
This model is consistent, but it clashes with application languages where null is often treated as one comparable sentinel. Reliable queries make nullable behavior explicit at every predicate, join, aggregate, and API boundary.
Use NULL predicates intentionally
Test absence with IS NULL and IS NOT NULL. Do not rely on = NULL, <> NULL, or a driver rewriting those expressions. For null-safe equality, use the database's documented operator, such as IS NOT DISTINCT FROM where supported, and understand its portability cost.
- Write truth-table fixtures for value, different value, and NULL.
- Decide whether two missing values count as equal for the business rule.
- Use COALESCE only when a real domain default exists.
- Check index plans when wrapping searchable columns in functions.
Keep outer joins from collapsing
A LEFT JOIN preserves unmatched rows by filling right-side columns with NULL. A right-side condition in WHERE then rejects those rows, often turning the result into an unintended inner join. Put relationship restrictions in ON when unmatched left rows must survive.
Some WHERE checks are intentional, such as selecting only unmatched rows with right_table.id IS NULL. Name that anti-join purpose in code or comments so a future refactor does not move the predicate casually.
Avoid nullable NOT IN surprises
If a NOT IN list or subquery contains NULL, each comparison can become unknown and the query may return no rows. Prefer NOT EXISTS with a correlated condition when expressing an anti-join, or explicitly exclude NULL from a list whose contract permits it.
- Test empty subqueries and subqueries containing only NULL.
- Check nullable foreign keys before choosing an anti-join shape.
- Review generated ORM SQL rather than assuming language-level semantics.
- Keep constraints aligned with assumptions in query code.
Carry nullability through the API
A missing JSON field, explicit null, and empty string are different inputs. Define how each maps to SQL parameters and whether a filter is omitted, searches for NULL, or searches for an empty value. Bind typed parameters rather than concatenating SQL.
Use Flashman's SQL formatter to inspect generated queries, JSON formatter for request shapes, diff tool for result fixtures, case converter for field mappings, and timestamp converter for nullable date filters. Run authoritative tests against the production database engine because NULL-safe operators and optimizer behavior vary.