Left Join with Missing - Data Handling in Pure Python

Join two tables by a shared key, keeping every left-side row regardless of whether the key exists in the right table. Unmatched rows receive None for the right-side value. The trace shows result growing one pair per order, with (3, None) appearing for the unmatched key 'x'.

By hand

Build the same index dict from the customer lists. Then walk every order and call index.get(k), which returns None for missing keys — no if guard needed, so all five orders produce a result row.

                            naive.py
                        
Replay: real traced execution (multi-file project)

                            order_ids = [1, 2, 3, 4, 5]
order_ckeys = ['a', 'b', 'x', 'c', 'a']
cust_keys = ['a', 'b', 'c', 'd']
cust_names = ['alice', 'bob', 'carol', 'dave']
index = {}
for k, name in zip(cust_keys, cust_names):
    index[k] = name
result = []
for oid, k in zip(order_ids, order_ckeys):
    result.append((oid, index.get(k)))
print('RESULT:', result)

                        
1order_ids ← [1, 2, 3, 4, 5]1order_ids = [1, 2, 3, 4, 5]2order_ckeys = ['a', 'b', 'x', 'c', 'a']⋯values this step[1, 2, 3, 4, 5]order_ids
2order_ckeys ← ['a', 'b', 'x', 'c', 'a']1order_ids = [1, 2, 3, 4, 5]2order_ckeys = ['a', 'b', 'x', 'c', 'a']3cust_keys = ['a', 'b', 'c', 'd']⋯values this step['a', 'b', 'x', 'c', 'a']order_ckeys
3cust_keys ← ['a', 'b', 'c', 'd']⋯2order_ckeys = ['a', 'b', 'x', 'c', 'a']3cust_keys = ['a', 'b', 'c', 'd']4cust_names = ['alice', 'bob', 'carol', 'dave']⋯values this step['a', 'b', 'c', 'd']cust_keys
4cust_names ← ['alice', 'bob', 'carol', 'dave']⋯3cust_keys = ['a', 'b', 'c', 'd']4cust_names = ['alice', 'bob', 'carol', 'dave']5index = {}⋯values this step['alice', 'bob', 'carol', 'dave']cust_names
5index ← {}⋯4cust_names = ['alice', 'bob', 'carol', 'dave']5index = {}6for k, name in zip(cust_keys, cust_names):⋯values this step{}index
6k ← 'a', name ← 'alice'⋯5index = {}6for k, name in zip(cust_keys, cust_names):7    index[k] = name⋯values this step'a'k'alice'name
7index ← {'a': 'alice'}⋯6for k, name in zip(cust_keys, cust_names):7    index[k] = name8result = []⋯values this step{} → {'a': 'alice'}index
8k ← 'b', name ← 'bob'⋯5index = {}6for k, name in zip(cust_keys, cust_names):7    index[k] = name⋯values this step'a' → 'b'k'alice' → 'bob'name
9index ← {'a': 'alice', 'b': 'bob'}⋯6for k, name in zip(cust_keys, cust_names):7    index[k] = name8result = []⋯values this step{'a': 'alice'} → {'a': 'alice', 'b': 'bob'}index
10k ← 'c', name ← 'carol'⋯5index = {}6for k, name in zip(cust_keys, cust_names):7    index[k] = name⋯values this step'b' → 'c'k'bob' → 'carol'name
11index ← {'a': 'alice', 'b': 'bob', 'c': 'carol'}⋯6for k, name in zip(cust_keys, cust_names):7    index[k] = name8result = []⋯values this step{'a': 'alice', 'b': 'bob'} → {'a': 'alice', 'b': 'bob', 'c': 'carol'}index
12k ← 'd', name ← 'dave'⋯5index = {}6for k, name in zip(cust_keys, cust_names):7    index[k] = name⋯values this step'c' → 'd'k'carol' → 'dave'name
13index ← {'a': 'alice', 'b': 'bob', 'c': 'carol', 'd': 'dave'}⋯6for k, name in zip(cust_keys, cust_names):7    index[k] = name8result = []⋯values this step{'a': 'alice', 'b': 'bob', 'c': 'carol'} → {'a': 'alice', 'b': 'bob', 'c': 'carol', 'd': 'dave'}index
14for k, name in zip(cust_keys, cust_names):⋯5index = {}6for k, name in zip(cust_keys, cust_names):7    index[k] = name⋯
15result ← []⋯7    index[k] = name8result = []9for oid, k in zip(order_ids, order_ckeys):⋯values this step[]result
16k ← 'a', oid ← 1⋯8result = []9for oid, k in zip(order_ids, order_ckeys):10    result.append((oid, index.get(k)))⋯values this step'd' → 'a'k1oid
17result ← [(1, 'alice')]⋯9for oid, k in zip(order_ids, order_ckeys):10    result.append((oid, index.get(k)))11print('RESULT:', result)values this step[] → [(1, 'alice')]result
18k ← 'b', oid ← 2⋯8result = []9for oid, k in zip(order_ids, order_ckeys):10    result.append((oid, index.get(k)))⋯values this step'a' → 'b'k1 → 2oid
19result ← [(1, 'alice'), (2, 'bob')]⋯9for oid, k in zip(order_ids, order_ckeys):10    result.append((oid, index.get(k)))11print('RESULT:', result)values this step[(1, 'alice')] → [(1, 'alice'), (2, 'bob')]result
20k ← 'x', oid ← 3⋯8result = []9for oid, k in zip(order_ids, order_ckeys):10    result.append((oid, index.get(k)))⋯values this step'b' → 'x'k2 → 3oid
21result ← [(1, 'alice'), (2, 'bob'), (3, None)]⋯9for oid, k in zip(order_ids, order_ckeys):10    result.append((oid, index.get(k)))11print('RESULT:', result)values this step[(1, 'alice'), (2, 'bob')] → [(1, 'alice'), (2, 'bob'), (3, None)]result
22k ← 'c', oid ← 4⋯8result = []9for oid, k in zip(order_ids, order_ckeys):10    result.append((oid, index.get(k)))⋯values this step'x' → 'c'k3 → 4oid
23result ← [(1, 'alice'), (2, 'bob'), (3, None), (4, 'carol')]⋯9for oid, k in zip(order_ids, order_ckeys):10    result.append((oid, index.get(k)))11print('RESULT:', result)values this step[(1, 'alice'), (2, 'bob'), (3, None)] → [(1, 'alice'), (2, 'bob'), (3, None), (4, 'carol')]result
24k ← 'a', oid ← 5⋯8result = []9for oid, k in zip(order_ids, order_ckeys):10    result.append((oid, index.get(k)))⋯values this step'c' → 'a'k4 → 5oid
25result ← [(1, 'alice'), (2, 'bob'), (3, None), (4, 'carol'), (5, 'alice')]⋯9for oid, k in zip(order_ids, order_ckeys):10    result.append((oid, index.get(k)))11print('RESULT:', result)values this step[(1, 'alice'), (2, 'bob'), (3, None), (4, 'carol')] → [(1, 'alice'), (2, 'bob'), (3, None), (4, 'carol'), (5, 'alice')]result
26for oid, k in zip(order_ids, order_ckeys):⋯8result = []9for oid, k in zip(order_ids, order_ckeys):10    result.append((oid, index.get(k)))⋯
27stdout ← RESULT: [(1, 'alice'), (2, 'bob'), (3, None), (4, 'carol'), (5, 'alice')]⋯10    result.append((oid, index.get(k)))11print('RESULT:', result)values this stepRESULT: [(1, 'alice'), (2, 'bob'), (3, None), (4, 'carol'), (5, 'alice')]stdout

The Pythonic way

A list comprehension over zip(order_ids, order_ckeys) with index.get(k) handles matched and unmatched rows in one expression, with no filter clause.

                            library.py
                        

                            order_ids = [1, 2, 3, 4, 5]
order_ckeys = ['a', 'b', 'x', 'c', 'a']
cust_keys = ['a', 'b', 'c', 'd']
cust_names = ['alice', 'bob', 'carol', 'dave']
index = {k: name for k, name in zip(cust_keys, cust_names)}
result = [(oid, index.get(k)) for oid, k in zip(order_ids, order_ckeys)]
print('RESULT:', result)

                        

RESULT: [(1, 'alice'), (2, 'bob'), (3, None), (4, 'carol'), (5, 'alice')]

Implementation notes

The key difference from inner-join-by-key: the inner join drops row 3 entirely; the left join keeps it with None. Row count is always preserved for the left table in a left join.
index.get(k) returns None by default; pass a second argument (index.get(k, 'unknown')) to substitute a different sentinel.
This is the mechanism behind pd.merge(orders, customers, on='key', how='left') in the python-pandas track — mechanism here, API there.
The simpler loop (no if branch) also produces fewer trace events than the inner join version for the same dataset.