Before this: Missing is not empty (and neither is zero)
The idea
In SQL, NULL = NULL is not true, so a LEFT JOIN never pairs two missing keys. pandas behaves differently: merge treats missing keys as equal and will happily attach the lookup row whose key is missing.
That difference creates silent errors: orders with no customer suddenly get the segment of a placeholder customer. The fix in pandas is to remove lookup rows with a missing key before merging, or to merge only rows whose key is present.
After any lookup, count what didn't match. Unmatched rows are either kept with a missing value or quarantined with a reason, depending on the rules, but they are never dropped silently.
Worked example
Input: orders.customer_key: "C001", missing · customers: ("C001", retail), (missing, UNASSIGNED)
- Visual
Look up segment from customers on customer_key → Rows with no match: Keep, with missing values
- SQL (DuckDB)
SELECT o.id, c.segment FROM orders o LEFT JOIN customers c ON o.customer_key = c.customer_key
- pandas
lookup = customers[customers["customer_key"].notna()] out = orders.merge(lookup, on="customer_key", how="left")
Result: C001 → retail; missing key → missing segment (not UNASSIGNED)
Common mistakes
- Trusting pandas merge with missing keys.
- Using IS NOT DISTINCT FROM in SQL for business keys.
- Not counting unmatched rows.
Check yourself
Does pandas merge match NaN to NaN?
Yes. Remove missing keys from the lookup side first.
What should happen to an order whose key isn't in customers?
Whatever the rules say: keep it with a missing value or quarantine it, never drop it silently.
Practice it
Add the segment to every order without matching blanks.