Data Analysis / Intermediate: Relationships and Uncertainty / Lesson 8

Joins, Cardinality, and Changing Grain

Combine tables without silently multiplying measures or dropping unmatched entities, and distinguish SQL null matching from documented pandas merge behavior.

About 5 min readContent reviewed 2026-10-25

On this page

Lesson goals

Predict join rows from key multiplicity.
Preserve unmatched parents intentionally.
Audit fanout, coverage, and missing-key semantics.

Declare both grains and the expected relationship

Before joining, state what one row means on each side and whether the keys are unique. A customer table can be one row per customer while an order table is several rows per customer. Joining them produces order-grain matches, not one row per customer. A primary-key-looking column name does not establish uniqueness; inspect the actual contract and duplicate counts.

Classify the relationship as one-to-one, one-to-many, many-to-one, or many-to-many for the chosen key. If a key has a matches on the left and b on the right, an equality join can produce a times b matched combinations. Predict that multiplicity before summing a measure. Record the expected output grain as explicitly as a column's units.

Read an outer join row by row

An INNER JOIN keeps matching combinations. A LEFT JOIN additionally preserves each left row without a match, extending right-side columns with NULL. The first program has three customers: one with two orders, one with one, and one with none. Four output rows are therefore expected. The repeated customer identifier reflects the relationship rather than a defect to remove with DISTINCT.

If only qualifying orders should be attached while every customer remains, place that child qualification in ON. A WHERE test on the right-side status can reject the NULL-extended row and effectively remove unmatched customers. Test missing matches using a right-side non-null key, since an optional stored attribute may itself be NULL even on a genuine match.

python Preserve a customer without an order

import sqlite3
from contextlib import closing

with closing(sqlite3.connect(":memory:")) as db:
    db.executescript("""
        CREATE TABLE customers(id INTEGER PRIMARY KEY);
        CREATE TABLE orders(id INTEGER PRIMARY KEY, customer_id INTEGER, amount INTEGER);
    """)
    db.executemany("INSERT INTO customers VALUES (?)", [(1,), (2,), (3,)])
    db.executemany("INSERT INTO orders VALUES (?, ?, ?)",
                   [(10, 1, 20), (11, 1, 30), (12, 2, 15)])
    rows = db.execute("""SELECT c.id, o.id, o.amount
                         FROM customers AS c LEFT JOIN orders AS o
                           ON o.customer_id = c.id
                         ORDER BY c.id, o.id""").fetchall()
    for row in rows:
        print(row)
    print("rows", len(rows))

Expected output

(1, 10, 20)
(1, 11, 30)
(2, 12, 15)
(3, None, None)
rows 4

Customer1 appears twice, customer2 once, and customer3 once with missing order fields. The declared grain explains all four rows.

Prevent independent details from multiplying totals

Joining two separate one-to-many details can multiply their combinations. One order with two lines and three payment records produces six joined rows. If the order total of twenty is repeated on each, its joined sum is120 even though the original order total is20. The second program makes this failure visible alongside the correct order-grain control total.

Aggregate each detail to the required shared grain before combining summaries, or calculate header measures separately. SUM(DISTINCT amount) is not a general repair: two different orders with the same amount are still two legitimate orders. DISTINCT operates on values or projections, not on your unstated entity definition. Inspect row counts and key multiplicity at each join stage.

python Expose fanout and SQL missing-key behavior

import sqlite3
from contextlib import closing

with closing(sqlite3.connect(":memory:")) as db:
    db.executescript("""
        CREATE TABLE orders(id INTEGER PRIMARY KEY, amount INTEGER);
        CREATE TABLE lines(id INTEGER PRIMARY KEY, order_id INTEGER);
        CREATE TABLE payments(id INTEGER PRIMARY KEY, order_id INTEGER);
        INSERT INTO orders VALUES (1, 20);
        INSERT INTO lines VALUES (1, 1), (2, 1);
        INSERT INTO payments VALUES (1, 1), (2, 1), (3, 1);
    """)
    joined = db.execute("""SELECT COUNT(*), SUM(o.amount) FROM orders AS o
                            JOIN lines AS l ON l.order_id = o.id
                            JOIN payments AS p ON p.order_id = o.id""").fetchone()
    original = db.execute("SELECT COUNT(*), SUM(amount) FROM orders").fetchone()
    matched = db.execute("""WITH a(k) AS (VALUES (NULL), (1)),
                                 b(k) AS (VALUES (NULL), (1))
                            SELECT COUNT(*) FROM a JOIN b ON a.k = b.k""").fetchone()[0]
    print("fanout", *joined)
    print("order_grain", *original)
    print("sql_null_matches", matched)

Expected output

fanout 6 120
order_grain 1 20
sql_null_matches 1

Two lines times three payments yield six copies of20. The equality join on nullable keys matches only the known key1.

Do not transfer missing-key assumptions between tools

In an ordinary SQL equality join, NULL does not equal NULL, so the final count above includes only the known key. Official pandas merge documentation warns that missing keys on both sides can match each other. A pipeline translated from SQL into pandas can therefore change both cardinality and interpretation without changing the apparent key column names. pandas is not installed here, and no pandas execution is claimed.

Choose an explicit policy: reject missing required keys, quarantine them, or retain them as unmatched under a documented transformation. In pandas, validate can check an expected relationship and indicator can expose merge coverage. Those options do not establish that keys identify the correct real entity. Review missing-key treatment before using a synthetic sentinel that might accidentally match many unrelated rows.

Reconcile coverage, keys, and measures

A join audit compares input rows, distinct keys, duplicate frequencies, matched and unmatched entities, and meaningful control totals. Retention rates by subgroup can reveal that missing keys or stale mappings exclude one population disproportionately. A high overall match rate does not prove every important subgroup is represented. Inspect a controlled sample without exposing real personal identifiers.

Build tiny counterexamples with zero, one, and several children, repeated amounts, and missing keys. Confirm that the output grain matches the intended analytical question before plotting or estimating uncertainty. A join is a transformation of evidence: its denominator and multiplicity changes belong in the report, not only in a developer's debugging notes.

Common errors and fixes

A joined total exceeds the source control total.

Why it happens: Independent child relationships multiplied header rows.
How to fix it: Aggregate at the required grain before joining or keep header totals separate.
Prevent it next time: Check key multiplicity and control totals after each join.

Customers with no qualifying orders disappeared.

Why it happens: A WHERE predicate rejected the NULL side of LEFT JOIN.
How to fix it: Place the child-qualification rule in ON when all parents must remain.
Prevent it next time: Test parents with no children and no qualifying children.

A SQL-to-pandas translation matched missing keys unexpectedly.

Why it happens: The tools use different missing-key merge semantics.
How to fix it: Define and validate a missing-key policy explicitly before merging.
Prevent it next time: Include missing keys in cross-tool parity checks.

Check your understanding

1. Two orders each total50 and each has two lines. Why does SUM(DISTINCT header.amount) not repair the join?

Show worked solution

The joined sum is200, but DISTINCT retains only the value50, not two order identities. The true header total is100. Aggregate by order identifier or summarize the order table independently; equal amounts are not duplicate orders.

2. A left customer table has a NULL key and a1 key; the right table has the same. How do ordinary SQL equality and pandas merge differ?