CMCE10002 Chap.9 Joining Tables on a Shared Key
Joining Tables on a Shared Key
The join type is a decision about unmatched keys
Two tables can be combined when they share a column, or set of columns, identifying what a row represents. The week 5 lecture defines that key by meaning: it tells you whether a row is one firm or one firm and year.
Every join keeps the matched keys, so the four types differ only in what happens to keys present in one table and not the other.
Two of the four care about order
Keeping only the matches, or keeping everything, gives the same rows whichever table is named first.
Keeping all of one table, or looking for the rows of one table that found no partner, are defined relative to a particular table, so swapping the inputs asks a different question.
Grain decides whether the join means anything
Combining two tables assumes they describe things at the same level of detail.
When they do not, the join still runs, the product row repeats once per period, and any total taken afterwards counts the same value many times.
Reporting coverage as part of the result
When a join leaves a column partly empty, that fact belongs in the write-up with a number attached: how many rows found no partner, and what fraction of the table that is.
A report giving an average computed over the matched rows without mentioning the unmatched ones describes a subset while appearing to describe the whole. Running the diagnostic join first turns that into something you know in advance rather than something discovered from a table full of blanks.
What this chapter covers
- 01
The key as a statement about what a row represents
- 02
Matched keys, left-only keys and right-only keys as three regions
- 03
What each of the four joins retains, and which columns travel with it
- 04
Why order matters for two of the joins and not the other two
- 05
Matching grain before the join, and predicting the row count after it
Preparing a review table to meet a weekly sales table
- 1State why the two tables cannot be joined as they stand.
- 1Give the preparation step and the grain it produces.
- 1Choose the join and justify the direction.
- 1State the check on the joined table.
Key terms
- Join key
- The column or set of columns shared by two tables that identifies what a row represents in both.
- Left join
- A join that retains every row on its first side, attaching columns from the second wherever a key matched.
- Inner join
- A join that retains only those keys present on both sides.
- Anti join
- A join that retains the first side's unmatched rows alone, carrying nothing across from the second.
- Full join
- A join that retains everything from either side, leaving blanks wherever a key appeared on one side only.
- Fan out
- The multiplication of rows that happens when a key is not unique on both sides of a join.
Joining Tables on a Shared Key FAQ
Which join keeps every row of the first table?
The left join retains every row on its first side and brings columns across from the second wherever a key matched. Rows that found no partner survive with empty cells in the attached columns, which is the join working correctly rather than an error.
Why did a joined table come back larger than either input?
Because the key is not unique in at least one of the tables, so rows on one side matched several rows on the other and the result multiplied. Counting the rows per key in each input before joining identifies which side is responsible.
How do you find rows that have no match at all?
Use the join that keeps only the unmatched rows of the first table. It brings no columns across from the second, because its purpose is diagnostic: it reports coverage before a real join turns the same fact into a column full of blanks.
Exam move
Predict the row count before every join and compare it afterwards. More rows than either input means the key was not unique, fewer rows than the first input means the wrong join type was used, and the same rows with blanks is a coverage finding.
Working through Joining Tables on a Shared Key in CMCE10002? Sia is AskSia’s AI Statistics tutor — ask any CMCE10002 Joining Tables on a Shared Key question and get a clear, step-by-step explanation grounded in how CMCE10002 is taught and assessed. Read this chapter free, then take your hardest questions to Sia.
