Joins: Combining Two Datasets Without Corrupting Either

A join sets two tables side by side wherever a shared column matches. Join the 32 Neelbagh return rows to the day book on the stall alone and 50 rows come out, with the takings total overstated by Rs 11,66,200/-. Join on the stall and the month instead and 13 come out. Nothing reports an error either time, so count the rows yourself.

Something is missing from both of those joins. No warning appeared. No column turned red. No script stopped early. Both joins ran cleanly, returned a wider table than the one that went in, and handed back something that looked exactly like the sort of thing being hoped for. The damage a join does is invisible in the table it produces and visible only in a count that has to be taken deliberately.

Here is the everyday shape of it, before any of the rupees. Twelve caterers work out of one wedding hall, and every month each of them sends the hall office a single billing sheet. The hall office also keeps a running notebook for anything worth remembering: a kitchen closed for repair, a room lent out for an afternoon, a bill that arrived twice. Now somebody asks for the billing and the notebook on one page. The clerk pairs each billing sheet with whatever the notebook holds under that caterer's name. A caterer with three entries in the notebook has its billing sheet written out three times, and its month of billing is now lying in the pile in triplicate. Nobody typed a wrong number. Nobody double counted on purpose. Copying a row copies the money on it, so the total simply grew.

What is already on the record, and what is added to it?

Three pieces of groundwork arrive already done. The first is the Neelbagh market itself, an invented covered market whose office keeps one row per stall per month over four months, and the file it hands over holding 32 rows and eight columns with all the faults it has always had. The second is the named conventionA counting rule written down before the work starts. A second person repeating it lands on the same figure rather than on some other perfectly defensible number. for the average takings per stall monthOne stall in one particular month, treated as a single thing to be counted. The unit the market office files by, so it is the unit anything joined to that file has to line up with.. The convention totals every takings cell that carries a figure worth using, then divides by the count of those cells and by nothing else. A box left blank holds no figure. Neither does the office codeA value a record keeper puts into a number column to carry a message rather than a quantity, so a reader who takes it literally adds the message to a total. the market writes when a return never arrived. A true zero, though, is a figure and goes in. The third piece is the discipline of noting how many rows there were before an operation and how many there are after. Nothing so far has leaned on that count as heavily as a join does.

The market office also keeps a day book: a running log of twelve notes, one row per note, carrying the stall, the stall name as the office wrote it, the month, the day it was written down and the wording of the note. Neither table changes. What changes is what happens when somebody puts them side by side. Both tables are correct. Both were kept properly. The trouble is manufactured entirely at the moment of joining, by a decision that takes half a second and is almost never written down.

Both tables are printed below in full. Every count below was worked out from these rows, and any of them can be redone with a pencil.

What does a join actually do, and what is the only question worth asking?

A join takes each row on the left, looks along the right hand table for every row whose shared column matches it, and writes out one output row for each match it finds. The search and the copy are the whole operation. Everything difficult about joining hides in the words "for each match it finds". One match produces one row. Two matches produce two rows. No matches produce nothing at all, unless the join has been asked to keep the left row anyway.

The output row count is not the input row count and was never going to be. A join is not a merge of two things into one thing of the same size. A join is a search, run once per left row, and its result is however many hits that search returned. So the only question worth asking first, before a single figure in the result is looked at, is how many rows went in and how many came out. Every other question about a join is a refinement of that one.

A second thing costs nothing to fix in place now and saves an argument later. The order the output rows come back in is a fact about how the join was carried out, not a fact about either table. If a stall appears first in the result it is because that is where the search happened to reach it. Nothing may be read off row orderWhere a row happens to land in the sequence. The position records how the table was put together and carries no meaning at all about the thing the row describes. in a joined table, not a ranking, not a recency, not a first and last. This matters here because a joined table often looks sorted, and a table that looks sorted invites people to read meaning into the top of it.

What does one row of each table describe?

Stating it as a full sentence for each table is the single habit that prevents everything below. One row of the returns table describes one stall in one month. A row of the day book describes a single note and nothing else. The two sentences are not the same shape, and every difficulty below comes from the difference.

Work through the counts. The returns table holds 32 rows over four months. The day book holds 12 notes. The 12 notes cover eight stalls, and they cover 11 stall months. Twelve notes across 11 stall months means one stall month carries two notes, and it does: NB-03 in month 2, one note written on day 5 and one on day 19. Those three counts are the entire arithmetic of the day book, and they are enough to predict most of what follows.

Neither of those facts is a fault in either table. Two things happened to NB-03 in month 2: a return was filed and then it was revised. The market office was right to write two notes. A log is a log, so the office was equally right to keep the day book at one row per note rather than one row per stall month. Both tables are correct records of what they record. GrainWhat one row of a table describes, stated as a sentence rather than assumed. Two tables can share every column name and still be counting different things. is not a quality problem to be fixed; it is a property to be known before it is acted on.

Where does the damage start, if it is not the join?

Set the two grain sentences beside each other and the rule falls out on its own. If the shared column is unique on the right hand table, then every left row finds at most one match, every left row is written out at most once, and the count survives the operation. If the shared column is not unique on the right, then a left row matching twice is written out twice, a left row matching three times is written out three times, and the money on that row is copied along with it.

Take NB-08 month 1, a plain row with Rs 42,000/- in it and nothing unusual about it at all. Join on the stall alone. The day book holds two notes for NB-08, one about month 3 and one about month 4. Neither has anything to do with month 1. The join does not know that and was never asked to know it: the shared column said NB-08, both notes say NB-08, so the month 1 return comes out twice. Rs 42,000/- is now sitting in the result as Rs 84,000/-, and every rupee of the second one was manufactured by the copy.

So where did the damage start? Not in the join. It did precisely what it was told, and it reported success because it succeeded. Not in either table, both of which are faithful records. The damage started at the moment somebody chose a column to join on without first writing down what one row of the right hand table describes. The choice took no time, produced no artefact, and left no trace in the output. Choosing a join column is the cheapest mistake to make and the most expensive one to find.

How much does the wrong column actually cost, in rupees?

Stop treating this as a caution and price it. Join all 32 return rows to the day book on the stall alone, keeping every match. Thirty two rows go in and 50 rows come out. The count of rows is the first number, and it is free. Anybody could have had it before computing anything at all.

Now the money. The one empty box on the record has been copied twice along with everything else, so of those 50 output rows, 48 carry a usable takings figure. The takings total across them reads Rs 33,06,299/- against the Rs 21,40,099/- that went in. The overstatement is Rs 11,66,200/-, or 54.4928 per cent of the true total. A report of the month's takings built on that table is not slightly wrong. The report is more than half as big again as the record it claims to describe.

Here is the part that catches people. Apply the named convention to the same corrupted table. The average takings per stall month comes out at Rs 68,881.23/- against a true Rs 69,035.45/-. The average has moved down by Rs 154.22/-, or 0.2234 per cent. Read those two figures together: the same single operation, on the same table, at the same moment, overstates the sum by 54.4928 per cent and moves the average by roughly a fifth of one per cent in the other direction.

Why does that happen? Because a sum accumulates every duplicate while an average divides by a denominatorThe count sitting underneath an average. Change the count and the same total starts answering a different question. The count belongs in the same sentence as any figure it produced. that grew at almost the same rate. Duplicating rows inflates the top and the bottom of the fraction together, so the ratio barely stirs. A sum breaks loudly and an average hides the same damage. A report built on averages can run corrupted for months without ever looking wrong.

Can one join drop rows and manufacture rows at the same time?

A join can do both, and this one does. Join the 32 return rows to the day book on the stall and the month together, keeping only the rows that match. Thirty two rows go in and 13 come out. The net is 19 rows fewer, and that net is the only number anything will show.

The net is a lie. Break it open. Of the 32 return rows, 11 found a note for that exact stall month and 21 found none at all, so 21 rows were dropped outright, taking their takings with them. Meanwhile NB-03 month 2 sits in the returns table as two rows, one for Rs 36,400/- filed on day 5 and one for Rs 39,700/- filed on day 19, and that same stall month carries two notes in the day book. Two returns meeting two notes produce four output rows where two rows went in. Two of those four were manufactured by the join.

So the true account is 21 rows dropped and 2 rows manufactured, and 21 less 2 is 19. No part of the system reports either the 21 or the 2, and the net figure of 19 reports neither of them. Worse, 19 is a plausible looking number. Somebody who counted before and after, saw 32 become 13, and reasoned that 19 rows had no matching note would be wrong about 19 separate rows and would have no way of discovering it from anything on the screen. A net row count that happens to look explainable is the most dangerous reading of all.

What happens when the join uses the readable column instead?

Every table here carries a stall name as well as a stall code, and the name is the pleasant one to work with. NB-03 means nothing to anybody; Harit Greens is a shop that can be pictured. So joining on the name feels natural, and it is the mistake this record was built to punish.

The market office wrote the stall name into the returns table on every row it appears in, four separate typings for a stall that traded all four months. On NB-03 it typed Harit Greens in months 1 and 2 and Harit Green, without the final letter, in months 3 and 4. The day book always wrote the full spelling. So the returns table carries nine distinct names for eight stalls and the day book carries eight, and that gap of one is a spelling rather than a stall.

Join the two tables on the name and those two short spelled rows match nothing. NB-03 month 3 worth Rs 41,000/- and NB-03 month 4 worth Rs 40,000/-, Rs 81,000/- between them, disappear from the result. Neither row is damaged or doubtful. Both are real trading months of a stall that traded every month of the record, and both are simply not in the answer any more. Nothing is reported. From the join's point of view nothing went wrong. It searched for Harit Green, found no such thing, and moved on.

The name column looked like the obvious one to use precisely because it was readable, and readable means retyped, and retyped means eventually inconsistent. A stall code is chosen as the joining column for the opposite reason. An identifierA label that exists purely to point at one thing. An identifier has no size, stands for no quantity, and gives nobody a reason to reword it. is dull on purpose, is copied rather than composed, and carries no version anybody feels tempted to improve.

Is a left join a safe join?

Most readers arrive having been told to keep every left row and stop worrying. Keeping every left row does solve exactly one problem: a left row that finds no match is written out once, with the right hand columns left empty, instead of vanishing. Keeping every left row is genuinely useful, and it turned 21 dropped rows into 21 kept rows in the panel above. A left row that finds three matches is still written out three times, so keeping every left row does nothing about multiplication.

Here is the ladder. The same 32 rows, keeping every left row every time, through the five keys:

Only the last setting returns what it was given. Four of the five hand back a table larger than the record, and a reader who was told that keeping every left row makes a join safe has four different corrupted tables to choose from.

Now the trap inside the trap, and it is the reason a row count on its own is not enough. Look at the third and fourth rows of that ladder. Both return 34 rows. The two results are not the same 34. Joining on the stall and the month attaches a note to 13 of those rows. Joining on the stall name and the month attaches a note to 12. The row that lost its note is NB-03 month 3: the day book wrote a note for that exact stall month, the note is sitting in the table above, and the join on the name silently returned that row with an empty note because the returns table spells the stall short in month 3.

The row count caught nothing there, inside the very example used here to argue for row counts. Row counting has an honest limit, and this is it. So the count of rows carrying a match is the second check, and it costs one more line: rows in, rows out, and of the rows out, how many actually found something on the right.

What is the fix, and how long does it take?

One line, and a decision somebody has to make. The trouble was never the join; it was that the right hand table had a grain the join was not told about. So the grain has to be supplied. Exactly one stall month held two notes. Collapsing the day book to one row per stall month before joining anything, keeping the later note by its day, takes the day book from 12 rows to 11.

Then join on the stall and the month, keeping every left row. Thirty two rows go in and 32 rows come out. The takings total is Rs 21,40,099/- going in and Rs 21,40,099/- coming out. The average takings per stall month is Rs 69,035.45/- going in and Rs 69,035.45/- coming out. The record comes back unchanged, with a note attached to the 11 stall months that have one and an empty note on the 21 that do not.

The fix is not a technique but a decision. Somebody has to say which of NB-03 month 2's two notes was meant, and no join anywhere can say it for them. The day 19 note is the one recording a revision, and a first figure withdrawn, so keeping the later note by its day is a defensible answer. Keeping the earlier one would be a different answer and would need a different reason. Keeping both is what was happening by accident, and it is the only one of the three that nobody chose.

# the two tables, exactly as printed above, held as plain rows
returns = [("NB-03", 2, "Harit Greens", 36400, 5),
           ("NB-03", 2, "Harit Greens", 39700, 19)]   # and 30 more, 32 rows
daybook = [("NB-03", 2, 5, "return filed"),
           ("NB-03", 2, 19, "return revised")]      # and 10 more, 12 notes

# the count before, which costs nothing and is the whole method
print(len(returns), len(daybook))          # 32 12

# the join everybody writes first, on the column both tables obviously share
wide = left_join(returns, daybook, on=["stall_id"])
print(len(wide))                            # 50
print(total_takings(wide))                  # 3306299, against 2140099

The count before and the count after, around the join on the stall alone. Every row it runs on is printed in the two tables further up, and the two printed counts are the only warning this operation will ever give.

# give the right hand table the grain the join needs: one row per stall month
collapsed = keep_latest_by_day(daybook, on=["stall_id", "month"])
print(len(collapsed))                       # 11, down from 12

# now the same join, on the stall and the month, keeping every left row
fixed = left_join(returns, collapsed, on=["stall_id", "month"])
print(len(returns), len(fixed))            # 32 32
print(total_takings(fixed))                 # 2140099, exactly what went in
print(matched_rows(fixed))                  # 11 rows carry a note, 21 carry none

The fix, and the second check beside it. Collapsing the day book to one row per stall month is the line that decides which note was meant, and printing the count of rows that carry a note is what separates this key from the one that also returns 34.

What gets written down before anything is computed from a joined table?

A lender pulling a borrower's monthly sales alongside a log of missed instalments, an analyst putting a price history beside a table of corporate events, a shopkeeper putting last year's stock sheet next to this year's, and a household laying its bank statement beside its own list of what each payment was for: all four are doing the same operation on two tables with different grains, and all four can silently double a figure they care about. Four lines protect every one of them, and the four lines cost nothing.

1. The rows going in. Thirty two. Written before the join runs, not after, because afterwards it has to be reconstructed from a table that has already changed.
2. What one row of the right hand table describes, as a sentence. One row describes one note. When that sentence is not the same shape as the left hand one, the count will move, and the reason it moves is already apparent.
3. The rows coming out, and of those, how many found something on the right. Two numbers, not one. Thirty four and 13 is a different result from 34 and 12, and only the second number can tell them apart.
4. One sentence saying why the counts differ, written before any arithmetic. On this record: 21 rows found no note and were kept with an empty one, and 2 rows were added where one stall month carried two notes.

A total computed after an unchecked join is not slightly uncertain. It is a figure with no known denominator, and nobody afterwards can say how many things it was a total of. The named convention that made the average defensible in the first place quietly stopped being true the moment the row count changed, and nothing on the screen marked the moment it stopped.