Before this: Joins where missing keys never match
The idea
SQL never matches NULL keys. pandas merge does: a missing key on the left matches a missing key on the right.
Drop or separate rows with missing keys on the lookup side before merging, and check with indicator=True.
Worked example
Input: orders key <NA>; customers has a row with key <NA>
- Visual
Look up → missing keys never match
- SQL (DuckDB)
LEFT JOIN customers USING (customer_key) -- NULL never matches
- pandas
orders.merge(customers.dropna(subset=["customer_key"]), on="customer_key", how="left")
Result: The order with a missing key gets no segment
Common mistakes
- Assuming SQL behaviour in pandas.
- Filling missing keys with placeholders.
Check yourself
Do NaN keys match in pandas merge?
Yes, unless you remove them first.
Practice it
Join without matching blanks.