SQL, data modeling, data quality, EDA, visualization · insight-1045
A negated filter drops NULL rows, so write the NULL branch explicitly
- Evidence type
- documented fact
- Difficulty
- easy
- Roles
- Data analyst, Data scientist, ML engineer
- Industries
- aviation, cross-industry
Summary: situation, action, reason, boundary
Situation
A query keeps "flights not affected by an event" with WHERE event <> 'strike', and the analyst expects every other flight to remain.
Action
Write the intended set: (event <> 'strike' OR event IS NULL), or event IS DISTINCT FROM 'strike'. Check how "no event" is stored by counting NULL, empty string and a sentinel value separately.
Reason
WHERE keeps rows whose predicate is true; a comparison with NULL is unknown and is discarded, also under negation.
Boundary
Exclusion filters, NOT IN lists, status != 'x' conditions and boolean flags with NULL.
Symptom or risk
Rows where event is NULL compare to NULL, which is not true, so the filter removes exactly the unaffected rows if "no event" is stored as NULL.
Exceptions
Columns declared NOT NULL, where the issue cannot occur.
Trade-offs
OR conditions can limit index use; IS DISTINCT FROM is clearer where supported.
Pitfalls
Believing the bug is absent because "no event" happens to be stored as an empty string in the test table.
Example
Synthetic airline data: flight_day.event holds an empty string, not NULL, on 20,482 of 20,824 rows; a copy where it was NULL would lose all of them under event <> 'strike'.
Lessons and concepts
Claims and their sources
Comparisons in PostgreSQL return NULL when either input is NULL, so a negated comparison on a NULL value is also NULL and is not selected by WHERE.