Left-Join Overwrite Behavior for Shared Columns

Question

Using the t and codes tables from the left-join question (defined again here): what would happen if codes already had a column called age, with every value equal to 100? Demonstrate by adding an age column to codes before performing the left join.

t:([] names:`Tom`Mary`Jack`Sean`Anne`Roy;
      country:`Ireland`England`France`England`Germany`Scotland;
      age:10 13 14 18 21 41;
      employed:101101b)

codes:([] Country:`Ireland`England`France`Germany`Scotland;
          digits:("+353";"+44";"+33";"+49";"+44"))

Solution

codes2:`country xcol update age:100 from codes

country  digits age
-------------------
Ireland  "+353" 100
England  "+44"  100
France   "+33"  100
Germany  "+49"  100
Scotland "+44"  100

t lj 1!codes2

names country  age employed digits
----------------------------------
Tom   Ireland  100 1        "+353"
Mary  England  100 0        "+44"
Jack  France   100 1        "+33"
Sean  England  100 1        "+44"
Anne  Germany  100 0        "+49"
Roy   Scotland 100 1        "+44"

Explanation

When the left and right tables share a non-key column — here, both t and codes2 have an age column — a left join behaves like an update: wherever a match is found, the right table's value overwrites the left table's value for that column.

Every row in t had a real age (10, 13, 14, ...), but after the join, every row shows 100 instead — the value from codes2 has replaced it entirely. This is worth knowing before joining tables casually: if both sides happen to define a column with the same name, the left table's data for that column is silently discarded, not merged or preserved alongside it.