Joining Datasets

The one idea: Combining two tables can silently change your numbers in two ways: rows that don't match can vanish, and rows that match more than once get duplicated. Always count the rows before and after a join, and check that a known total still adds up.

⚠️ The trap: "A LEFT JOIN just tacks on some columns from the other table — it can't change my revenue total." That feels right and it's wrong. When one order matches several rows on the other side (one-to-many), that order's amount is duplicated once per match and your total silently inflates — count the rows before and after every join, and confirm a known total still adds up.

Real questions almost never live in one table. "Revenue by marketing channel" needs orders and the campaign table. "Churn by plan" needs subscriptions and customers. Joins are how tables combine, and they are the most dangerous operation in everyday analysis, because their failure modes are silent. A bad filter usually gives you an obviously weird count. A bad join gives you a plausible number that is 30% too high, and it passes review unnoticed.

Two questions govern every join: which rows survive? (the join type) and how many times does each row appear? (the relationship's cardinality). Get either wrong and the output is fiction. This lesson covers both, plus the row-count discipline that catches join bugs early.

Join type = which rows survive

A join matches rows in two tables on a key. The join type decides what happens to rows that don't find a match:

| INNER JOIN | Only…