Why Merge Design Matters
A laboratory file, a clinical file, and a dosing file can describe the same participants without containing the same kinds of records. One clinical row might describe a subject, while the laboratory file has one row per sample and the dosing file has one row per administration. Combining these files is not just a matter of finding columns with similar names. The join must preserve the intended records and make clear which records correspond.
The central design question is: what does one row represent in each source, and which fields uniquely identify that row for this merge? Subject identifiers are often necessary, but a subject identifier alone cannot distinguish several samples or events from the same subject. Depending on the file grain, additional keys may include an occasion, visit, sample identifier, or a defined time point. Time should be included only when the sources use a compatible, explicitly defined time representation; two time fields that merely look similar are not automatically equivalent.
Before joining, state the row meaning and key for each file. For example, a clinical covariate file may have one row per subject, whereas a sample file may have one row per collected sample. Joining those files by subject can be a deliberate one-to-many operation: each sample inherits the subject-level information. It is not the same operation as joining two event-level files by subject alone.
Join Semantics and Row Counts
An inner join retains only matching rows from both sources. A left join retains every row from the left source and adds matching values from the right source; when there is no match, the right-side fields are missing in the result. These definitions concern rows, not just distinct key values: if a key occurs more than once on either side, every matching combination may appear.
For the formulas below, let \(n_A(k)\) and \(n_B(k)\) be the numbers of rows in sources A and B with the same fully nonmissing key value or key tuple \(k\). Assume ordinary equality matching, as in a typical SQL join: a missing key component does not equal another missing component. Rows with any missing key component are therefore treated as unmatched, not as a shared key. Let \(K_A\) and \(K_B\) be the sets of distinct nonmissing keys in A and B, and let \(m_A\) be the number of A rows with at least one missing key component. Then:
The product in the inner-join formula counts all row pairs with the same nonmissing key. In a left join, each A row with a key absent from B still contributes one output row, as does each A row with a missing key; when a key occurs several times in B, each matching combination contributes a row. Thus an unexpected increase in row count can be evidence of repeated keys, not evidence that more source records were recovered.
Plan the Merge Before Running It
Write down whether a row represents a subject, visit, sample, dose event, or another defined unit. Identify columns that distinguish records at that grain.
Name every key field, its meaning, units or format where relevant, and whether matching is exact. Do not assume that a subject-only key is sufficient for event-level records.
Count missing key components, repeated keys, and keys present on only one side. Confirm that the observed relationship matches the intended one-to-one, one-to-many, or many-to-one merge.
Compare output counts with the expected join behavior. Inspect unmatched records and repeated matches, and retain enough source identifiers to trace resulting rows back to their origins.
Worked Example: Attaching a Subject-Level Clinical File
Suppose a hypothetical clinical file has one row per subject, and a laboratory file has one row per subject-level baseline result. The intended key is the nonmissing subject identifier, and the expected relationship is at most one row per subject in each source.
| Clinical subject ID | Age (years) |
|---|---|
| S01 | 34 |
| S02 | 52 |
| S03 | 41 |
| S04 | 29 |
| Laboratory subject ID | Baseline result (units) |
|---|---|
| S01 | 6.2 |
| S03 | 5.8 |
| S04 | 7.1 |
An inner join on subject ID returns three rows: S01, S03, and S04. S02 is absent because it has no matching laboratory row. A left join with the clinical file on the left returns four rows. S02 remains, with its laboratory result missing. There are no repeated or missing keys, so the formulas give \(N_{\mathrm{inner}}=1+1+1=3\) and \(N_{\mathrm{left}}=1+1+1+1=4\).
The left join is appropriate if the goal is to retain every clinical subject and attach available laboratory data. It does not establish why S02 lacks a result. That absence is an unmatched record to investigate, not a value to replace with zero.
Time Keys and the Grain of Event-Level Files
When laboratory and dosing sources contain multiple records per subject, a subject identifier alone usually identifies a group of records rather than a unique record. A more specific key may include a defined occasion and time point, but only if those fields refer to the same concept in both sources. For instance, a nominal visit label is not necessarily interchangeable with an actual sample time, and a timestamp is not necessarily interchangeable with elapsed time from a reference event.
A key should not be made to look unique by adding a convenient column without checking what it means. Conversely, if the intended task is to attach subject-level information to every sample, repeated subject identifiers in the sample file are expected: the intended relationship is one clinical row to many sample rows. The resulting number of rows should match the sample file's count for a left join from samples when the clinical key is unique.
The next example illustrates why separate event-level files should not be joined merely because they share a subject. It is a cardinality diagnostic, not a rule that laboratory and dosing events should be paired by their sequence or nearest time. Any event-level matching requires a scientifically specified key and matching rule.
Worked Example: Detecting an Unsafe Subject-Only Join
In a hypothetical study, the laboratory source has two sample rows for subject S07, at defined times 2 and 8 hours. The dosing source has three administration rows for S07, at times 0, 6, and 12 hours. Each source has one row per event, and neither source has another subject.
If both files are joined by subject ID alone, then \(n_A(\text{S07})=2\) and \(n_B(\text{S07})=3\). An inner join produces
Those six rows are all sample-administration combinations for S07, not six independently observed sample-dose matches. The join has multiplied the event records because subject ID does not identify a shared event. Adding exact time as a key would retain only exact shared times, but here none of the sample times (2 and 8 hours) equals a dosing time (0, 6, and 12 hours); that exact-key inner join would return zero rows. This result would not mean the files are empty or that no dose could be relevant to a sample. It means exact time equality does not provide matches in these data.
The correction is to define the intended data structure first. If the goal is to preserve sample records and attach subject-level covariates, join samples to a source with one row per subject. If the goal is to relate sample records to dosing history, use a scientifically justified event-time procedure rather than an unexamined subject-only join. Do not treat the six Cartesian combinations as valid pairings.
Identifier Consistency and Unmatched Rows
A logically correct key can fail operationally when identifiers are represented differently. Leading zeros, surrounding spaces, inconsistent capitalization, accidental punctuation, and different data types can all prevent intended exact matches. Converting identifiers casually can also create false matches: converting “0012” and “12” to the same number is safe only if the source specifications establish that leading zeros are not meaningful and the conversion cannot collapse distinct IDs.
Use an explicit, documented normalization rule or an authoritative crosswalk when the source definitions support it. Preserve the original identifier alongside any normalized field so that a match can be traced and reviewed. Avoid fuzzy matching as a silent default; similar strings can refer to different subjects. Report unmatched keys on both sides, since a left join alone does not show records found only in the right-hand source.
Worked Example: Resolving a Formatting Mismatch
A hypothetical clinical file contains subject ID “0012”; a laboratory file contains “12”. The initial exact join yields no match. Before changing the data, the team checks the source specifications and an approved subject crosswalk. They confirm that both strings represent the same subject and that the two-digit identifier is the canonical form.
The documented rule is to trim surrounding spaces and left-pad numeric subject IDs to four characters, while retaining the raw ID. Applying it gives clinical “0012” and laboratory “0012”. For this key, \(n_A(\text{0012})=1\) and \(n_B(\text{0012})=1\), so the inner join contributes \(1\times1=1\) row, and the left join contributes \(1\times\max(1,1)=1\) row for that clinical record.
This resolution is justified by the crosswalk and identifier specification, not by the appearance of the strings alone. If the crosswalk had not confirmed equivalence, the correct action would be to flag the mismatch for investigation rather than force a match. A conversion that maps distinct canonical IDs to the same value could produce a false join even while reducing the unmatched count.
Diagnostics, Alternatives, and Limitations
Useful diagnostics compare the intended relationship with what the data actually contain. Check key uniqueness in each source where uniqueness is expected; count rows with any missing key component; list distinct keys present on only one side; and compare row counts before and after each join. For a many-to-one attachment, a left join from the many-row source should preserve that source's row count if the one-row source is truly unique on the key and the join implementation retains unmatched left rows. If the output grows, inspect repeated keys on the right. If it shrinks, check whether an inner join was used or whether the left source was not the one intended.
A join is different from appending (stacking) records. Join when sources describe corresponding entities or records and their fields can be brought together using defined keys. Append when sources contain the same kind of record in separate batches or periods and should become additional rows, with compatible variable definitions. Choosing the wrong operation can either multiply records or put unrelated records side by side.
A join also does not resolve scientific discrepancies or prove that two similar measurements refer to the same collection event. Exact-key joins can miss records when time formats differ; broad keys can create ambiguous combinations; and a successful match can still be based on an incorrect identifier mapping. Time tolerances, event attribution, and reconciling conflicting source values require explicit rules rather than an automatic consequence of merging.
- Common mistake: “Both files contain subject S07, so every S07 row should be matched.” Correction: subject ID groups records; it does not necessarily identify a corresponding sample or dose event. Check each file's grain and the key's cardinality.
- Common mistake: “The unmatched count fell after converting IDs, so the merge improved.” Correction: fewer unmatched rows are not proof of correct matching. Verify the conversion against source definitions or a crosswalk, and check that distinct IDs were not collapsed.
- Common mistake: “A missing key in each file should match the other missing key.” Correction: under ordinary equality semantics, missing key components do not match. Treat those rows as unmatched unless a specifically defined null-safe matching rule is intended.
In reproducible preparation, record the source files, key fields, normalization rules, intended relationship, join type, and before-and-after counts. These details make it possible to explain why rows were retained, unmatched, or multiplied, and to repeat the merge consistently.
Check Your Understanding
Answer each question, then compare with the solutions below.
- Source A has two rows for nonmissing key K, and source B has three rows for K. How many rows does an ordinary inner join produce for K?
- Source A has one row with a missing subject ID, and source B has one row with a missing subject ID. Under ordinary equality matching, how many matched pairs do these rows produce?
- A sample file has 15 rows and a clinical file has exactly one row per subject. What should the row count be after a left join from samples, assuming no other row-changing operation?
- Two event-level files each contain several rows per subject. What does a subject-only many-to-many join imply about the resulting combinations?
- What evidence should support normalizing “0012” and “12” to the same identifier?
Solutions. (1) \(2\times3=6\) rows, because every A row matches every B row with K. (2) Zero matched pairs; the missing keys are unmatched under ordinary equality semantics. (3) Fifteen rows, provided the clinical key is unique; each sample row gets one clinical match or missing clinical fields. (4) It creates combinations among the rows sharing each subject, potentially multiplying records without establishing event correspondence. The key must be refined or another justified data operation selected. (5) An authoritative source specification or crosswalk confirming equivalence, plus a documented rule that does not collapse distinct identifiers; retain the raw values for traceability.