20  Another table

The sale knows its product, so who knows the maker? Another table does. join adds that table’s columns to this one, matching rows by the columns that say which rows correspond. products holds a maker for each product it lists.

run('
  sales
    then join products by [product]
    then pick [product, maker, revenue]
')
product maker revenue
Widget Acme 100
Gadget Globex 120
Doohickey Initech 120
Widget Acme 150
Widget Acme 200
Gadget Globex 300
Doohickey Initech 80
Doohickey Initech 200
Widget Acme 50
Gadget Globex 240
Widget Acme 75
Gadget Globex 60
Widget Acme 125
Sprocket NA 150
Sprocket NA 90 
sales |> join(products, by = product) |> pick(product, maker, revenue)
product maker revenue
Widget Acme 100
Gadget Globex 120
Doohickey Initech 120
Widget Acme 150
Widget Acme 200
Gadget Globex 300
Doohickey Initech 80
Doohickey Initech 200
Widget Acme 50
Gadget Globex 240
Widget Acme 75
Gadget Globex 60
Widget Acme 125
Sprocket NA 150
Sprocket NA 90 
(sales
  >> join(products, by = col.product)
  >> pick(col.product, col.maker, col.revenue))
product maker revenue
Widget Acme 125
Gadget Globex 60
Doohickey Initech 200
Widget Acme 75
Gadget Globex 240
Doohickey Initech 80
Widget Acme 50
Gadget Globex 300
Doohickey Initech 120
Widget Acme 200
Gadget Globex 120
Widget Acme 150
Widget Acme 100
Sprocket NaN 150
Sprocket NaN 90

sales then join products by [product] then pick [product, maker, revenue]

The Sprocket rows come back with maker missing. sales sold a product that products has never heard of, so there was no row to bring columns from. The rows themselves stay: a join keeps this table’s rows whether or not they matched, until you say otherwise.

Leave by out and god matches on the column names both tables share, then prints the choice back as a note beside the answer, the way the laws show. It is never silent about a choice you did not make.

run('
  sales
    then join products
    then take 2
')
date region product quantity revenue cost maker
2025-11-03 West Widget 4 100 40 Acme
2025-11-17 East Gadget 2 120 80 Globex 
sales |> join(products) |> take(2)
date region product quantity revenue cost maker
2025-11-03 West Widget 4 100 40 Acme
2025-11-17 East Gadget 2 120 80 Globex 
sales >> join(products) >> take(2)
date region product quantity revenue cost maker
2025-11-03 West Widget 4 100 40 Acme
2025-11-17 East Gadget 2 120 80 Globex

Often the two tables call the key different things. A table of sales names its region region, and a table of managers names the same thing area. Say both, with is between them, and this table’s name comes first.

managers <- data.frame(
  area    = c("West", "East", "North"),
  manager = c("Ada", "Grace", "Alan"),
  stringsAsFactors = FALSE
)

run('
  sales
    then join managers by [region] is [area]
    then pick [region, manager, revenue]
    then take 3
')
region manager revenue
West Ada 100
East Grace 120
West Ada 120 
sales |>
  join(managers, by = region == area) |>
  pick(region, manager, revenue) |>
  take(3)
region manager revenue
West Ada 100
East Grace 120
West Ada 120 
managers = pd.DataFrame({
    "area":    ["West", "East", "North"],
    "manager": ["Ada", "Grace", "Alan"],
})

(sales
  >> join(managers, by = col.region == col.area)
  >> pick(col.region, col.manager, col.revenue)
  >> take(3))
region manager revenue
West Ada 125
East Grace 60
North Alan 75

sales then join managers by [region] is [area]

The answer keeps region, this table’s name for it, and area does not arrive. The two hold the same value on every row that matched, so bringing both would be bringing one column twice. Keeping this table’s name is what lets the rest of the sentence go on saying region.

is here is the same word the grammar uses for equality everywhere else. R writes == and Python writes ==: R has no bare is, and Python’s is already means something else. That one word is the only difference.

Keys of both kinds mix in one by, separated by commas.

What arrives is a column like any other. The next step groups by manager, a column sales never had.

run('
  sales
    then join managers by [region] is [area]
    then summarize [total] as total([revenue]) by [manager]
')
manager total
Ada 785
Alan 545
Grace 730 
sales |>
  join(managers, by = region == area) |>
  summarize(total = total(revenue), by = manager)
manager total
Ada 785
Alan 545
Grace 730 
(sales
  >> join(managers, by = col.region == col.area)
  >> summarize(total = total(col.revenue), by = col.manager))
manager total
Ada 785
Alan 545
Grace 730

What varies between the four joins you may know by name is only this: what happens to a row that found no match. So that is what the argument is called.

unmatched = Which unmatched rows survive Called elsewhere
"this" (the default) this table’s left join
"none" neither table’s inner join
"both" both tables’ full join

There is no "other", and that is not an omission. A right join is this join with the tables swapped, so it adds no meaning and gets no word.

run('
  sales
    then join products unmatched "none"
    then summarize [revenue] as total([revenue]) by [maker]
')
maker revenue
Acme 700
Globex 720
Initech 400 
sales |>
  join(products, unmatched = "none") |>
  summarize(revenue = total(revenue), by = maker)
maker revenue
Acme 700
Globex 720
Initech 400 
(sales
  >> join(products, unmatched = "none")
  >> summarize(revenue = total(col.revenue), by = col.maker))
maker revenue
Acme 700
Globex 720
Initech 400

A column on both tables that is not being matched on would arrive twice, so it is refused rather than quietly renamed to something you did not choose.

overlap <- data.frame(product = "Widget", revenue = 1,
                      stringsAsFactors = FALSE)
collect(sales |> join(overlap, by = product))
Error:
! 
illegal: both tables have `revenue`, and a join would bring back two columns of that name. Rename one first, or drop it: `then pick all_but [revenue]`
  |
2 |   then join overlap by [product]
  |        ^^^^^^^^^^^^
overlap = pd.DataFrame({"product": ["Widget"], "revenue": [1]})

try:
    collect(sales >> join(overlap, by = col.product))
except GodError as refusal:
    print(refusal)

illegal: both tables have `revenue`, and a join would bring back two columns of that name. Rename one first, or drop it: `then pick all_but [revenue]`
  |
2 |   then join overlap by [product]
  |        ^^^^^^^^^^^^

Two more refusals guard the key itself. A join that could never match finds no partner for any row, and that looks like a fact about your data. The key must hold the same kind of thing in both tables:

codes <- data.frame(product = c(1, 2), code = c("A", "B"))
collect(sales |> join(codes, by = product))
Error:
! 
illegal: `[product]` is text here and a number in `codes`, so the two can never match. Convert one of them first
  |
2 |   then join codes by [product]
  |                       ^^^^^^^
codes = pd.DataFrame({"product": [1, 2], "code": ["A", "B"]})

try:
    collect(sales >> join(codes, by = col.product))
except GodError as refusal:
    print(refusal)

illegal: `[product]` is text here and a number in `codes`, so the two can never match. Convert one of them first
  |
2 |   then join codes by [product]
  |                       ^^^^^^^

And the key must exist on both sides. maker belongs to products alone, so asking sales for it is refused, and the refusal names the columns sales does have:

collect(sales |> join(products, by = maker))
Error:
! 
illegal: there is no column called `maker`. The table has: date, region, product, quantity, revenue, cost
  |
2 |   then join products by [maker]
  |                          ^^^^^
try:
    collect(sales >> join(products, by = col.maker))
except GodError as refusal:
    print(refusal)

illegal: there is no column called `maker`. The table has: date, region, product, quantity, revenue, cost
  |
2 |   then join products by [maker]
  |                          ^^^^^

A join is the step where it is hardest to say what the table will hold afterwards, so look before running.

sales |> join(products, by = product) |> show_steps()
sales date :text region :text product :text quantity :number revenue :number cost :number join products by [product] date region =product quantity revenue cost +maker :text products =product maker :text matched on product · maker crosses over every row of this table is kept rows may multiply — one for each time products repeats a key 
show_steps(sales >> join(products, by = col.product))
sales date :text region :text product :text quantity :number revenue :number cost :number join products by [product] date region =product quantity revenue cost +maker :text products =product maker :text matched on product · maker crosses over every row of this table is kept rows may multiply — one for each time products repeats a key

The arriving table gets a line of its own. The column both tables matched on carries an =, and it carries it twice, once on each side, because it is on both and arrives once rather than as two columns with invented names. What crossed over carries a +.

One thing the checker cannot see is a key that repeats. If the other table holds two rows for one key, your rows multiply, and that is a fact about the rows rather than about the sentence. The checker reads column names and never a row, so this one stays yours to know. The drawing says so on its last line rather than promising a number it cannot know.

When the question is only which rows have a partner over there, nothing needs to arrive at all. The next chapter asks it with a word that cannot multiply anything.