SQL, data modeling, data quality, EDA, visualization · insight-0039
Put filters on the optional table in the ON clause, or your LEFT JOIN becomes an inner join
- Evidence type
- documented fact
- Difficulty
- easy
- Roles
- Data analyst, Data scientist
- Industries
- cross-industry
Summary: situation, action, reason, boundary
Situation
A query LEFT JOINs flights to promotions to keep all flights, then adds WHERE promo.channel = 'web'.
Action
Move conditions on the right-hand table into the ON clause, or write them as (promo.channel = 'web' OR promo.flight_no IS NULL) when that is the intent. Check the row count against the left table.
Reason
WHERE keeps only rows where the predicate is true; comparisons with NULL are never true, so unmatched rows disappear.
Boundary
Any outer join with filters, coverage metrics and "customers without X" queries.
Symptom or risk
Flights without a promotion have NULL in promo.channel, the comparison yields NULL, and WHERE drops them, so "share of flights with a promo" becomes 100%.
Exceptions
When you do want only matched rows, write an inner join so the intent is explicit.
Trade-offs
Conditions in ON are less familiar to some readers; add a comment.
Pitfalls
Testing on a sample where every left row happens to have a match, so the bug is invisible.
Lessons and concepts
Claims and their sources
In SQLite, comparison operators return NULL when an operand is NULL, while IS and IS NOT never return NULL.