đź”—Join comparison
Which rows survive?
Side-by-side join comparison clarifies which rows survive for inner, left, right, and full outer joins, preventing “everything is a left join” habits. Outer joins preserve driving rows and pad missing matches with NULL; inner joins require matches on both sides.
Optimizers cannot freely reorder outer joins like inner joins; NULL-padding affects later predicates. Use a simple decision tree: mandatory relationship → inner; optional enrichment → left; reconciliation → full.
đź“ŠShapes
Know the driving table’s cardinality.
| Join | Result shape | Typical use |
|---|---|---|
| INNER | Matches only | Default facts assembly |
| LEFT | All left + optional right | Optional expansions, orphan detection |
| RIGHT | All right + optional left | Rewritable as LEFT with swap |
| FULL | Union of sides with NULL gaps | Reconciliation / diffs |
| CROSS | Cartesian product | Generators, dense combinations |
Join footguns
- Converting inner joins to left joins “just in case,” hiding data quality bugs.
- Filtering outer joins in WHERE in ways that nullify outer semantics.
- Confusing NATURAL JOIN (avoid) with real business keys.
Checks
- Draw Venn diagrams tied to concrete keys.
- Always know which table is driving row cardinality.
- Prefer explicit ON clauses with named keys.
- Revalidate counts when changing join types in reports.
Teaching
Pair this table with one real schema: name the foreign keys so “optional right” is not an abstract phrase.
TL;DR
Join types differ by which unmatched rows survive: INNER (both sides), LEFT (keep left), RIGHT (keep right), FULL (keep both). Anti-joins are usually LEFT JOIN … WHERE right.key IS NULL (or NOT EXISTS). Memorize the Venn diagram, then practice translating business questions (“optional profile”) into the correct join + null test.