A match of two tables on their key columns returns zero rows, though the same codes print on both sides. Why?
answer
- printing is not comparing
- invisible differences beat the eye
- case, padding, a lost zero
- one column, one representation
- count the unshared key values
basics
~20 sEquality compares stored values, not the rendering. Two codes that print alike can differ by letter case, leading or trailing spaces, an invisible character, a lost leading zero, or one side holding digits as text and the other as numbers.
solid answer
~50 sA key match pairs rows whose key columns compare equal, and equality is decided on the stored value; the screen shows a rendering of that value, which is a lossy, separate step. So codes that look identical can differ in letter case, in padding spaces at either end, in an invisible character riding inside the value, in a leading zero one side no longer has, or in the representation itself - digits held as text against digits held as numbers. Zero matched rows is actually the friendly form of this failure, because nothing downstream looks plausible. The first move is not to read rows: take the distinct key values from each side, count how many each side holds that the other does not, and then look at the character length and the character codes of a few of them.
go deeper
Recall that a comparison sees the stored value while you see a rendering of it, and be able to name three invisible differences: letter case, padding spaces, and a leading zero that is gone.
Explain why each cause survives a visual check, and why one side holding digits as text against another holding numbers can pair nothing at all without raising anything.
Show the habit rather than the list: before believing any matched table, compare the two key value sets and quantify what failed to pair, because the partial version of this defect ships.
Weigh what it costs to settle one key format once at the seam between two producers against letting every downstream consumer defend itself, and decide which of the two your team can actually sustain.
## What the comparison actually looks at Matching two tables on a key column pairs rows whose named key columns **compare equal**. Equality is decided on the value the tool has stored. What you read on screen is a *rendering* of that value: a separate step that fits it into a cell of a chosen width, and one that is lossy by design, because it shows nothing for anything that occupies no ink and it may pad or clip what it does show. Every failure in this family lives in the gap between the stored value and its rendering. The eye confirms a pairing that the comparison refuses, and the candidate who says "I checked, they are identical" has checked the wrong artefact. The practical consequence is that the investigation must never begin by reading rows on screen. ## The differences that do not print | What actually differs | Why the screen hides it | Where it usually comes from | |---|---|---| | **Letter case** | the eye reads a letter; the comparison reads its character code | one producer stores codes as typed, another raises them to a single case | | **Leading or trailing spaces** | a space at the edge of a cell has no visible boundary | a fixed-width source, a spreadsheet round trip, a hand-edited cell | | **An invisible character inside the value** | a zero-width mark or a non-breaking space takes no ink | text copied out of a document, a page or a message | | **A leading zero that is gone** | `00742` and `742` are equal as numbers, not as codes | a column that stopped being held as text somewhere upstream | | **The representation of the column** | both sides render as digits | one column holds text, the other holds numbers | | **A look-alike letter from another alphabet** | the two glyphs are drawn identically | values typed or pasted from mixed sources | Those six split into two failure shapes, and the split matters more than the list: - **A within-value difference** - case, padding, an invisible character, a look-alike letter - hits *some* of the values. The match does not return nothing; it returns whichever subset happened to be clean, which is the dangerous form. - **A whole-column difference** - one side holding digits as text while the other holds numbers, or one side having lost its leading zeros - hits *every* value at once. That is the form that tends to return nothing. ## Zero matched rows is the outcome you want An empty result is the friendly member of this family. Every total downstream is zero, every chart is blank, and somebody notices on the first run. The same defect in a milder form returns a table of the right shape carrying a plausible number, and a plausible number is signed off. So the correct reaction to an empty result is relief: this failure announced itself, which is not something you can count on. ## Working out which one it is 1. **Read the representation of each key column.** A column carries one representation for all of its rows, so this is a single fact to read rather than a per-value check. If the two sides differ here, stop - you have found it. 2. **Compare the two sets of key values.** Count the distinct values on each side, how many appear on both, and how many each side holds that the other does not. Those counts separate "nothing paired" from "most paired" immediately, and they are computed from the values alone. 3. **Take about ten of the unshared values and look at what they are made of** - the number of characters and the character code of each one, never the rendering. A code that should be five characters and is six is carrying padding or a passenger; two codes of equal length differing in one character code are a case or look-alike difference. 4. **Only then decide what to do about it.** The repair belongs to whatever step produced the column; the skill being tested here is seeing the problem, not cleaning it. ## What varies between tools, and what does not The arithmetic does not vary: a value either compares equal or it does not, and a key with no partner on the other side contributes no pair anywhere. What varies is whether anything tells you. - Comparing a text key column against a numeric one **is refused at the call on some designs and quietly matches nothing on others**; a third behaviour is to convert one side and compare the converted values, which can succeed and can also alter what the value meant. Do not plan on being told. - Whether the result shows which rows failed to pair is a property of the tool. Some can add a column saying which side each output row came from; others offer nothing, and where there is nothing you build the same information from a membership test against the other side's key values. - How values are displayed - padded, clipped, aligned differently by representation - belongs to the display, not to the data, so a tell you learned in one environment does not travel to another. The one instrument that travels everywhere is the comparison of the two key value sets, because it uses nothing but the values themselves.
- Both key columns are text, both are upper case, neither is padded, and the match still finds nothing. What is left?Something inside the value that the rendering cannot show: an invisible character such as a zero-width mark, a look-alike letter drawn like a Latin one, or a prefix one producer adds. Compare character lengths first - two codes that should be equal but differ in length settle it in one step - then compare the character codes position by position.
- How do you tell a padding difference from a representation difference without inspecting every value?They live at different levels. A representation difference is a property of the whole column, so you read it once and it answers for every row. Padding is a property of individual values, so you sample: take a few key values from each side and compare their character lengths against the length the code is supposed to have.
Two parcels for the same person: one label reads J. SMITH, the other reads J. Smith with a space after it. A human sorter delivers both; a machine keying on the exact label text sends one to the unknown-address bin. If only some labels carry that space, most parcels arrive and a steady trickle quietly does not.
saying these in an interview costs you the question
- Says the codes match because they look the same on screen.
- Assumes a mismatched key representation always raises an error.
- Re-runs with a different match shape and calls the problem fixed.
- Thinks zero matched rows must mean one input was empty.
- Rules out padding because the data came from a machine, not a person.
- Reads rows by eye instead of comparing the two key value sets.