2026-08-28 · 6 min read · Rahul Chitturi
- sql
- database
- debugging
SQL NULL means missing or unknown, not an ordinary value. Comparisons such as status = NULL and status <> NULL therefore evaluate to unknown rather than true, which can silently remove rows from a WHERE clause.
The same rule appears in joins, aggregates, constraints, and generated queries. A report may look correct until one nullable column enters the data and changes the result without causing a database error.
Reduce the query to nullable inputs
Format the exact query and list which expressions can produce NULL. Use a small fixture containing matching values, non-matching values, and NULL so each predicate has an observable result.
- Replace = NULL and <> NULL with IS NULL and IS NOT NULL
- Inspect nullable values introduced by LEFT or RIGHT JOIN
- Check whether NOT IN contains a NULL from its subquery
- Confirm bound parameters have the intended database type
Preserve outer-join intent
A filter on the nullable side of a LEFT JOIN can turn it into an effective inner join when placed in WHERE. Move relationship conditions into ON when unmatched left-side rows must remain, then test both matched and unmatched cases.
Do not add COALESCE everywhere as a quick fix. Replacing unknown values with a default changes business meaning and may prevent indexes from being used as expected.
A Flashman workflow
Use the SQL formatter to expose predicate and join structure, the JSON formatter for API parameters, the diff tool to compare expected and actual rows, and the case converter when database and application field names differ.
Turn the incident into a fixture with explicit NULL cases. The regression test should assert both rows that remain and rows intentionally excluded by the corrected predicate.