Merging and Relating

The previous topic opened up the inside of the rows, and by the end the dataset was clean: missing values were flagged, duplicates removed, types converted, categories and text corrected. Even so, the dataset is not ready for analysis, because it is still shaped by its source, not by the question. Subscriber information sits in one table, meter readings in another, field visits in a third; no question can be put to any one of these three tables alone.

This topic reshapes the dataset around the question, and the first step is to bring the tables side by side. The measure of this step is the following: a join changes the row count, and the direction of the change tells you nothing by itself. The row count can drop, grow, or stay the same; each of the three can be the sign of a correct result and of a silent error alike. For that reason this lesson writes down, item by item, what went in, what came out, and where the difference went.

• TF1. The dataset is the cleaned version that came out of the previous topic and is fictional: the subscriber table holds one subscriber per row, the reading table holds one period reading per row, and the visit table holds one field visit per row. All three tables are modeled with node; no query interface is imitated.
• TF2. A join places the rows of two tables side by side on a key. A union, introduced in the Databases curriculum, stacks the rows of two tables one under the other and leaves the column layout unchanged: one adds rows, the other adds columns and changes the row count according to the number of matches on the key. The one measured in this lesson is the second.
• TF3. An inner join outputs only the matching rows; unmatched rows on both sides drop. An outer join keeps the unmatched rows of the left table with an empty right side. The difference between the two is not a preference but a number.
• TF4. The grain of the key is a decision separate from the join itself. If a key in the left table corresponds to several rows in the right table, the left row appears that many times; this is called fan-out.
• TF5. A join can drop and fan out at the same time. In that case, even when the output row count is lower than the input, no filtering has taken place, and the totals have been counted both too little and too much.

What the Inner Join Drops

// join.mjs — MODEL: a join over the three tables that come out of cleaning, on a key.
// The dataset is fictional; no query interface is imitated.
export const SEED = 20260218, RAW = 1400, PERIOD = ["2026-01", "2026-02", "2026-03"];
const mix = (x) => { x = Math.imul(x ^ (x >>> 16), 2246822507);
  x = Math.imul(x ^ (x >>> 13), 3266489909); return (x ^ (x >>> 16)) >>> 0; };
const rng = (t) => { let s = mix(t);
  return () => (s = (s * 1664525 + 1013904223) >>> 0) / 4294967296; };
const pick = (u, w) => { let t = 0;
  for (let i = 0; i < w.length; i++) { t += w[i]; if (u < t) return i; } return w.length - 1; };
export const REGION = [["north", 0.28, 21], ["south", 0.22, 17], ["east", 0.18, 26],
  ["west", 0.14, 14], ["central", 0.18, 23]].map(([name, share, base]) => ({ name, share, base }));

// Subscriber table: those dropped in cleaning stay outside, every remaining row is a subscriber.
export const SUBSCRIBER = [], DROPPED = [];
for (let i = 0; i < RAW; i++) {
  const r = rng(SEED + i), b = REGION[pick(r(), REGION.map((x) => x.share))];
  const k = { subscriber_id: 10001 + i, region: b.name, base: b.base,
    household_size: pick(r(), [0.06, 0.24, 0.30, 0.24, 0.11, 0.05]),
    satisfaction: pick(r(), [0.08, 0.14, 0.27, 0.34, 0.17]) + 1 };
  (r() < 0.046 ? DROPPED : SUBSCRIBER).push(k);
}
// Reading table: some readings belong to dropped subscribers, some subscribers
// carry no readings at all, some subscriber+period pairs appear twice with two different values.
export const READING = [];
let s = 0;
for (const k of [...SUBSCRIBER, ...DROPPED]) {
  const r = rng(SEED + 7000 + k.subscriber_id), n = pick(r(), [0.05, 0.12, 0.21, 0.62]);
  for (let d = 0; d < n; d++) {
    const t = r() < 0.038 ? 0 : Math.round(k.base * Math.exp((r() + r() + r() - 1.5) * 0.62)
      * (1 - d * 0.05) * 100) / 100;
    READING.push({ row_id: ++s, subscriber_id: k.subscriber_id, period: PERIOD[d], usage_m3: t });
    if (r() < 0.028) READING.push({ row_id: ++s, subscriber_id: k.subscriber_id, period: PERIOD[d],
      usage_m3: Math.round(t * 1.02 * 100) / 100 });
  }
}
// Visit table: one row is one field visit; it does not depend on the period.
export const VISIT = [];
for (const k of SUBSCRIBER) {
  const r = rng(SEED + 31000 + k.subscriber_id), n = pick(r(), [0.58, 0.27, 0.11, 0.04]);
  for (let z = 0; z < n; z++) VISIT.push({ subscriber_id: k.subscriber_id,
    result: ["positive", "repeat", "unreachable"][pick(r(), [0.60, 0.28, 0.12])] });
}

// Each row of the left table is placed beside the right rows whose key is equal.
// The "outer" option keeps an unmatched left row with an empty right side.
export const join = (left, right, ka, kb, outer = false) => {
  const index = new Map();
  for (const r of right) { const k = kb(r);
    if (!index.has(k)) index.set(k, []); index.get(k).push(r); }
  const out = [], matched = new Set();
  let unmatchedLeft = 0;
  for (const l of left) {
    const e = index.get(ka(l));
    if (!e) { unmatchedLeft++; if (outer) out.push({ left: l, right: null }); continue; }
    for (const r of e) { out.push({ left: l, right: r }); matched.add(r); }
  }
  return { out, unmatchedLeft, unmatchedRight: right.length - matched.size };
};
export const gen = (w) => (h) => h.map((v, j) => (j ? String(v).padStart(w[j])
  : String(v).padEnd(w[0]))).join("");

if (import.meta.url === `file://${process.argv[1]}`) {
  const id = (x) => x.subscriber_id;
  console.log(`model (seed ${SEED}): subscriber ${SUBSCRIBER.length} rows, reading ` +
    `${READING.length} rows, visit ${VISIT.length} rows`);
  const y = gen([34, 11, 11, 9, 12, 12]);
  console.log("\n" + y(["join", "left in", "right in", "out", "left miss",
    "right miss"]));
  const B = [
    ["B1 reading <- subscriber (inner)", READING, SUBSCRIBER, false],
    ["B2 subscriber <- reading (inner)", SUBSCRIBER, READING, false],
    ["B3 subscriber <- reading (outer)", SUBSCRIBER, READING, true],
  ];
  const runs = {};
  for (const [name, left, right, outer] of B) {
    const r = join(left, right, id, id, outer);
    runs[name.slice(0, 2)] = r;
    console.log(y([name, left.length, right.length, r.out.length, r.unmatchedLeft, r.unmatchedRight]));
  }
  console.log(`\ninner join drops ${runs.B1.unmatchedLeft} of ${READING.length} readings; ` +
    `the same join never shows ${runs.B1.unmatchedRight} of ${SUBSCRIBER.length} subscribers ` +
    `in the output table`);
  console.log(`outer join keeps those ${runs.B3.unmatchedLeft} subscribers with an empty reading: ` +
    `${runs.B3.out.length} rows instead of ${runs.B2.out.length}`);
  console.log(`neither join produced a warning`);
}

model (seed 20260218): subscriber 1329 rows, reading 3471 rows, visit 818 rows

join                                  left in   right in      out   left miss  right miss
B1 reading <- subscriber (inner)         3471       1329     3302         169          69
B2 subscriber <- reading (inner)         1329       3471     3302          69         169
B3 subscriber <- reading (outer)         1329       3471     3371          69         169

inner join drops 169 of 3471 readings; the same join never shows 69 of 1329 subscribers in the output table
outer join keeps those 69 subscribers with an empty reading: 3371 rows instead of 3302
neither join produced a warning

Three rows read the same two tables and give three different numbers. B1 and B2 differ only in which table is on the left, and both output 3,302 rows: in an inner join the order of the sides does not change the output rows, it only changes in which column the unmatched ones are reported. B3 gives 69 more rows with the same key, because it keeps the 69 subscribers that carry no readings with an empty reading.

The loss is accounted for in two items. 169 readings carry a subscriber number, but that number does not exist in the subscriber table; these are the readings of the 71 records dropped in cleaning. 69 subscribers, on the other hand, are real subscribers whose meters were not read at all in that period, and they never appear in the output of the inner join. The second is this lesson’s silent loss: there is no error message, not even an empty cell; the row was never produced. If the question “average usage” is put to the output of the inner join, the 69 subscribers that were never read do not take part in the answer; whether those 69 dropped at random or in a pattern cannot be read from the output at all.

This is where the measure comes from: an inner join is a filtering decision, and it is made while preparing the table that answers a question. An outer join makes the decision readable, because the 69 empty rows are visible in the output and can be counted.

If the two sides are not reported separately, this distinction stays invisible. A single sentence such as “3,302 rows were joined” hides both the 169 dropped readings and the 69 dropped subscribers at once; yet the two items are independent of each other and arose from different causes. The first is a trace left by the previous step, that is, by cleaning: the readings of the 71 dropped records were left orphaned. The second is a gap in the data itself, and cleaning could not have seen it, because that a row is missing from the reading table becomes clear only when it is compared with the subscriber table. A join report therefore carries not one number but at least four: the input rows of both sides, the output rows, and the unmatched rows on both sides.

When the Grain of the Key Is Wrong

// grain.mjs — same key, wrong grain: a reading row carries a period, a visit row
// does not. If the two are joined on subscriber_id alone, nothing drops and nothing fans out.
import { SUBSCRIBER, READING, VISIT, join, gen } from "./join.mjs";

const id = (x) => x.subscriber_id;
const inner = join(READING, SUBSCRIBER, id, id).out.map((x) => x.left);
const visitCount = new Map();
for (const z of VISIT) visitCount.set(z.subscriber_id, (visitCount.get(z.subscriber_id) ?? 0) + 1);
const rv = join(inner, VISIT, id, id);
const dropped = inner.filter((o) => !visitCount.has(o.subscriber_id)).length;
const oneToOne = inner.filter((o) => visitCount.get(o.subscriber_id) === 1).length;
const fanned = inner.filter((o) => (visitCount.get(o.subscriber_id) ?? 0) > 1).length;

const y = gen([40, 10, 10]);
console.log(y(["reading x visit (on subscriber_id)", "reading", "rows"]));
console.log(y(["in", inner.length, inner.length]));
console.log(y(["no visit: dropped", dropped, 0]));
console.log(y(["one visit: kept one-to-one", oneToOne, oneToOne]));
console.log(y(["many visits: fanned out", fanned, rv.out.length - oneToOne]));
console.log(y(["out", "", rv.out.length]));

const T = (a, f) => a.reduce((x, o) => x + f(o), 0);
const beforeT = T(inner, (o) => o.usage_m3);
const afterT = T(rv.out, (x) => x.left.usage_m3);
console.log(`\ntotal usage was ${beforeT.toFixed(1)} m3, now ${afterT.toFixed(1)} m3; ` +
  `it went from ${inner.length} rows down to ${rv.out.length} rows but this is not a filter`);
console.log(`${dropped} readings were never counted, ${fanned} readings ` +
  `were counted ${rv.out.length - oneToOne} times`);

// Right grain: one period of one subscriber is one row. If the same pair appears twice,
// cleaning did not treat it as an exact match, because the two reading values differ.
const unique = new Map();
for (const o of inner) { const k = `${o.subscriber_id}|${o.period}`; if (!unique.has(k)) unique.set(k, o); }
const uniqueT = T([...unique.values()], (o) => o.usage_m3);
console.log(`\nsubscriber+period pair: ${inner.length} rows hold ${unique.size} unique pairs; ` +
  `${inner.length - unique.size} rows count the same period a second time`);
console.log(`total ${beforeT.toFixed(1)} m3 instead of ${uniqueT.toFixed(1)} m3; ` +
  `difference ${(beforeT - uniqueT).toFixed(1)} m3`);
console.log(`in 3471 readings -> out ${unique.size} rows: 169 unmatched, ` +
  `${inner.length - unique.size} duplicate pairs, 69 subscribers never shown`);

reading x visit (on subscriber_id)         reading      rows
in                                            3302      3302
no visit: dropped                             1901         0
one visit: kept one-to-one                     913       913
many visits: fanned out                        488      1134
out                                                     2047

total usage was 65185.6 m3, now 40374.1 m3; it went from 3302 rows down to 2047 rows but this is not a filter
1901 readings were never counted, 488 readings were counted 1134 times

subscriber+period pair: 3302 rows hold 3199 unique pairs; 103 rows count the same period a second time
total 65185.6 m3 instead of 63060.6 m3; difference 2124.9 m3
in 3471 readings -> out 3199 rows: 169 unmatched, 103 duplicate pairs, 69 subscribers never shown

This table is the real finding of the lesson. The reading table carries a subscriber and a period, while the visit table carries only a subscriber. If the two are joined on subscriber_id alone, the grain of the key does not match: three readings of a subscriber produce six rows with that subscriber’s two visits.

The resulting row count is lower than the input, from 3,302 to 2,047, and looked at by itself this resembles a filter. It is not. Two opposing things happened within the same operation: the 1,901 readings of subscribers with no visit at all dropped, and the 488 readings of subscribers with several visits fanned out into 1,134 rows. Because the net change is negative, the fan-out is invisible in the count. Total usage falls from 65,185.6 m³ to 40,374.1 m³; this is a number that could be read as “usage went down”, and not a single reading has changed.

The second measurement shows that grain must be asked about even with the right key. The 3,302 rows the inner join returned contain only 3,199 unique subscriber and period pairs; 103 rows count the same period a second time. These did not fall under cleaning’s exact-match rule: in 101 of the 103 pairs the usage values of the two records differ from each other, and in the remaining two the row numbers differ. The effect on the total is 2,124.9 m³, that is, more than 3% of the total.

The closing account of the lesson is on the last line: 3,471 readings went in, 3,199 rows came out; 169 records did not match, 103 records were dropped because they counted the same period a second time, and 69 subscribers never appeared in the output. When all three items are written down, a join is a preparation step; when they are not, it is merely a change.

Summary

• A join places rows side by side on a key and adds columns; a union stacks rows one under the other. In a join the row count changes according to the number of matches on the key.
• The inner join outputs 3,302 rows from 3,471 readings and 1,329 subscribers: 169 readings did not match and 69 subscribers never appeared in the output. The outer join keeps those 69 subscribers and returns 3,371 rows.
• The silent dropping of the 69 subscribers that were never read produces no error, and the question “average usage” is answered without those 69 subscribers.
• When the grain of the key does not match, one join drops and fans out at the same time: 1,901 readings are never counted, 488 readings are counted 1,134 times, and because the output has fewer rows than the input the fan-out stays invisible.
• Even with the right key, grain must be asked about: the 3,302 rows hold 3,199 unique subscriber and period pairs, and the 103 repeats inflate the total by 2,124.9 m³.

Next Step

The tables are now side by side, and what we hold is a single dataset in which one row carries one subscriber’s reading for one period. This shape does not suit every question. The question “the change between a subscriber’s three periods” requires comparing three rows, yet the same information can sit in one row in a shape that turns each period into a column. Moving back and forth between the two shapes looks like a purely formal operation, but information can be lost in one direction, and the loss is noticed only on the way back. The next lesson defines these two shapes and measures whether the conversion is lossless, that is, whether the original dataset is reached again when we convert back, by counting.