Inner-Join vs Left-Join with Missing Matches

Question

Using the t and codes tables from the left-join question (defined again here, before any join has been applied to t), demonstrate the difference between ij and lj when codes does not have a matching entry for Scotland.

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

t ij 1!`country xcol delete from codes where Country=`Scotland

names country age employed digits
---------------------------------
Tom   Ireland 10  1        "+353"
Mary  England 13  0        "+44"
Jack  France  14  1        "+33"
Sean  England 18  1        "+44"
Anne  Germany 21  0        "+49"

t lj 1!`country xcol delete from codes where Country=`Scotland

names country  age employed digits
----------------------------------
Tom   Ireland  10  1        "+353"
Mary  England  13  0        "+44"
Jack  France   14  1        "+33"
Sean  England  18  1        "+44"
Anne  Germany  21  0        "+49"
Roy   Scotland 41  1        ""

Explanation

With ij (inner-join), the result only contains rows whose key has a match on both sides — Roy's row, with country=Scotland, has no corresponding entry in codes now, so it's dropped from the result entirely. The output has 5 rows instead of t's original 6.

With lj (left-join), every row from the left table is kept regardless of whether a match exists — Roy's row is still present, but since there's no match, digits is filled with the null value for a string — the empty string "". The output has all 6 rows.

This is the core distinction between the two: lj always preserves the left table's row count; ij only keeps the rows that found a match, which can shrink the result if the right table doesn't cover every key on the left.