Joins

The examples join reaction-time trials with a table of subjects. Subject s3 has no entry, and s4 has no trials:

open Talon

let trials =
  Query.of_table
    (Talon.v
       [
         ("subject", Column.v Type.string [| "s1"; "s1"; "s2"; "s3"; "s3" |]);
         ("trial", Column.v Type.int16 [| 1; 2; 1; 1; 2 |]);
         ("rt", Column.v Type.float64 [| 0.412; 0.388; 0.501; 0.450; 0.433 |]);
       ])

let subjects =
  Query.of_table
    (Talon.v
       [
         ("id", Column.v Type.string [| "s1"; "s2"; "s4" |]);
         ("age", Column.v Type.uint8 [| 24; 31; 27 |]);
       ])

Conditions

A condition is a value of Join.cond. Atoms name a left column, then a right column:

Atoms combine with &&, written inside Join.( … ): Join.(keys [ "ticker" ] && eq "day" "date"). A join keeps exactly the pairs on which every atom holds. position with another atom, or one atom twice, raises when the condition is built. The Join module's preamble in talon.mli states the matching rules.

Keys match by identity: the same values in the total order that sorting uses, where NaN equals NaN and -0. equals 0.. A null key is one more key, so null keys on both sides match each other. The two columns of an eq must meet at a common type, as operands of an expression do.

Ranges and periods

A comparison matches as the expression comparing the two columns would, in the total order that sorting uses, and never at a null. Trial windows on the left, each with the events it holds:

let windows =
  Query.of_table
    (Talon.v
       [
         ("window", Column.v Type.int16 [| 1; 2 |]);
         ("start", Column.v Type.float64 [| 0.0; 10.0 |]);
         ("stop", Column.v Type.float64 [| 5.0; 15.0 |]);
       ])

let events =
  Query.of_table
    (Talon.v [ ("t", Column.v Type.float64 [| 12.5; 3.0; 7.0; 5.0 |]) ])

let in_windows =
  Query.(windows |> join ~on:Join.(le "start" "t" && gt "stop" "t") events)

table 2 rows × 4 columns
 window  start    stop      t
 int16   float64  float64   float64
      1   0.0000   5.00000   3.00000
      2  10.0000  15.00000  12.50000

The event at 5.0 is at the first window's stop, so it falls in neither. With the events on the left, the same pairs are Join.inside "t" ("start", "stop"), in the events' order.

The join sorts one side by one column, its band column, and searches it for each row of the other side, so it reads only the pairs its bands hold. Here the band column is the events' t, which both comparisons bound. With equality atoms beside the comparisons it sorts within each key. Every other comparison is tested on each of those pairs. The Range joins section of Join in talon.mli states how the band column is chosen.

overlaps reads each side's two columns as a period from the lesser value to the greater. Periods that only touch, such as (0, 2) and (2, 3), do not overlap. A period whose two values are equal is the one point, which overlaps a period that holds it, so an event of zero duration still meets the trials around it.

Kinds

~kind says which rows the join keeps. It defaults to Inner.

let on = Join.eq "subject" "id"
let inner = Query.(trials |> join ~on subjects)
let left = Query.(trials |> join ~kind:Left ~on subjects)
let full = Query.(trials |> join ~kind:Full ~on subjects)
let semi = Query.(trials |> join ~kind:Semi ~on subjects)
let anti = Query.(trials |> join ~kind:Anti ~on subjects)

Inner keeps the matched pairs. Left also keeps each left row without a match, its right columns null:

table 5 rows × 4 columns
 subject  trial  rt        age
 string   int16  float64   uint8
 s1           1  0.412000     24
 s1           2  0.388000     24
 s2           1  0.501000     31
 s3           1  0.450000      ∅
 s3           2  0.433000      ∅

Full then appends each right row without a match, its left columns null except the key, which takes the right row's value:

table 6 rows × 4 columns
 subject  trial  rt        age
 string   int16  float64   uint8
 s1           1  0.412000     24
 s1           2  0.388000     24
 s2           1  0.501000     31
 s3           1  0.450000      ∅
 s3           2  0.433000      ∅
 s4           ∅         ∅     27

Semi keeps each left row that has a match, once, and Anti each left row that has none. Both keep the left columns only:

table 2 rows × 3 columns
 subject  trial  rt
 string   int16  float64
 s3           1  0.450000
 s3           2  0.433000

A right join is a left join with the arguments swapped.

Columns and order

The result has the left columns, then the right columns except the right side of each equality atom: the key of eq "subject" "id" appears once, as subject. In a Full join that key has the common type of both sides. A name on both sides is a problem, and talon adds no suffixes: rename one side first with Kit.rename.

Rows come in the left's order, each left row followed by its matches in the right's order. An equality join reads both inputs before it returns a row and runs in time proportional to the rows of both inputs plus the matches. A range join reads both inputs too, sorts its band column's side unless it is already in order, and searches it for each row of the other side. The Algorithms section of Join in talon.mli states the costs, the Join preamble the order of the rows, and Query.join when a join blocks.

Asserting matches

~each_left and ~each_right state how many matches each row of a side must have: Any, the default, At_most_one, One or At_least_one. A row with another number fails the run and names the side, the key and the count. A lookup table that must hold every key once is ~each_left:One:

let checked = Query.(trials |> join ~on ~each_left:One subjects)

join ~on:(eq "subject" "id") ~each_left:One: row 3: the left row whose "subject" is "s3" matches 0 rows, not one.

Without the assertion, the inner join drops s3's trials and nothing says so. With ~each_right:At_most_one, a lookup table with a duplicated key fails instead of duplicating left rows.

Problems

A join checks its condition against both schemas when it is applied. A column missing on its side is a problem, reported with the columns of both inputs:

join: 1 problem
  right: no column "subject". The columns are "id" and "age".
  left (3 columns): subject string, trial int16, rt float64
  right (2 columns): id string, age uint8

The example 04-joins runs every join on this page.

this page is tested with mdxedit this page ↗